Unexplained unique constraint violation on `event_push_summary_unique_index`

[DMRobertson]

https://sentry.tools.element.io/organizations/element/issues/35076/?project=2&referrer=weekly-email

First seen in 1.69.0rc2 (b=matrix-org-hotfixes,3d5242da14).

UniqueViolation: duplicate key value violates unique constraint "event_push_summary_unique_index"
DETAIL:  Key (user_id, room_id)=(@USERNAME:matrix.org, !ROOMID:matrix.org) already exists.

  File "synapse/metrics/background_process_metrics.py", line 240, in run
    return await func(*args, **kwargs)
  File "synapse/storage/databases/main/event_push_actions.py", line 1006, in _rotate_notifs
    caught_up = await self.db_pool.runInteraction(
  File "synapse/storage/database.py", line 881, in runInteraction
    return await delay_cancellation(_runInteraction())
  File "twisted/internet/defer.py", line 1656, in _inlineCallbacks
    result = current_context.run(
  File "twisted/python/failure.py", line 514, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/storage/database.py", line 848, in _runInteraction
    result = await self.runWithConnection(
  File "synapse/storage/database.py", line 976, in runWithConnection
    return await make_deferred_yieldable(
  File "twisted/python/threadpool.py", line 244, in inContext
    result = inContext.theWork()  # type: ignore[attr-defined]
  File "twisted/python/threadpool.py", line 260, in <lambda>
    inContext.theWork = lambda: context.call(  # type: ignore[attr-defined]
  File "twisted/python/context.py", line 117, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "twisted/python/context.py", line 82, in callWithContext
    return func(*args, **kw)
  File "twisted/enterprise/adbapi.py", line 282, in _runWithConnection
    result = func(conn, *args, **kw)
  File "synapse/storage/database.py", line 969, in inner_func
    return func(db_conn, *args, **kwargs)
  File "synapse/storage/database.py", line 710, in new_transaction
    r = func(cursor, *args, **kwargs)
  File "synapse/storage/databases/main/event_push_actions.py", line 1117, in _handle_new_receipts_for_notifs_txn
    self.db_pool.simple_upsert_txn(
  File "synapse/storage/database.py", line 1257, in simple_upsert_txn
    return self.simple_upsert_txn_native_upsert(
  File "synapse/storage/database.py", line 1397, in simple_upsert_txn_native_upsert
    txn.execute(sql, list(allvalues.values()))
  File "synapse/storage/database.py", line 388, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "synapse/storage/database.py", line 436, in _do_execute
    return func(sql, *args, **kwargs)

@erikjohnston wonders if this is a configuration problem and that we need to remove an old unique index.

Sounds like it's in the neighbourhood of work that @clokep has been doing?

[erikjohnston]

I think the index added in #13753, should have had a replaces_index="event_push_summary_unique_index" in it

[clokep]

I think the index added in #13753, should have had a replaces_index="event_push_summary_unique_index" in it

I think this is correct. Any recommendations on how we fix that?

[clokep]

Note that in #13776 we manually delete the event_push_summary_unique_index index. I think this was done this way in order to be backwards compatible with code which wouldn't provide the proper fields?

[erikjohnston]

I think we can move "thread_id": "main" from keyvalues to values here:

synapse/synapse/storage/databases/main/event_push_actions.py

Line 1120 in 422cff7

keyvalues={"room_id": room_id, "user_id": user_id, "thread_id": "main"},

?

[clokep]

But we update right before that, so it should be able to find a matching row:

synapse/synapse/storage/databases/main/event_push_actions.py

Lines 1106 to 1112 in 422cff7

	# First ensure that the existing rows have an updated thread_id field.
	self.db_pool.simple_update_txn(
	txn,
	table="event_push_summary",
	keyvalues={"room_id": room_id, "user_id": user_id, "thread_id": None},
	updatevalues={"thread_id": "main"},
	)

See the changes in #14045.

[erikjohnston]

OH, "thread_id": None doesn't work, as it does thread_id = NULL, which is always false :(

[reivilibre]

OH, "thread_id": None doesn't work, as it does thread_id = NULL, which is always false :(

actually always NULL IIRC 🤯 , though of course NULL is falsy in a where clause...