Skip to content
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
@gpetters94

Description

@gpetters94

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

No one assigned

    Labels

    A-Background-UpdatesFilling in database columns, making the database eventually up-to-dateA-DatabaseDB stuff like queries, migrations, new/remove columns, indexes, unexpected entries in the dbO-UncommonMost users are unlikely to come across this or unexpected workflowS-MajorMajor functionality / product severely impaired, no satisfactory workaround.X-Needs-InfoThis issue is blocked awaiting information from the reporter

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions