psycopg2.errors.DuplicateObject: constraint "event_push_actions_thread_id" for relation "event_push_actions" already exists

[smonksi]

Description

When upgrading today (June 07) from synapse 1.83 to 1.85.1, synapse fails to start with the error

psycopg2.errors.DuplicateObject: constraint "event_push_actions_thread_id" for relation "event_push_actions" already exists

If I configure for a clean install, i.e. a completely new database, the upgrade functions normally, but obviously minus all data.

Steps to reproduce

upgrade to 1.85 via git pull
Bug exists both on develop branch and on release branch release-v1.85

Homeserver

scaramellow.co.uk (currently down)

Synapse Version

1.85.1

Installation Method

Other (please mention below)

Database

Postgres version (14+238), native installation

Workers

I don't know

Platform

Installation using source code (vscode IDE)

Running both using poetry command and using vscode (via venv for debugging)

Configuration

Ubuntu 22.04 LTS
Source code

Relevant log output

Error during initialisation:
     Traceback (most recent call last):
       File "/home/si/Projects/synapse/synapse/app/homeserver.py", line 352, in setup
         hs.setup()
       File "/home/si/Projects/synapse/synapse/server.py", line 338, in setup
         self.datastores = Databases(self.DATASTORE_CLASS, self)
       File "/home/si/Projects/synapse/synapse/storage/databases/__init__.py", line 74, in __init__
         prepare_database(
       File "/home/si/Projects/synapse/synapse/storage/prepare_database.py", line 141, in prepare_database
         _upgrade_existing_database(
       File "/home/si/Projects/synapse/synapse/storage/prepare_database.py", line 535, in _upgrade_existing_database
         database_engine.execute_script_file(cur, absolute_path)
       File "/home/si/Projects/synapse/synapse/storage/engines/_base.py", line 149, in execute_script_file
         cls.executescript(cursor, f.read())
       File "/home/si/Projects/synapse/synapse/storage/engines/postgres.py", line 228, in executescript
         cursor.execute(f"COMMIT; BEGIN TRANSACTION; {script}")
       File "/home/si/Projects/synapse/synapse/storage/database.py", line 417, in execute
         self._do_execute(self.txn.execute, sql, parameters)
       File "/home/si/Projects/synapse/synapse/storage/database.py", line 469, in _do_execute
         return func(sql, *args, **kwargs)
     psycopg2.errors.DuplicateObject: constraint "event_push_actions_thread_id" for relation "event_push_actions" already exists

Anything else that would be useful to know?

Possibly related to #15736

[smonksi]

Update

This appears to be the result of a partial installation that failed, possibly due to a setup issue specific to my dev environment.

This issue was resolved by manually updating the database, removing the following constraints from the relevant tables: -

"event_push_actions_thread_id" from "event_push_actions" 
"event_push_actions_staging_thread_id" from "event_push_actions_staging"
"event_push_summary_last_receipt_stream_id_lock_check" from "event_push_summary_last_receipt_stream_id"

and then re-running the upgrade, which then worked normally.

[erikjohnston]

@smonksi is it possible that you ran Synapse from the develop branch at any point? We've been merging/reverting the change to add the constraints, which would explain seeing that error.

[smonksi]

@erikjohnston Yes, I did run from both the release and the develop branches. However my attempt with develop branch was after failing first with the main release branch. I did run poetry install --extras all when switching between branches.

[erikjohnston]

Weird, I'm not seeing any release (or release candidate) that adds the event_push_actions_thread_id constraint.

I'm going to close this for now, since you've fixed your deployment, but if anyone else sees this then please shout.

[smonksi]

Ok @erikjohnston, if I can throw any more more light on it, I'll update here.

[erikjohnston]

Thanks for filing the issue!

[smonksi]

No problem!

[MadLittleMods]

I also saw this locally on my developer copy of Synapse. Since I don't care about my data there, I just ran these commands and started Synapse again (manually poking your database comes with no warranties):

$ psql --user postgres synapse
> ALTER TABLE event_push_actions DROP CONSTRAINT event_push_actions_thread_id;
> ALTER TABLE event_push_actions_staging DROP CONSTRAINT event_push_actions_staging_thread_id;
> ALTER TABLE event_push_summary DROP CONSTRAINT event_push_summary_thread_id;
> \q

[Gabe-LSN]

$ psql --user postgres synapse
> ALTER TABLE event_push_actions DROP CONSTRAINT event_push_actions_thread_id;
> ALTER TABLE event_push_actions_staging DROP CONSTRAINT event_push_actions_staging_thread_id;
> ALTER TABLE event_push_summary DROP CONSTRAINT event_push_summary_thread_id;
> \q

I had the same issue using the docker containers from the github, this fixed my issue.
Thank you @MadLittleMods