Two bots simultaneously calling `get_joined_members` causes error

mautrix/client/state_store/asyncpg/store.py の set_members から始め、他のデータベースの executemany パスと併せて、その PostgreSQL の copy_records_to_table パスを調査します。get_joined_members の並行呼び出しを再現し、並行更新によって重複キーエラーが発生しなくなり、サポート対象のすべてのデータベースでメンバーリストの一貫性が保たれることを確認します。

I'm using get_joined_members() for a bot to determine if a room is a direct message.

If multiple instances of the bot are running, I occasionally get this this error:

Failed to run handler
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/mautrix/client/syncer.py", line 235, in _catch_errors
    await handler(data)
  File "/usr/local/lib/python3.11/site-packages/mautrix/crypto/machine.py", line 167, in handle_device_lists
    await self._fetch_keys(device_lists.changed, include_untracked=False)
  File "/usr/local/lib/python3.11/site-packages/mautrix/crypto/device_lists.py", line 80, in _fetch_keys
    await self.crypto_store.put_devices(user_id, new_devices)
  File "/usr/local/lib/python3.11/site-packages/mautrix/crypto/store/asyncpg/store.py", line 615, in put_devices
    await conn.copy_records_to_table("crypto_device", records=data, columns=columns)
  File "/usr/local/lib/python3.11/site-packages/mautrix/util/async_db/connection.py", line 38, in wrapper
    ret = await func(self, arg, *args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/mautrix/util/async_db/connection.py", line 151, in copy_records_to_table
    return await self.wrapped.copy_records_to_table(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/asyncpg/connection.py", line 983, in copy_records_to_table
    return await self._protocol.copy_in(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 529, in copy_in
asyncpg.exceptions.UniqueViolationError: duplicate key value violates unique constraint "crypto_device_pkey"
DETAIL:  Key (user_id, device_id)=(@xxxx:yyyy, zzzz) already exists.

It looks like this is happening because set_members works by deleting a room's members from the database and then inserting the new member list returned by get_joined_members, so if this function is simultaneously called by two bots, the old rows and removed at the same time and then duplicate new rows are added which results in the error.

It seems like this could be fixed by adding an ON CONFLICT (user_id, device_id) DO NOTHING, but I haven't done a pull request because set_members uses copy_records_to_table for postgresql and executemany for the other databases and I'm not familiar enough with them to be confident.