PostgreSQL: DeleteTable's first catalog select has no lock timeout and waits behind CreateBackup #614

Where

crates/storage-postgres/src/delete_table.rs, the SELECT ... FROM tables WHERE account_id = $1 AND table_name = $2 AND table_status IN ('ACTIVE', 'CREATING') FOR UPDATE that starts every DeleteTable, on main at abf8082f (lines 27 to 41).

What happens

The select runs on a catalog transaction with no lock_timeout. It waits for any transaction that holds the table's row:

• CreateBackup holds the row FOR SHARE from its first catalog read until the data snapshot has been taken (crates/storage-postgres/src/backup_engine.rs, the comment above the FOR SHARE select explains why), so a DeleteTable issued during a backup of a large table waits for the whole snapshot.
• UpdateTable holds the row FOR UPDATE for the length of its catalog transaction (crates/storage-postgres/src/update_table.rs).

While it waits, the request holds one connection of the catalog pool. With pool_size concurrent DeleteTable calls against tables being backed up, the pool is exhausted for other requests until the backups finish or sqlx's 30 s acquire timeout fires for the later callers.

The same shape was bounded for the control-plane pass in PR #504 (a transaction-local lock_timeout on the catalog transaction, and a defined answer when it fires). This select was left as it was because it is on main and not part of that fix.

Service behaviour

On Amazon DynamoDB, DeleteTable during a CreateBackup of the same table answers 200 with TableStatus: DELETING and the backup completes (the backup is of the table state at request time). This was not re-measured for this issue; the row for it should be measured before the fix is written.

Suggested fix

Set a transaction-local lock_timeout on the DeleteTable catalog transaction before the select (the constant PR #504 introduces in worker_store.rs can move to a shared place). On lock_not_available, decide the answer from the measurement: if the service serves the delete during a backup, the fix is to not block on the backup's FOR SHARE at all (for example by reading the row without the lock and marking it DELETING with a conditional UPDATE, letting the backup's definition read stay consistent through its own snapshot); otherwise answer ResourceInUseException with the measured sentence.

A test: hold the row FOR SHARE from a second connection, call DeleteTable, expect it to return within the timeout rather than wait for the release.