user_dir worker database problem

[maxklenk]

Description

When running a load test on my synapse setup with workers the user_dir worker fails to update the user profiles here.

Maybe additional indexes are needed, similar errors were fixed by adding indexes in #4877

Steps to reproduce

• setup synapse with workers (at least the user_dir worker is needed)
• simulate requests from multiple users e.g. using a load test
• the worker logs an error

Version information

• Version: 1.23.0

• Install method: docker based synapse setup with workers

• Platform:
  • Python 3.8.6

• Error Log:

Nov 20 01:13:16 test-node1 matrix-synapse-user_dir:18331[22229]:
  2020-11-20 00:13:16,621 - synapse.metrics.background_process_metrics - 216 - ERROR - user_directory.notify_new_event-732 - Background process 'user_directory.notify_new_event' threw an exception
  Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/synapse/metrics/background_process_metrics.py", line 212, in run
  result = await result
  File "/usr/local/lib/python3.8/site-packages/synapse/handlers/user_directory.py", line 103, in process
  await self._unsafe_process()
  File "/usr/local/lib/python3.8/site-packages/synapse/handlers/user_directory.py", line 154, in _unsafe_process
  await self._handle_deltas(deltas)
  File "/usr/local/lib/python3.8/site-packages/synapse/handlers/user_directory.py", line 228, in _handle_deltas
  await self._handle_new_user(room_id, state_key, profile)
  File "/usr/local/lib/python3.8/site-packages/synapse/handlers/user_directory.py", line 311, in _handle_new_user
  await self.store.update_profile_in_user_dir(
  File "/usr/local/lib/python3.8/site-packages/synapse/storage/databases/main/user_directory.py", line 468, in update_profile_in_user_dir
  await self.db_pool.runInteraction(
  File "/usr/local/lib/python3.8/site-packages/synapse/storage/database.py", line 632, in runInteraction
  result = await self.runWithConnection(
  File "/usr/local/lib/python3.8/site-packages/synapse/storage/database.py", line 712, in runWithConnection
  return await make_deferred_yieldable(
  File "/usr/local/lib/python3.8/site-packages/twisted/python/threadpool.py", line 250, in inContext
  result = inContext.theWork()
  File "/usr/local/lib/python3.8/site-packages/twisted/python/threadpool.py", line 266, in <lambda>
  inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
  File "/usr/local/lib/python3.8/site-packages/twisted/python/context.py", line 122, in callWithContext
  return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/usr/local/lib/python3.8/site-packages/twisted/python/context.py", line 85, in callWithContext
  return func(*args,**kw)
  File "/usr/local/lib/python3.8/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
  compat.reraise(excValue, excTraceback)
  File "/usr/local/lib/python3.8/site-packages/twisted/python/compat.py", line 464, in reraise
  raise exception.with_traceback(traceback)
  File "/usr/local/lib/python3.8/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
  result = func(conn, *args, **kw)
  File "/usr/local/lib/python3.8/site-packages/synapse/storage/database.py", line 707, in inner_func
  return func(db_conn, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/synapse/storage/database.py", line 510, in new_transaction
  r = func(cursor, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/synapse/storage/databases/main/user_directory.py", line 381, in _update_profile_in_user_dir_txn
  new_entry = self.db_pool.simple_upsert_txn(
  File "/usr/local/lib/python3.8/site-packages/synapse/storage/database.py", line 954, in simple_upsert_txn
  self.simple_upsert_txn_native_upsert(
  File "/usr/local/lib/python3.8/site-packages/synapse/storage/database.py", line 1076, in simple_upsert_txn_native_upsert
  txn.execute(sql, list(allvalues.values()))
  File "/usr/local/lib/python3.8/site-packages/synapse/storage/database.py", line 271, in execute
  self._do_execute(self.txn.execute, sql, *args)
  File "/usr/local/lib/python3.8/site-packages/synapse/storage/database.py", line 297, in _do_execute
  return func(sql, *args)
  psycopg2.errors.InvalidColumnReference: there is no unique or exclusion constraint matching the ON CONFLICT specification

[clokep]

Could you provide a bit more about your config? How are you enabling the user directory worker?

[richvdh]

we don't have the info needed to pursue this.