Repository navigation
PostgreSQL: DeleteTable interrupted between the catalog commit and the storage drop leaves orphan data tables #449
Description
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.
- In a
psqlsession 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. - From a boto3 client with a 5 s read timeout:
DeleteTable. - The client gets
ReadTimeoutError. In the catalog, thetablesandindexesrows are gone. In the data database, both_ddb_tables are present andpg_stat_activityshows theDROP TABLEwaiting. - Release the lock. The
DROP TABLEbackend disappears frompg_stat_activityand 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
UpdateTableruns 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
DELETINGuntil 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.
Activity
fix(postgres): keep a deleted table DELETING until its data tables ar…
fix(postgres): keep a deleted table DELETING until its data tables ar…
fix(postgres): keep a separate-database DeleteTable DELETING until th…
hitesh96db commentedon Nov 9, 2026
Status check against
mainat 35edb77. I read the code; I did not re-run the reproduction.Already fixed on main (mostly by #395)
tokio::spawned task, so a dropped request no longer rolls the drop back. This is the first change proposed above.ACCESS SHARE. An in-use probe and the 1 sSYNC_DROP_LOCK_TIMEOUTsend DeleteTable down the asynchronous path. The row staysDELETINGand the poller drops the tables later.worker_store.rs. It now drops the data tables first and deletes the row only after the drop succeeds, so the commit-then-drop order is gone there.Still open on main: separate data database, which is what
extenddb initcreatesbootstrapper.rsalways recordsdata_database_connection_string, so a default deployment takes this path.delete_table.rsstill deletes and commits the catalog row before the data commit. Two cases still leave_ddb_<id>tables with no catalog row:DATA_DROP_RETRY_BOUND(60 s).Main documents this instead of fixing it. The comment on
DATA_DROP_RETRY_BOUNDsays "Nothing in the server drops such relations later", anddocs/troubleshooting.mdhas a manual cleanup section ("Data tables without a catalog row").#504 closes the remaining cases
On the separate data database path, #504 marks the row
DELETINGand commits it, drops the data tables, and removes the row only after that drop commits. After a process death or a data database outage past the 60 s window, the row staysDELETINGwith its transition due. The startup pass, or a later poller pass, then drops the tables and removes the row. A row names the data tables until they are dropped, so none can be orphaned. #504 also updates theDATA_DROP_RETRY_BOUNDcomment and the troubleshooting section. It shows 35/35 checks passing on 47e95b1.Before closing this issue on #504's merge:
_ddb_tables with no catalog row (the other option above). The updated troubleshooting text says relations orphaned by earlier builds have to be dropped by hand. That is fine if no affected build has been deployed; otherwise it needs a follow-up issue or a release note.delete_table.rsandworker_store.rschanged on main after its head, for example feat: store and report WarmThroughput on tables and global secondary indexes #474 (WarmThroughput addedwarm_throughputto the sameSELECT ... FOR UPDATE) and feat(backup): catalog index, BackupCatalog, JobClaim, legacy reader [backup/restore 6] #671. The green CI is against the old base, so the tests should be re-run after the rebase.I have not run #504's tests myself.