psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "receipts_linearized_uniqueness"

[Ezwen]

Description

We encountered a database corruption problem some weeks ago, and had to make many fixes in the database by hand to reach again a consistent state that would allow synapse to run. We were fortunately successful enough, and our instance is now running nicely once more and without any remaining corruption.

However, after this intervention, we noticed our homeserver started having lots of errors labeled sycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "receipts_linearized_uniqueness". This is probably due to this table having obtained new rows it shouldn't have during the corruption, and these lines remained in place as their presence does not directly violates the contraint − in fact, it's adding new rows that Synapse refuses as it would break the constraint.

While obviously Synapse is not responsible of external database problems, I thought it was relevant to open this issue as I find it strange that it is not able to recover from such a situation, eg. by deleting old rows before inserting new ones, in order to preserve the uniqueness constraint?

Steps to reproduce

• Encounter an unbfortunate database problem and end up with a slightly inconsistent receipts_linearized table
• Start synapse and check logs

Homeserver

Private

Synapse Version

1.90.0

Installation Method

Docker (matrixdotorg/synapse)

Database

PostgreSQL v15

Workers

Single process

Platform

Debian 11, no virtualization, synapse and postgre running in Docker containers

Configuration

No response

Relevant log output

2023-08-20 19:14:40,671 - synapse.federation.federation_server - 1481 - ERROR - PUT-990851- Failed to handle edu 'm.receipt'
--
Traceback (most recent call last):
File "/usr/local/lib/python3.11/site-packages/synapse/federation/federation_server.py", line 1477, in on_edu
await handler(origin, content)
File "/usr/local/lib/python3.11/site-packages/synapse/handlers/receipts.py", line 112, in _received_remote_receipt
await self._handle_new_receipts(receipts)
File "/usr/local/lib/python3.11/site-packages/synapse/handlers/receipts.py", line 120, in _handle_new_receipts
res = await self.store.insert_receipt(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/synapse/storage/databases/main/receipts.py", line 769, in insert_receipt
event_ts = await self.db_pool.runInteraction(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 930, in runInteraction
return await delay_cancellation(_runInteraction())
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/twisted/internet/defer.py", line 1693, in _inlineCallbacks
result = context.run(
^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/twisted/python/failure.py", line 518, in throwExceptionIntoGenerator
return g.throw(self.type, self.value, self.tb)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 896, in _runInteraction
result = await self.runWithConnection(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 1025, in runWithConnection
return await make_deferred_yieldable(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/twisted/python/threadpool.py", line 244, in inContext
result = inContext.theWork()  # type: ignore[attr-defined]
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/twisted/python/threadpool.py", line 260, in <lambda>
inContext.theWork = lambda: context.call(  # type: ignore[attr-defined]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/twisted/python/context.py", line 117, in callWithContext
return self.currentContext().callWithContext(ctx, func, *args, **kw)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/twisted/python/context.py", line 82, in callWithContext
return func(*args, **kw)
^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/twisted/enterprise/adbapi.py", line 282, in _runWithConnection
result = func(conn, *args, **kw)
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 1018, in inner_func
return func(db_conn, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 758, in new_transaction
r = func(cursor, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/synapse/storage/databases/main/receipts.py", line 684, in _insert_linearized_receipt_txn
self.db_pool.simple_upsert_txn(
File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 1291, in simple_upsert_txn
return self.simple_upsert_txn_native_upsert(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 1431, in simple_upsert_txn_native_upsert
txn.execute(sql, list(allvalues.values()))
File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 417, in execute
self._do_execute(self.txn.execute, sql, parameters)
File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 469, in _do_execute
return func(sql, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "receipts_linearized_uniqueness"
DETAIL:  Key (room_id, receipt_type, user_id)=(<redacted room id>, m.read, <redacted user id>) already exists.

Anything else that would be useful to know?

No response

[DMRobertson]

simple_upsert_txn_native_upsert

Synapse is attempting to upsert a row here (I.e INSERT INTO ... ON CONFLICT (...) DO ...) which effectively does delete an old row before inserting a new one. It is very surprising to see that this fails.

The index in question is receipts_linearized_uniqueness... which does not exist on Matrix.org and seems to have been dropped in #13877. ISTR we had fun and games with migrations in this area... @clokep does this ring any bells?

[DMRobertson]

In the meantime it would be useful if you could provide us with the output of

• \d receipts_linearized
• SELECT * FROM background_updates;
• SELECT * FROM applied_schema_deltas ORDER BY version ASC, file ASC;

to see what has gone wrong.

[Ezwen]

Here are the outputs:

synapse=# \d receipts_linearized
               Table "public.receipts_linearized"
        Column         |  Type  | Collation | Nullable | Default 
-----------------------+--------+-----------+----------+---------
 stream_id             | bigint |           | not null | 
 room_id               | text   |           | not null | 
 receipt_type          | text   |           | not null | 
 user_id               | text   |           | not null | 
 event_id              | text   |           | not null | 
 data                  | text   |           | not null | 
 instance_name         | text   |           |          | 
 event_stream_ordering | bigint |           |          | 
 thread_id             | text   |           |          | 
Indexes:
    "receipts_linearized_id" btree (stream_id)
    "receipts_linearized_room_stream" btree (room_id, stream_id)
    "receipts_linearized_unique_index" UNIQUE, btree (room_id, receipt_type, user_id) WHERE thread_id IS NULL
    "receipts_linearized_uniqueness" UNIQUE CONSTRAINT, btree (room_id, receipt_type, user_id)
    "receipts_linearized_uniqueness_thread" UNIQUE CONSTRAINT, btree (room_id, receipt_type, user_id, thread_id)
    "receipts_linearized_user" btree (user_id)

synapse=# SELECT * FROM background_updates;
 update_name | progress_json | depends_on | ordering 
-------------+---------------+------------+----------
(0 rows)

synapse=# SELECT * FROM applied_schema_deltas ORDER BY version ASC, file ASC;
 version |                                    file                                    
---------+----------------------------------------------------------------------------
17 | 17/drop_indexes.sql
17 | 17/server_keys.sql
17 | 17/user_threepids.sql
18 | 18/server_keys_bigger_ints.sql
19 | 19/event_index.sql
20 | 20/dummy.sql
20 | 20/pushers.py
20 | 20/pushers.pyc
21 | 21/end_to_end_keys.sql
21 | 21/receipts.sql
22 | 22/receipts_index.sql
22 | 22/user_threepids_unique.sql
23 | 23/drop_state_index.sql
24 | 24/stats_reporting.sql
25 | 25/00background_updates.sql
25 | 25/fts.py
25 | 25/fts.pyc
25 | 25/guest_access.sql
25 | 25/history_visibility.sql
25 | 25/tags.sql
26 | 26/account_data.sql
27 | 27/account_data.sql
27 | 27/forgotten_memberships.sql
27 | 27/ts.py
27 | 27/ts.pyc
28 | 28/event_push_actions.sql
28 | 28/events_room_stream.sql
28 | 28/public_roms_index.sql
28 | 28/receipts_user_id_index.sql
28 | 28/upgrade_times.sql
28 | 28/users_is_guest.sql
29 | 29/push_actions.sql
30 | 30/alias_creator.sql
30 | 30/as_users.py
30 | 30/as_users.pyc
30 | 30/deleted_pushers.sql
30 | 30/presence_stream.sql
30 | 30/public_rooms.sql
30 | 30/push_rule_stream.sql
30 | 30/state_stream.sql
30 | 30/threepid_guest_access_tokens.sql
31 | 31/invites.sql
31 | 31/local_media_repository_url_cache.sql
31 | 31/pushers.py
31 | 31/pushers.pyc
31 | 31/pushers_index.sql
31 | 31/search_update.py
31 | 31/search_update.pyc
32 | 32/events.sql
32 | 32/openid.sql
32 | 32/pusher_throttle.sql
32 | 32/remove_indices.sql
32 | 32/reports.sql
33 | 33/access_tokens_device_index.sql
33 | 33/devices.sql
33 | 33/devices_for_e2e_keys.sql
33 | 33/devices_for_e2e_keys_clear_unknown_device.sql
33 | 33/event_fields.py
33 | 33/event_fields.pyc
33 | 33/remote_media_ts.py
33 | 33/remote_media_ts.pyc
33 | 33/user_ips_index.sql
34 | 34/appservice_stream.sql
34 | 34/cache_stream.py
34 | 34/cache_stream.pyc
34 | 34/device_inbox.sql
34 | 34/push_display_name_rename.sql
34 | 34/received_txn_purge.py
34 | 34/received_txn_purge.pyc
34 | 34/sent_txn_purge.py
34 | 34/sent_txn_purge.pyc
35 | 35/add_state_index.sql
35 | 35/contains_url.sql
35 | 35/device_outbox.sql
35 | 35/device_stream_id.sql
35 | 35/event_push_actions_index.sql
35 | 35/public_room_list_change_stream.sql
35 | 35/state.sql
35 | 35/state_dedupe.sql
35 | 35/stream_order_to_extrem.sql
36 | 36/readd_public_rooms.sql
37 | 37/remove_auth_idx.py
37 | 37/remove_auth_idx.pyc
37 | 37/user_threepids.sql
38 | 38/postgres_fts_gist.sql
39 | 39/appservice_room_list.sql
39 | 39/device_federation_stream_idx.sql
39 | 39/event_push_index.sql
39 | 39/federation_out_position.sql
39 | 39/membership_profile.sql
40 | 40/current_state_idx.sql
40 | 40/device_inbox.sql
40 | 40/device_list_streams.sql
40 | 40/event_push_summary.sql
40 | 40/pushers.sql
41 | 41/device_list_stream_idx.sql
41 | 41/device_outbound_index.sql
41 | 41/event_search_event_id_idx.sql
41 | 41/ratelimit.sql
42 | 42/current_state_delta.sql
42 | 42/device_list_last_id.sql
42 | 42/event_auth_state_only.sql
42 | 42/user_dir.py
42 | 42/user_dir.pyc
43 | 43/blocked_rooms.sql
43 | 43/quarantine_media.sql
43 | 43/url_cache.sql
43 | 43/user_share.sql
44 | 44/expire_url_cache.sql
45 | 45/group_server.sql
45 | 45/profile_cache.sql
46 | 46/drop_refresh_tokens.sql
46 | 46/drop_unique_deleted_pushers.sql
46 | 46/group_server.sql
46 | 46/local_media_repository_url_idx.sql
46 | 46/user_dir_null_room_ids.sql
46 | 46/user_dir_typos.sql
47 | 47/last_access_media.sql
47 | 47/postgres_fts_gin.sql
47 | 47/push_actions_staging.sql
47 | 47/state_group_seq.py
47 | 47/state_group_seq.pyc
48 | 48/add_user_consent.sql
48 | 48/add_user_ips_last_seen_index.sql
48 | 48/deactivated_users.sql
48 | 48/group_unique_indexes.py
48 | 48/group_unique_indexes.pyc
48 | 48/groups_joinable.sql
49 | 49/add_user_consent_server_notice_sent.sql
49 | 49/add_user_daily_visits.sql
49 | 49/add_user_ips_last_seen_only_index.sql
50 | 50/add_creation_ts_users_index.sql
50 | 50/erasure_store.sql
50 | 50/make_event_content_nullable.py
50 | 50/make_event_content_nullable.pyc
51 | 51/e2e_room_keys.sql
51 | 51/monthly_active_users.sql
52 | 52/add_event_to_state_group_index.sql
52 | 52/device_list_streams_unique_idx.sql
52 | 52/e2e_room_keys.sql
53 | 53/add_user_type_to_users.sql
53 | 53/drop_sent_transactions.sql
53 | 53/event_format_version.sql
53 | 53/user_dir_populate.sql
53 | 53/user_ips_index.sql
53 | 53/user_share.sql
53 | 53/user_threepid_id.sql
53 | 53/users_in_public_rooms.sql
54 | 54/account_validity.sql
54 | 54/account_validity_with_renewal.sql
54 | 54/add_validity_to_server_keys.sql
54 | 54/delete_forward_extremities.sql
54 | 54/drop_legacy_tables.sql
54 | 54/drop_presence_list.sql
54 | 54/relations.sql
54 | 54/stats.sql
54 | 54/stats2.sql
55 | 55/access_token_expiry.sql
55 | 55/track_threepid_validations.sql
55 | 55/users_alter_deactivated.sql
56 | 56/add_spans_to_device_lists.sql
56 | 56/current_state_events_membership.sql
56 | 56/current_state_events_membership_mk2.sql
56 | 56/delete_keys_from_deleted_backups.sql
56 | 56/destinations_failure_ts.sql
56 | 56/destinations_retry_interval_type.sql.postgres
56 | 56/device_stream_id_insert.sql
56 | 56/devices_last_seen.sql
56 | 56/drop_unused_event_tables.sql
56 | 56/event_expiry.sql
56 | 56/event_labels.sql
56 | 56/event_labels_background_update.sql
56 | 56/fix_room_keys_index.sql
56 | 56/hidden_devices.sql
56 | 56/nuke_empty_communities_from_db.sql
56 | 56/public_room_list_idx.sql
56 | 56/redaction_censor.sql
56 | 56/redaction_censor2.sql
56 | 56/redaction_censor3_fix_update.sql.postgres
56 | 56/redaction_censor4.sql
56 | 56/remove_tombstoned_rooms_from_directory.sql
56 | 56/room_key_etag.sql
56 | 56/room_membership_idx.sql
56 | 56/room_retention.sql
56 | 56/signing_keys.sql
56 | 56/signing_keys_nonunique_signatures.sql
56 | 56/state_group_room_idx.sql
56 | 56/stats_separated.sql
56 | 56/unique_user_filter_index.py
56 | 56/user_external_ids.sql
56 | 56/users_in_public_rooms_idx.sql
57 | 57/delete_old_current_state_events.sql
57 | 57/device_list_remote_cache_stale.sql
57 | 57/local_current_membership.py
57 | 57/remove_sent_outbound_pokes.sql
57 | 57/rooms_version_column.sql
57 | 57/rooms_version_column_2.sql.postgres
57 | 57/rooms_version_column_3.sql.postgres
58 | 58/00background_update_ordering.sql
58 | 58/02remove_dup_outbound_pokes.sql
58 | 58/03persist_ui_auth.sql
58 | 58/05cache_instance.sql.postgres
58 | 58/06dlols_unique_idx.py
58 | 58/07add_method_to_thumbnail_constraint.sql.postgres
58 | 58/07persist_ui_auth_ips.sql
58 | 58/08_media_safe_from_quarantine.sql.postgres
58 | 58/09shadow_ban.sql
58 | 58/10_pushrules_enabled_delete_obsolete.sql
58 | 58/10drop_local_rejections_stream.sql
58 | 58/10federation_pos_instance_name.sql
58 | 58/11dehydration.sql
58 | 58/11fallback.sql
58 | 58/11user_id_seq.py
58 | 58/12room_stats.sql
58 | 58/13remove_presence_allow_inbound.sql
58 | 58/14events_instance_name.sql
58 | 58/14events_instance_name.sql.postgres
58 | 58/15_catchup_destination_rooms.sql
58 | 58/15unread_count.sql
58 | 58/16populate_stats_process_rooms_fix.sql
58 | 58/17_catchup_last_successful.sql
58 | 58/18stream_positions.sql
58 | 58/19instance_map.sql.postgres
58 | 58/19txn_id.sql
58 | 58/20instance_name_event_tables.sql
58 | 58/20user_daily_visits.sql
58 | 58/21as_device_stream.sql
58 | 58/21drop_device_max_stream_id.sql
58 | 58/22puppet_token.sql
58 | 58/22users_have_local_media.sql
58 | 58/23e2e_cross_signing_keys_idx.sql
58 | 58/24drop_event_json_index.sql
58 | 58/25user_external_ids_user_id_idx.sql
58 | 58/26access_token_last_validated.sql
58 | 58/27local_invites.sql
58 | 58/28drop_last_used_column.sql.postgres
58 | 58/28rejected_events_metadata.sql
59 | 59/01ignored_user.py
59 | 59/02shard_send_to_device.sql
59 | 59/03shard_send_to_device_sequence.sql.postgres
59 | 59/04_event_auth_chains.sql
59 | 59/04_event_auth_chains.sql.postgres
59 | 59/04drop_account_data.sql
59 | 59/05cache_invalidation.sql
59 | 59/06chain_cover_index.sql
59 | 59/06shard_account_data.sql
59 | 59/06shard_account_data.sql.postgres
59 | 59/07shard_account_data_fix.sql
59 | 59/08delete_pushers_for_deactivated_accounts.sql
59 | 59/08delete_stale_pushers.sql
59 | 59/09rejected_events_metadata.sql
59 | 59/10delete_purged_chain_cover.sql
59 | 59/11add_knock_members_to_stats.sql
59 | 59/11drop_thumbnail_constraint.sql.postgres
59 | 59/12account_validity_token_used_ts_ms.sql
59 | 59/12presence_stream_instance.sql
59 | 59/12presence_stream_instance_seq.sql.postgres
59 | 59/13users_to_send_full_presence_to.sql
59 | 59/14refresh_tokens.sql
59 | 59/15locks.sql
59 | 59/16federation_inbound_staging.sql
60 | 60/01recreate_stream_ordering.sql.postgres
60 | 60/02change_stream_ordering_columns.sql.postgres
61 | 61/01change_appservices_txns.sql.postgres
61 | 61/01insertion_event_lookups.sql
61 | 61/02drop_redundant_room_depth_index.sql
61 | 61/02state_groups_state_n_distinct.sql.postgres
61 | 61/03recreate_min_depth.py
62 | 62/01insertion_event_extremities.sql
63 | 63/01create_registration_tokens.sql
63 | 63/02delete_unlinked_email_pushers.sql
63 | 63/02populate-rooms-creator.sql
63 | 63/03session_store.sql
63 | 63/04add_presence_stream_not_offline_index.sql
64 | 64/01msc2716_chunk_to_batch_rename.sql.postgres
65 | 65/01msc2716_insertion_event_edges.sql
65 | 65/02_thread_relations.sql
65 | 65/03remove_hidden_devices_from_device_inbox.sql
65 | 65/04_local_group_updates.sql
65 | 65/05_remove_room_stats_historical_and_user_stats_historical.sql
65 | 65/06remove_deleted_devices_from_device_inbox.sql
65 | 65/07_arbitrary_relations.sql
65 | 65/08_device_inbox_background_updates.sql
65 | 65/10_expirable_refresh_tokens.sql
65 | 65/11_devices_auth_provider_session.sql
67 | 67/01drop_public_room_list_stream.sql
68 | 68/01event_columns.sql
68 | 68/02_msc2409_add_device_id_appservice_stream_type.sql
68 | 68/03_delete_account_data_for_deactivated_accounts.sql
68 | 68/04_refresh_tokens_index_next_token_id.sql
68 | 68/04partial_state_rooms.sql
68 | 68/05partial_state_rooms_triggers.py
68 | 68/06_msc3202_add_device_list_appservice_stream_type.sql
69 | 69/01as_txn_seq.py
69 | 69/01device_list_oubound_by_room.sql
69 | 69/02cache_invalidation_index.sql
70 | 70/01clean_table_purged_rooms.sql
70 | 70/08_state_group_edges_unique.sql
71 | 71/01rebuild_event_edges.sql.postgres
71 | 71/01remove_noop_background_updates.sql
71 | 71/02event_push_summary_unique.sql
72 | 72/01add_room_type_to_state_stats.sql
72 | 72/01event_push_summary_receipt.sql
72 | 72/02event_push_actions_index.sql
72 | 72/03bg_populate_events_columns.py
72 | 72/03drop_event_reference_hashes.sql
72 | 72/03remove_groups.sql
72 | 72/04drop_column_application_services_state_last_txn.sql.postgres
72 | 72/05receipts_event_stream_ordering.sql
72 | 72/05remove_unstable_private_read_receipts.sql
72 | 72/06add_consent_ts_to_users.sql
72 | 72/06thread_notifications.sql
72 | 72/07force_update_current_state_events_membership.py
72 | 72/07thread_receipts.sql.postgres
72 | 72/08begin_cache_invalidation_seq_at_2.sql.postgres
72 | 72/08thread_receipts.sql
73 | 73/01event_failed_pull_attempts.sql
73 | 73/02add_pusher_enabled.sql
73 | 73/02room_id_indexes_for_purging.sql
73 | 73/03pusher_device_id.sql
73 | 73/03users_approved_column.sql
73 | 73/04partial_join_details.sql
73 | 73/04pending_device_list_updates.sql
73 | 73/05old_push_actions.sql.postgres
73 | 73/06thread_notifications_thread_id_idx.sql
73 | 73/08thread_receipts_non_null.sql.postgres
73 | 73/09partial_joined_via_destination.sql
73 | 73/09threads_table.sql
73 | 73/10_update_sqlite_fts4_tokenizer.py
73 | 73/10login_tokens.sql
73 | 73/11event_search_room_id_n_distinct.sql.postgres
73 | 73/12refactor_device_list_outbound_pokes.sql
73 | 73/13add_device_lists_index.sql
73 | 73/20_un_partial_stated_room_stream.sql
73 | 73/21_un_partial_stated_room_stream_seq.sql.postgres
73 | 73/22_rebuild_user_dir_stats.sql
73 | 73/22_un_partial_stated_event_stream.sql
73 | 73/23_fix_thread_index.sql
73 | 73/23_un_partial_stated_room_stream_seq.sql.postgres
73 | 73/24_events_jump_to_date_index.sql
73 | 73/25drop_presence.sql
74 | 74/01_user_directory_stale_remote_users.sql
74 | 74/02_set_device_id_for_pushers_bg_update.sql
74 | 74/03_membership_tables_event_stream_ordering.sql.postgres
74 | 74/03_room_membership_index.sql
74 | 74/04_delete_e2e_backup_keys_for_deactivated_users.sql
74 | 74/04_membership_tables_event_stream_ordering_triggers.py
74 | 74/05_events_txn_id_device_id.sql
74 | 74/90COMMENTS_destinations.sql.postgres
76 | 76/01_add_profiles_full_user_id_column.sql
76 | 76/02_add_user_filters_full_user_id_column.sql
76 | 76/03_per_user_experimental_features.sql
76 | 76/04_add_room_forgetter.sql
77 | 77/01_add_profiles_not_valid_check.sql.postgres
77 | 77/02_add_user_filters_not_valid_check.sql.postgres
77 | 77/03bg_populate_full_user_id_profiles.sql
77 | 77/04bg_populate_full_user_id_user_filters.sql
77 | 77/05thread_notifications_backfill.sql
77 | 77/06thread_notifications_not_null_event_push_actions.sql.postgres
77 | 77/06thread_notifications_not_null_event_push_actions_staging.sql.postgres
77 | 77/06thread_notifications_not_null_event_push_summary.sql.postgres
77 | 77/14bg_indices_event_stream_ordering.sql
78 | 78/01_validate_and_update_profiles.py
78 | 78/02_validate_and_update_user_filters.py
78 | 78/03_remove_unused_indexes_user_filters.py
78 | 78/03event_extremities_constraints.py
78 | 78/04_add_full_user_id_index_user_filters.py
79 | 79/03_read_write_locks_triggers.sql.postgres
79 | 79/04_mitigate_stream_ordering_update_race.py
79 | 79/05_read_write_locks_triggers.sql.postgres

And about:

The index in question is receipts_linearized_uniqueness... which does not exist on Matrix.org and seems to have been dropped in #13877. ISTR we had fun and games with migrations in this area... @clokep does this ring any bells?

Oh.

While trying to save our database we did end up recreating some tables manually, including some indexes/constraints… We used this file, ie. the latest "full" version of the schema, as source of truth to try to recreate things exactly as expected by synapse.

But I realize only now, with your comment that this file is only giving us version 72 of the schema, which since then has much evolved but only through deltas (73 to 79)… which we have not taken into account in our database repair operations.

So I think it's completely our fault :) and that we should be able to save the situation by going through deltas 73 to 79 and finding what changes we failed to apply to the tables we re-created by hand…

[DMRobertson]

But I realize only now, with your comment that this file is only giving us version 72 of the schema, which since then has much evolved but only through deltas (73 to 79)

Ahh yes---we have a script to cut a full schema dump (which avoid having to run migrations and background updates) but we haven't run it in a while. Perhaps we ought to do that soon.

we should be able to save the situation by going through deltas 73 to 79 and finding what changes we failed to apply to the tables we re-created by hand…

Ouch, that sounds painful---but I'm glad we've got a hypothesis for what went wrong.

Let us know how you get on and if there are any problems. We can't officially support manual database tinkering... but we might be able to offer some quick suggestions if you see anything strange.

[clokep]

ISTR we had fun and games with migrations in this area... @clokep does this ring any bells?

Sounds like it isn't the cause, but for thoroughness: there was a bunch of issues here (#14406, #14123, #14377), which we think were solved (#14650, #14453, #14915).

Some of those issues might have some commands to run to manually poke data around the tables (and I think one of the background updates does this for you?) You might just need to run those.

[Ezwen]

Update: We checked all the schema deltas after version 72 and compared to our current tables, and we were happy that the only missing bit was the receipts_linearized_uniqueness constraint removal.

Therefore we simply ran ALTER TABLE receipts_linearized DROP CONSTRAINT receipts_linearized_uniqueness; and everything is back to normal, and we have a happy synapse once more :).

We did find another similar error in the logs that occurs very very rarely (once every two days on average), which is psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "device_lists_remote_cache_unique_id", but we checked and this time the constraint device_lists_remote_cache_unique_id is here as it should, so this must be unrelated to all this database repair situation.

Apologies for the extraneous ticket, and thanks for your comments that pointed us in the right direction!

[DMRobertson]

No worries---thanks for confirming!

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "device_lists_remote_cache_unique_id"

We see similar errors on Matrix.org. I would guess that two things are racing to insert into device_lists_remote_cache somehow. -> #16243