Hi, I found a race between DROP TABLESPACE and a concurrent command that adds a shared dependency on the tablespace. Both commands can succeed, leaving an object whose pg_class.reltablespace and pg_shdepend entries refer to a tablespace that no longer exists.
One way to reproduce this is: 1. One session updates the pg_tablespace row and keeps the transaction open. 2. A second session runs DROP TABLESPACE. It finds no dependencies, then waits while deleting the pg_tablespace tuple. 3. A third session creates a partitioned table in the tablespace and commits. 4. The first session aborts, allowing DROP TABLESPACE to finish using the result of its earlier dependency check. ISTM the race is possible because shdepAddDependency() takes an AccessShareLock on the referenced shared object and rechecks that it still exists, but DropTableSpace() calls checkSharedDependencies() without first taking the corresponding conflicting lock. DropRole() appears to follow that protocol already. For a fix, my first thought was to have DROP TABLESPACE take an AccessExclusiveLock before checking its shared dependencies. However, doing only that seems to introduce a lock-order problem with commands that update pg_tablespace without first locking the tablespace. Such a command can hold the catalog tuple while DROP TABLESPACE holds the object lock, and then try to acquire the object lock itself when the transaction records a tablespace dependency. I went through the paths that update pg_tablespace, and I think ALTER TABLESPACE RENAME/SET, DROP OWNED, and REASSIGN OWNED need to acquire an AccessShareLock before updating the catalog tuple. Direct GRANT, REVOKE, and ALTER OWNER appear to acquire an object lock already. The attached 0002 contains those changes, separately from the DROP-side change in 0001. (They likely need to be squashed once the patch looks fine). Does this AccessExclusiveLock/AccessShareLock protocol seem like the right way to close the race? Also, have I missed another pg_tablespace update path that should participate in the same protocol? Regards, Ayush
v1-0001-Prevent-orphaned-tablespace-dependencies.patch
Description: Binary data
v1-0002-Avoid-deadlocks-with-DROP-TABLESPACE.patch
Description: Binary data
