room_retention rows not deleted when a room is deleted, making rooms unrejoinable

### Description

If the server admin deletes a room (on another homeserver) which has a room retention event in it, and then a user re-joins the room, then the join will fail because the the delete never deleted the room retention event from the `room_retention` table in PG, and when it tries to rejoin the room, it will try to re-insert that row, causing a primary key unique constraint violation.

### Steps to reproduce

- Create a room on homeserver A
- Send a room retention event
- Delete the room on homeserver B
- Try to rejoin the room on homeserver B

When joining the room (in element-web), the homeserver B user will see an error message like:
```
Failed to join
MatrixError: [500] Internal server error (https://matrix.cyberia.club/_matrix/client/v3/join/!wEbxbxgivzSlzQWoHO%3Atent.passtheballsocrates.com)
```

### Homeserver

reproduced on both

matrix.tent.passtheballsocrates.com
and
matrix.cyberia.club

### Synapse Version

{"server_version":"1.132.0"}

### Installation Method

Docker (matrixdotorg/synapse)

### Database

postgres

### Workers

Single process

### Platform

linux

### Configuration

_No response_

### Relevant log output

```shell
synapse | psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "room_retention_pkey"
synapse | DETAIL: Key (room_id, event_id)=(!JFYMcOkcWtccikJlkA:cyberia.club, $hl2a_YdHnSUPi0TFcOQOS0qT7Wz1EVIuuBx-AWi2lA0) already exists.
```

```
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: 2025-06-18 23:53:00,529 - synapse.http.server - 146 - ERROR - POST-44968557 - Failed handle request via 'JoinRoomAliasServlet':
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: Traceback (most recent call last):
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/http/server.py", line 332, in _async_render_wrapper
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: callback_return = await self._async_render(request)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/http/server.py", line 544, in _async_render
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: callback_return = await raw_callback_return
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/rest/client/room.py", line 550, in on_POST
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return await self._do(request, requester, room_identifier, None)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/rest/client/room.py", line 531, in _do
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: await self.room_member_handler.update_membership(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/handlers/room_member.py", line 666, in update_membership
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: result = await self.update_membership_locked(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/handlers/room_member.py", line 1088, in update_membership_locked
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: remote_join_response = await self._remote_join(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/handlers/room_member.py", line 1934, in _remote_join
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: event_id, stream_id = await self.federation_handler.do_invite_join(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/handlers/federation.py", line 726, in do_invite_join
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: await self._federation_event_handler.process_remote_join(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/logging/opentracing.py", line 922, in _wrapper
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return await func(*args, **kwargs)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/handlers/federation_event.py", line 536, in process_remote_join
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: await self._auth_and_persist_outliers(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/logging/opentracing.py", line 922, in _wrapper
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return await func(*args, **kwargs)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/handlers/federation_event.py", line 1777, in _auth_and_persist_outliers
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: await self.persist_events_and_notify(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/handlers/federation_event.py", line 2281, in persist_events_and_notify
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: ) = await self._storage_controllers.persistence.persist_events(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/logging/opentracing.py", line 922, in _wrapper
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return await func(*args, **kwargs)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/controllers/persist_events.py", line 428, in persist_events
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: ret_vals = await yieldable_gather_results(enqueue, partitioned.items())
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/util/async_helpers.py", line 307, in yieldable_gather_results
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: raise dfe.subFailure.value from None
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py", line 2010, in _inlineCallbacks
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: result = context.run(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/failure.py", line 549, in throwExceptionIntoGenerator
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return g.throw(self.value.with_traceback(self.tb))
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/controllers/persist_events.py", line 423, in enqueue
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return await self._event_persist_queue.add_to_queue(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/controllers/persist_events.py", line 245, in add_to_queue
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: res = await make_deferred_yieldable(end_item.deferred.observe())
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/controllers/persist_events.py", line 288, in handle_queue_loop
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: ret = await self._per_item_callback(room_id, item.task)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/controllers/persist_events.py", line 369, in _process_event_persist_queue_task
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return await self._persist_event_batch(room_id, task)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/controllers/persist_events.py", line 646, in _persist_event_batch
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: await self.persist_events_store._persist_events_and_state_updates(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/logging/opentracing.py", line 922, in _wrapper
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return await func(*args, **kwargs)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/events.py", line 348, in _persist_events_and_state_updates
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: await self.db_pool.runInteraction(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 953, in runInteraction
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return await delay_cancellation(_runInteraction())
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py", line 2010, in _inlineCallbacks
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: result = context.run(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/failure.py", line 549, in throwExceptionIntoGenerator
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return g.throw(self.value.with_traceback(self.tb))
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 919, in _runInteraction
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: result: R = await self.runWithConnection(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 1048, in runWithConnection
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return await make_deferred_yieldable(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/threadpool.py", line 269, in inContext
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: result = inContext.theWork() # type: ignore[attr-defined]
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/threadpool.py", line 285, in
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: inContext.theWork = lambda: context.call( # type: ignore[attr-defined]
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/context.py", line 117, in callWithContext
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return self.currentContext().callWithContext(ctx, func, *args, **kw)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/context.py", line 82, in callWithContext
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return func(*args, **kw)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/enterprise/adbapi.py", line 282, in _runWithConnection
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: result = func(conn, *args, **kw)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 1041, in inner_func
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return func(db_conn, *args, **kwargs)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 781, in new_transaction
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: r = func(cursor, *args, **kwargs)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/events.py", line 1003, in _persist_events_txn
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: self._update_metadata_tables_txn(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/events.py", line 2686, in _update_metadata_tables_txn
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: self._store_retention_policy_for_room_txn(txn, event)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/databases/main/events.py", line 3142, in _store_retention_policy_for_room_txn
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: self.db_pool.simple_insert_txn(
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 1099, in simple_insert_txn
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: txn.execute(sql, vals)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 427, in execute
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: self._do_execute(self.txn.execute, sql, parameters)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 489, in _do_execute
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: return func(sql, *args, **kwargs)
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "room_retention_pkey"
Jun 18 23:53:00 matrix.cyberia.club matrix-synapse[2139993]: DETAIL: Key (room_id, event_id)=(!wEbxbxgivzSlzQWoHO:tent.passtheballsocrates.com, $3f7XTdZNkhwEp_dRb9QPmCiRLjxrdBkmstNwGC4_49A) already exists.
```

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

Please make an automated test which:

1. creates a room
2. federates the room
3. uses every feature on that room
4. deletes the room, both on the server it was created on, and on the server it federated with.
5. uses `pg_dump` to dump the DB on both servers
6. and if the room ID still exists within either dump after the deletion finishes, then the test fails.

There have been lots of bugs like this over the years. Deleting a room should really delete it, and it should be verified by an automated test.