these tables have foreign keys on a highly referenced table and lock is required by drop
If FK constraints point to a table to be deleted, add CASCADE to also drop any such FK constraint (not the referencing tables). The manual:
(CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)
If FK constraints point from a table to be deleted (your case, as clarified in an update) then it dies with the table. Unfortunately, dropping an FK constraint also requires a brief ACCESS EXCLUSIVE lock on the referenced table. The manual:
ALTER TABLE changes the definition of an existing table. [...] An
ACCESS EXCLUSIVE lock is acquired unless explicitly noted.
This leaves room for interpretation. Indeed an ACCESS EXCLUSIVE lock is taken on both tables, referenced and referencing. (I verified in a quick test on Postgres 14.)
The lock on the referenced table is very brief and shouldn't be a problem unless there is heavy concurrent access on that table - especially if there are long-running transactions.
To avoid locking the referenced table longer than absolutely necessary, COMMIT after every DROP. Like (requires Postgres 11 or later):
DO
$do$
DECLARE
_schema name;
_tbl name;
_sql text;
BEGIN
FOR _schema, _tbl IN
SELECT schemaname, tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public' -- or what you need
AND tablename = ANY('{t2, t3}') -- array of tables to delete
LOOP
_sql := format('DROP TABLE %I.%I CASCADE', _schema, _tbl); -- CASCADE needed?
RAISE NOTICE '%', _sql;
-- EXECUTE _sql; -- un-comment once you are sure
COMMIT; -- !!!
END LOOP;
END;
$do$
I put in RAISE NOTICE and commented the actual DROP as child safety device. Un-comment the EXECUTE line (and optionally comment the RAISE) to prime the bomb.
Since that commits after every DROP, you don't collect locks along the way. In particular, that highly contested target table of your FK constraints is only blocked for a brief moment for each DROP command.
Dropping the FK constraint with ALTER TABLE ... DROP CONSTRAINT ... separately will hardly help, as that runs into the same problem. It can be an option, though, for multiple FK constraints from the same table (in separate transactions like above) - so you only have to wait for a single target table at a time.
If you still get stuck, just execute the same command again: only tables that still exist are dropped.
You could add IF EXISTS to the DROP, but that only makes sense if you expect concurrent transactions to DROP tables, which doesn't seem to apply.
If you still get stuck, take an exclusive lock on the target table in question and DROP all tables in the same transaction at once. Concurrent access on that table will be halted during this, obviously, so best at low activity times or during a maintenance window. (OTOH, if there is no concurrent access, you can simply DROP without contention anyway.)
Maybe you can identify and fix long-running transactions that don't need to stay open for that long? Those are a general burden for DBs with concurrent access in any case.
