Two bots simultaneously calling `get_joined_members` causes error

Commencez par set_members dans mautrix/client/state_store/asyncpg/store.py et examinez son chemin PostgreSQL copy_records_to_table ainsi que les chemins executemany pour les autres bases de données. Reproduisez des appels get_joined_members concurrents et vérifiez que les mises à jour concurrentes ne produisent plus d’erreurs de clé en double, tout en préservant la liste des membres de manière cohérente dans toutes les bases de données prises en charge.

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.