This repository was archived by the owner on Apr 26, 2024. It is now read-only.
This repository was archived by the owner on Apr 26, 2024. It is now read-only.
Migration 79/04_mitigate_stream_ordering_update_race
fails because there is no unique constraint matching given keys for referenced table "events"
#16192
Open
Description
Description
Similar to #10691, I'm having issues after a system update. After an upgrade (from Debian 11 to 12) and a reboot, I get the following error on startup: psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "events"
What's strange is I don't think synapse actually updated. It looks like it's still using a bullseye version, and I have daily unattended upgrades so it shouldn't have changed much. I've compared the schema to the one referenced in the source code, but I don't see any missing keys.
synapse=> \d events
Table "public.events"
Column | Type | Collation | Nullable | Default
----------------------+---------+-----------+----------+---------
stream_ordering | integer | | not null |
topological_ordering | bigint | | not null |
event_id | text | | not null |
type | text | | not null |
room_id | text | | not null |
content | text | | |
unrecognized_keys | text | | |
processed | boolean | | not null |
outlier | boolean | | not null |
depth | bigint | | not null | 0
origin_server_ts | bigint | | |
received_ts | bigint | | |
sender | text | | |
contains_url | boolean | | |
instance_name | text | | |
stream_ordering2 | bigint | | |
state_key | text | | |
rejection_reason | text | | |
Indexes:
"events_pkey" PRIMARY KEY, btree (stream_ordering)
"event_contains_url_index" btree (room_id, topological_ordering, stream_ordering) WHERE contains_url = true AND outlier = false
"events_event_id_key" UNIQUE CONSTRAINT, btree (event_id)
"events_order_room" btree (room_id, topological_ordering, stream_ordering)
"events_room_stream" btree (room_id, stream_ordering)
"events_ts" btree (origin_server_ts, stream_ordering)
Referenced by:
TABLE "event_edges" CONSTRAINT "event_edges_event_id_fkey" FOREIGN KEY (event_id) REFERENCES events(event_id) NOT VALID
TABLE "event_forward_extremities" CONSTRAINT "event_forward_extremities_event_id" FOREIGN KEY (event_id) REFERENCES events(event_id) DEFERRABLE INITIALLY DEFERRED NOT VALID
TABLE "current_state_events" CONSTRAINT "event_stream_ordering_fkey" FOREIGN KEY (event_stream_ordering) REFERENCES events(stream_ordering) NOT VALID
TABLE "local_current_membership" CONSTRAINT "event_stream_ordering_fkey" FOREIGN KEY (event_stream_ordering) REFERENCES events(stream_ordering) NOT VALID
TABLE "room_memberships" CONSTRAINT "event_stream_ordering_fkey" FOREIGN KEY (event_stream_ordering) REFERENCES events(stream_ordering) NOT VALID
TABLE "event_txn_id_device_id" CONSTRAINT "event_txn_id_device_id_event_id_fkey" FOREIGN KEY (event_id) REFERENCES events(event_id) ON DELETE CASCADE
TABLE "event_txn_id" CONSTRAINT "event_txn_id_event_id_fkey" FOREIGN KEY (event_id) REFERENCES events(event_id) ON DELETE CASCADE
TABLE "partial_state_events" CONSTRAINT "partial_state_events_event_id_fkey" FOREIGN KEY (event_id) REFERENCES events(event_id)
TABLE "partial_state_rooms" CONSTRAINT "partial_state_rooms_join_event_id_fkey" FOREIGN KEY (join_event_id) REFERENCES events(event_id)
TABLE "un_partial_stated_event_stream" CONSTRAINT "un_partial_stated_event_stream_event_id_fkey" FOREIGN KEY (event_id) REFERENCES events(event_id) ON DELETE CASCADE
Rules:
populate_stream_ordering2 AS
ON INSERT TO events DO UPDATE events SET stream_ordering2 = new.stream_ordering
WHERE events.stream_ordering = new.stream_ordering
Steps to reproduce
- Upgrade from Debian 11 to 12
- Start the server
Homeserver
My self-hosted server
Synapse Version
1.90.0+bullseye1
Installation Method
Debian packages from packages.matrix.org
Database
PostgreSQL, single server, never ported.
Workers
I don't know
Platform
Debian 12 (previously 11) on a VPS
Configuration
No response
Relevant log output
2023-08-27 22:28:54,839 - root - 348 - WARNING - main - ***** STARTING SERVER *****
2023-08-27 22:28:54,839 - root - 349 - WARNING - main - Server /opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/app/homeserver.py version 1.90.0
2023-08-27 22:28:54,839 - root - 354 - INFO - main - Server hostname: REDACTED
2023-08-27 22:28:54,839 - root - 355 - INFO - main - Instance name: master
2023-08-27 22:28:54,839 - root - 356 - INFO - main - Twisted reactor: EPollReactor
2023-08-27 22:28:54,839 - synapse.app.homeserver - 350 - INFO - main - Setting up server
2023-08-27 22:28:54,839 - synapse.server - 337 - INFO - main - Setting up.
2023-08-27 22:28:54,853 - synapse.storage.databases - 66 - INFO - main - [database config 'master']: Checking database server
2023-08-27 22:28:54,854 - synapse.storage.databases - 69 - INFO - main - [database config 'master']: Preparing for databases ['main', 'state']
2023-08-27 22:28:54,855 - synapse.storage.prepare_database - 120 - INFO - main - ['main', 'state']: Checking existing schema version
2023-08-27 22:28:54,857 - synapse.storage.prepare_database - 124 - INFO - main - ['main', 'state']: Existing schema is 79 (+0 deltas)
2023-08-27 22:28:54,857 - synapse.storage.databases.main - 323 - INFO - main - Checking database for consistency with configuration...
2023-08-27 22:28:54,858 - synapse.storage.prepare_database - 418 - INFO - main - Applying schema deltas for v79
2023-08-27 22:28:54,859 - synapse.storage.prepare_database - 534 - INFO - main - Applying engine-specific schema 79/03_read_write_locks_triggers.sql.postgres
2023-08-27 22:28:54,886 - synapse.storage.prepare_database - 509 - INFO - main - Running 79/04_mitigate_stream_ordering_update_race.py:run_create
2023-08-27 22:28:54,895 - synapse.app._base - 215 - ERROR - main - Exception during startup
Traceback (most recent call last):
File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/app/homeserver.py", line 353, in setup
hs.setup()
File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/server.py", line 339, in setup
self.datastores = Databases(self.DATASTORE_CLASS, self)
File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/__init__.py", line 74, in __init__
prepare_database(
File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/prepare_database.py", line 141, in prepare_database
_upgrade_existing_database(
File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/prepare_database.py", line 510, in _upgrade_existing_database
module.run_create(cur, database_engine)
File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/schema/main/delta/79/04_mitigate_stream_ordering_update_race.py", line 58, in run_create
cur.execute(add_cse_sql)
File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 417, in execute
self._do_execute(self.txn.execute, sql, parameters)
File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 471, in _do_execute
return func(sql, *args, **kwargs)
psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "events"
### Anything else that would be useful to know?
_No response_
Metadata
Metadata
Assignees
Labels
Filling in database columns, making the database eventually up-to-dateDB stuff like queries, migrations, new/remove columns, indexes, unexpected entries in the dbMost users are unlikely to come across this or unexpected workflowMajor functionality / product severely impaired, no satisfactory workaround.This issue is blocked awaiting information from the reporter