Repository navigation
PostgreSQL: concurrent DeleteTable calls exhaust a shared pool waiting on a second connection #615
Description
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):
| Concurrent DeleteTable calls | Response time | Rows left DELETING |
|---|---|---|
| 5 | all within 0.12 s | 0 |
| 24 | all 24 answered after 30.07 s | 20 |
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.
Activity
LeeroyHannigan commentedon Oct 31, 2026
The part of this issue about concurrent DeleteTable calls, and the measurements above, are of an earlier version of PR #504 that ran a scoped pass per DeleteTable (
finish_table_delete). PR #504 no longer has either.On main since #395, a DeleteTable on one shared database runs its in-use probe and its drops on the catalog transaction's own connection (
delete_table.rslines 185 and 219 on main at1d0d8f4a2), which is the second fix suggested above. Each call holds one connection.concurrent_delete_tables_do_not_exhaust_the_poolintests/table_snapshot.rsruns 20 concurrent DeleteTable calls against an engine withpool_size8, on both layouts, and requires all of them to return within 5 s. It passes in main's CI run for1d0d8f4a2and on the PR #504 branch.The poller pass still takes two connections from one pool. The pass is now
control_plane_pass(worker_store.rsline 76). It probes on its own catalog connection (line 239) and then callsdrop_table_storage(&self.data_pool, ...)(line 258), and with no separate data databasedata_poolis a clone of the catalog pool (lib.rslines 383 to 386). The poller runs one pass at a time. A DeleteTable or UpdateTable of a table the pass has locked holds a connection while it waits for that row. I haven't measured whether enough of those can fill the pool before the pass gets its second connection.LeeroyHannigan commentedon Nov 3, 2026
The scoped pass per DeleteTable that this issue measured was in an earlier version of PR #504 and never reached main: neither
finish_table_deletenorprocess_transitionsis on main. On main, a DeleteTable on one shared database probes and drops on its own catalog connection (since #395), andconcurrent_delete_tables_do_not_exhaust_the_poolincrates/storage-postgres/tests/table_snapshot.rsruns 20 concurrent DeleteTable calls on both layouts. The poller'scontrol_plane_passstill takes its drop connection from the same pool, as the comment above describes. No failure of that has been measured; a measured one should get its own issue.