PostgreSQL: concurrent DeleteTable calls exhaust a shared pool waiting on a second connection #615

Where

crates/storage-postgres/src/worker_store.rs, process_transitions: the pass opens a catalog transaction (self.pool.begin()), selects the candidate row, and then calls drop_table_storage(&self.data_pool, ...), which begins a second transaction on data_pool. When no separate data database is configured, data_pool is a clone of the catalog pool (crates/storage-postgres/src/lib.rs, the data_conn branch), so both transactions come from one pool.

This applies to the pass DeleteTable runs for its own table (finish_table_delete, PR #504) and to the poller pass. The poller runs one pass at a time, so it needs two connections; DeleteTable calls run concurrently, one scoped pass each.

Measured

PostgreSQL 16.15, one scratch database for catalog and data, pool_size 10, no poller running, concurrent DeleteTable calls each awaiting its scoped pass (probe kept at issues/pr-sweep-2026-10-26/504-lock-timeout/v-probe-pool.rs.txt on the development host; not part of the tree):

With more concurrent calls than the pool has connections, every connection is held by a catalog transaction waiting for a second connection that none can get, until sqlx's 30 s acquire timeout fails the drop. The clients get TableStatus: DELETING (the acquire failure takes the existing "drop failed, leave the row for the poller" path), and the poller finishes the rows once connections are free. No data is lost; the cost is 30 s of latency on each call and a pool that serves nothing else for that time.

Not measured with a separate data database, where the two pools are distinct and the deadlock cannot form.

Suggested fix

Cap concurrent scoped passes at pool_size / 2 with a semaphore taken before self.pool.begin(), which leaves each pass a connection for its second acquire; backup_engine::restore_server_slot_count does the same for restores. Alternatively, when data_pool is the catalog pool, run the drop on the catalog transaction's own connection, which is a larger change to drop_table_storage.

A test: with pool_size N and one database, issue 2N concurrent DeleteTable calls on 2N tables and assert every call returns within a few seconds and no row is left DELETING after the calls return.