PostgreSQL: DeleteTable interrupted between the catalog commit and the storage drop leaves orphan data tables #449

Summary

On PostgreSQL, DeleteTable deletes and commits the catalog rows first and drops the data tables afterwards, inside the request future. If the request is interrupted between the two, the base and index data tables (_ddb_<id>) are left in the data database with no catalog row, and nothing removes them later.

Reproduction

PostgreSQL backend, a table with one GSI and 2,000 items.

1. In a psql session on the data database: BEGIN; LOCK TABLE "_ddb_<table_id>" IN ACCESS SHARE MODE; and leave it open. Any client transaction that has read the table holds the same lock, so this stands in for a slow reader.
2. From a boto3 client with a 5 s read timeout: DeleteTable.
3. The client gets ReadTimeoutError. In the catalog, the tables and indexes rows are gone. In the data database, both _ddb_ tables are present and pg_stat_activity shows the DROP TABLE waiting.
4. Release the lock. The DROP TABLE backend disappears from pg_stat_activity and the tables are still there: the client's disconnect dropped the handler future and with it the sqlx transaction, so the drop was rolled back when the lock was granted.

The same end state with a server kill instead of a client timeout: kill -9 the server while the drop is waiting, release the lock, restart. After 75 s both tables are still present. The restarted server's startup pass (process_control_plane_transitions, worker_store.rs) acts on DELETING rows only, and this row was already deleted. The deferred delete path in worker_store.rs has the same commit-then-drop order.

Measured on cc981e2d (main) and on the #401 branch at 8c317624. On 8c317624 the vector_index_holds row the drop inserts is cleared by the sweep after 10 minutes; the tables are not.

A client cannot see the leak: DescribeTable and PutItem on the name answer ResourceNotFoundException, and CreateTable with the same name succeeds (a new table id). The orphan tables hold the deleted table's items and index rows in the data database until an operator finds them.

What would close it

Two changes, each of which covers one of the two interruptions:

• Run the storage drop in a spawned task, as UpdateTable runs the GSI build (update_table.rs, so a client disconnect cannot cancel it part way), so a client timeout no longer rolls the drop back.
• Keep the table row as DELETING until the drop has committed, so the control-plane poller finishes a drop a crash interrupted; or have the startup pass drop any _ddb_ table whose id has no catalog row.

Where it came up

Found while reviewing #401, which rewrites the drop order to avoid a deadlock with the GSI queue worker. #401 keeps the order in which the catalog delete and the storage drop commit, so the leak is the same on main and on that branch.