Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Migration 79/04_mitigate_stream_ordering_update_race fails because there is no unique constraint matching given keys for referenced table "events" #16192

Open
matrixbot opened this issue Dec 21, 2023 · 0 comments
Open

Migration 79/04_mitigate_stream_ordering_update_race fails because there is no unique constraint matching given keys for referenced table "events" #16192

matrixbot opened this issue Dec 21, 2023 · 0 comments
Labels
A-Background-Updates A-Database O-Uncommon S-Major X-Needs-Info

Comments

@matrixbot
Copy link
Collaborator

matrixbot commented Dec 21, 2023 •
edited
Loading

This issue has been migrated from #16192.

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_
@matrixbot matrixbot changed the title Dummy issue Migration 79/04_mitigate_stream_ordering_update_race fails because there is no unique constraint matching given keys for referenced table "events" Dec 22, 2023
@matrixbot matrixbot reopened this Dec 22, 2023
@matrixbot matrixbot mentioned this issue Dec 22, 2023
Open
3 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
A-Background-Updates A-Database O-Uncommon S-Major X-Needs-Info
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@matrixbot