DB error "duplicate key value violates unique constraint"

[peanutlasko]

I keep seeing these errors pop up in my Docker container logs. Is this something to be worried about?

23-05-20 10:06:21 fail: BirdsiteLive.Pipeline.Processors.Federation.SendTweetsToFollowersProcessor[0]
2023-05-20 10:06:21 Posting to mas.to/inbox failed
2023-05-20 10:06:21 Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "sync_tweets_acct_tweetid_inbox_key"
2023-05-20 10:06:21
2023-05-20 10:06:21 DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
2023-05-20 10:06:21 at Npgsql.Internal.NpgsqlConnector.g__ReadMessageLong|225_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
2023-05-20 10:06:21 at Npgsql.NpgsqlDataReader.g__ReadMessageSequential|44_0(NpgsqlConnector connector, Boolean async)
2023-05-20 10:06:21 at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
2023-05-20 10:06:21 at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
2023-05-20 10:06:21 at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
2023-05-20 10:06:21 at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
2023-05-20 10:06:21 at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
2023-05-20 10:06:21 at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 418
2023-05-20 10:06:21 at BirdsiteLive.DAL.Postgres.DataAccessLayers.SyncTweetsPostgresDal.SaveTweetAsync(SyncTweet tweet) in /src/DataAccessLayers/BirdsiteLive.DAL.Postgres/DataAccessLayers/SyncTweetsPostgresDal.cs:line 48
2023-05-20 10:06:21 at BirdsiteLive.Pipeline.Processors.SubTasks.SendTweetsTaskBase.SaveSyncTweetAsync(String acct, Int64 tweetId, String host, String inbox) in /src/BirdsiteLive.Pipeline/Processors/Federation/SubTasks/SendTweetsTaskBase.cs:line 30
2023-05-20 10:06:21 at BirdsiteLive.Pipeline.Processors.SubTasks.SendTweetsToSharedInboxTask.ExecuteAsync(ExtractedTweet[] tweets, SyncTwitterUser user, String host, Follower[] followersPerInstance) in /src/BirdsiteLive.Pipeline/Processors/Federation/SubTasks/SendTweetsToSharedInboxTask.cs:line 65
2023-05-20 10:06:21 at BirdsiteLive.Pipeline.Processors.SubTasks.SendTweetsToSharedInboxTask.ExecuteAsync(ExtractedTweet[] tweets, SyncTwitterUser user, String host, Follower[] followersPerInstance) in /src/BirdsiteLive.Pipeline/Processors/Federation/SubTasks/SendTweetsToSharedInboxTask.cs:line 87
2023-05-20 10:06:21 at BirdsiteLive.Pipeline.Processors.Federation.SendTweetsToFollowersProcessor.ProcessFollowersWithSharedInboxAsync(ExtractedTweet[] tweets, List`1 followers, SyncTwitterUser user) in /src/BirdsiteLive.Pipeline/Processors/Federation/SendTweetsToFollowersProcessor.cs:line 71
2023-05-20 10:06:21 Exception data:
2023-05-20 10:06:21 Severity: ERROR
2023-05-20 10:06:21 SqlState: 23505
2023-05-20 10:06:21 MessageText: duplicate key value violates unique constraint "sync_tweets_acct_tweetid_inbox_key"
2023-05-20 10:06:21 Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
2023-05-20 10:06:21 SchemaName: public
2023-05-20 10:06:21 TableName: sync_tweets
2023-05-20 10:06:21 ConstraintName: sync_tweets_acct_tweetid_inbox_key
2023-05-20 10:06:21 File: nbtinsert.c
2023-05-20 10:06:21 Line: 433
2023-05-20 10:06:21 Routine: _bt_check_unique

[Sverik]

Ditto. Some Tweets appear with a huge delay, more than a month later. Not sure if this is related.

[Sverik]

Could it be that the unique index for sync_tweets table is missing host?

birdsitelive=# \d+ sync_tweets_acct_tweetid_inbox_key 
     Index "public.sync_tweets_acct_tweetid_inbox_key"
 Column  |          Type           | Definition | Storage  
---------+-------------------------+------------+----------
 acct    | character varying(50)   | acct       | extended
 tweetid | bigint                  | tweetid    | plain
 inbox   | character varying(2048) | inbox      | extended
unique, btree, for table "public.sync_tweets"

Whereas the index for followers includes host

birdsitelive=# \d+ followers_acct_host_key 
         Index "public.followers_acct_host_key"
 Column |          Type          | Definition | Storage  
--------+------------------------+------------+----------
 acct   | character varying(50)  | acct       | extended
 host   | character varying(253) | host       | extended
unique, btree, for table "public.followers"

[reykjalin]

I've started seeing the same issue and my instance has become unresponsive 🤔