psycopg2 event_push_summary_unique_index key duplicated

[Gredin67]

Description

My mautrix_whatsapp bridge is working irregularly, dropping most of the messages.
In synapse logs, the key of my mautrix_whatsapp relaybot user looks duplicated.

Steps to reproduce

• restore an old (9 months) backup of the mautrix-whatsapp bridge postgreSQL DB
• upgrade the bridge from v0.2.3 to v0.4.0 and then to v0.8.0
• the bridge is registered on an untouched synapse instance

Homeserver

sans-nuage.fr

Synapse Version

1.74.0

Installation Method

Other (please mention below)

Database

single PostgreSQL server

Workers

Single process

Platform

YunoHost 11.0.11

Configuration

No response

Relevant log output

psycopg2.errors.UniqueViolation: ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « event_push_summary_unique_index »
janv. 23 23:50:47 domain.tld python[22652]: DETAIL:  La clé « (user_id, room_id)=(@relaybot:domain.tld, !ididididiididi:domain.tld) » existe déjà.

### Anything else that would be useful to know?

_No response_

[squahtx]

Could you provide the stack trace that gets logged before this line?

psycopg2.errors.UniqueViolation: ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « event_push_summary_unique_index »

[Gredin67]

Jan 24 17:32:27 sans-nuage.fr python[12457]: 2023-01-24 17:32:27,331 - synapse.crypto.keyring - 822 - WARNING - ServerKeyFetcher-34621- Error looking up keys from ahlosclub.com: Failed to send request: ConnectionRefusedError: Connection was refused by other side: 111: Connection refused.
Jan 24 17:32:31 sans-nuage.fr python[12457]: 2023-01-24 17:32:31,970 - synapse.metrics.background_process_metrics - 244 - ERROR - rotate_notifs-2103- Background process 'rotate_notifs' threw an exception
Jan 24 17:32:31 sans-nuage.fr python[12457]: Traceback (most recent call last):
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/metrics/background_process_metrics.py", line 242, in run
Jan 24 17:32:31 sans-nuage.fr python[12457]:     return await func(*args, **kwargs)
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/event_push_actions.py", line 1456, in _rotate_notifs
Jan 24 17:32:31 sans-nuage.fr python[12457]:     caught_up = await self.db_pool.runInteraction(
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 882, in runInteraction
Jan 24 17:32:31 sans-nuage.fr python[12457]:     return await delay_cancellation(_runInteraction())
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py", line 1693, in _inlineCallbacks
Jan 24 17:32:31 sans-nuage.fr python[12457]:     result = context.run(
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/twisted/python/failure.py", line 518, in throwExceptionIntoGenerator
Jan 24 17:32:31 sans-nuage.fr python[12457]:     return g.throw(self.type, self.value, self.tb)
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 849, in _runInteraction
Jan 24 17:32:31 sans-nuage.fr python[12457]:     result = await self.runWithConnection(
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 977, in runWithConnection
Jan 24 17:32:31 sans-nuage.fr python[12457]:     return await make_deferred_yieldable(
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/twisted/python/threadpool.py", line 244, in inContext
Jan 24 17:32:31 sans-nuage.fr python[12457]:     result = inContext.theWork()  # type: ignore[attr-defined]
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/twisted/python/threadpool.py", line 260, in <lambda>
Jan 24 17:32:31 sans-nuage.fr python[12457]:     inContext.theWork = lambda: context.call(  # type: ignore[attr-defined]
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/twisted/python/context.py", line 117, in callWithContext
Jan 24 17:32:31 sans-nuage.fr python[12457]:     return self.currentContext().callWithContext(ctx, func, *args, **kw)
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/twisted/python/context.py", line 82, in callWithContext
Jan 24 17:32:31 sans-nuage.fr python[12457]:     return func(*args, **kw)
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/twisted/enterprise/adbapi.py", line 282, in _runWithConnection
Jan 24 17:32:31 sans-nuage.fr python[12457]:     result = func(conn, *args, **kw)
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 970, in inner_func
Jan 24 17:32:31 sans-nuage.fr python[12457]:     return func(db_conn, *args, **kwargs)
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 711, in new_transaction
Jan 24 17:32:31 sans-nuage.fr python[12457]:     r = func(cursor, *args, **kwargs)
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/event_push_actions.py", line 1679, in _rotate_notifs_txn
Jan 24 17:32:31 sans-nuage.fr python[12457]:     self._rotate_notifs_before_txn(
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/event_push_actions.py", line 1792, in _rotate_notifs_before_txn
Jan 24 17:32:31 sans-nuage.fr python[12457]:     self.db_pool.simple_upsert_many_txn(
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 1443, in simple_upsert_many_txn
Jan 24 17:32:31 sans-nuage.fr python[12457]:     return self.simple_upsert_many_txn_native_upsert(
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 1541, in simple_upsert_many_txn_native_upsert
Jan 24 17:32:31 sans-nuage.fr python[12457]:     txn.execute_values(sql, args, fetch=False)
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 382, in execute_values
Jan 24 17:32:31 sans-nuage.fr python[12457]:     return self._do_execute(
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 436, in _do_execute
Jan 24 17:32:31 sans-nuage.fr python[12457]:     return func(sql, *args, **kwargs)
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 383, in <lambda>
Jan 24 17:32:31 sans-nuage.fr python[12457]:     lambda the_sql: execute_values(self.txn, the_sql, values, fetch=fetch),
Jan 24 17:32:31 sans-nuage.fr python[12457]:   File "/opt/yunohost/matrix-synapse/lib/python3.9/site-packages/psycopg2/extras.py", line 1299, in execute_values
Jan 24 17:32:31 sans-nuage.fr python[12457]:     cur.execute(b''.join(parts))
Jan 24 17:32:31 sans-nuage.fr python[12457]: psycopg2.errors.UniqueViolation: ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « event_push_summary_unique_index »
Jan 24 17:32:31 sans-nuage.fr python[12457]: DETAIL:  La clé « (user_id, room_id)=(@relaybot:sans-nuage.fr, !xxxxxxxx:sans-nuage.fr) » existe déjà.
Jan 24 17:32:32 sans-nuage.fr python[12457]: 2023-01-24 17:32:32,539 - synapse.http.matrixfederationclient - 278 - WARNING - _maybe_retry_device_resync-2106- {GET-O-225773} [dead.sh] Error reading response GET matrix://dead.sh/_matrix/federation/v1/user/devices/%40lued%3Adead.sh: Failed to send request: RuntimeError: Remote server sent Content-Type header of 'text/html; charset=utf-8', not 'application/json'
Jan 24 17:32:32 sans-nuage.fr python[12457]: 2023-01-24 17:32:32,539 - synapse.handlers.device - 1191 - WARNING - _maybe_retry_device_resync-2106- Failed to handle device list update for @lued:dead.sh: Failed to send request: RuntimeError: Remote server sent Content-Type header of 'text/html; charset=utf-8', not 'application/json'

[clokep]

Maybe related to #14120?

[squahtx]

Turns out this is already fixed by #14669, which is in Synapse 1.75.
Upgrading should fix it.