Avoid exclusive access locks on referenced tables when DROPping in PostgreSQL

Question

Why does dropping a table in PostgreSQL require ACCESS EXCLUSIVE locks on any referenced tables? How can I reduce this to an ACCESS SHARED lock or no lock at all? i.e. is there a way to drop a relation without locking the referenced table?

I can't find any mention of which locks are required in the documentation, but unless I explicitly get locks in the correct order when dropping multiple tables during concurrent operations, I can see deadlocks waiting on an AccessExclusiveLock in the logs, and acquiring this restrictive lock on commonly-referenced tables is causing momentary delays to other processes when tables are deleted.

To clarify,

CREATE TABLE base (
    id SERIAL,
    PRIMARY KEY (id)
);
CREATE TABLE main (
    id SERIAL,
    base_id INT,
    PRIMARY KEY (id),
    CONSTRAINT fk_main_base (base_id)
        REFERENCES base (id)
        ON DELETE CASCADE ON UPDATE CASCADE
);
DROP TABLE main; -- why does this need to lock base?

Answer 1

For anyone googling and trying to understand why their drop table (or drop foreign key or add foreign key) got stuck for a long time:

PostgreSQL (I looked at versions 9.4 to 13) foreign key constraints are actually implemented using triggers on both ends of the foreign key.

If you have a company table (id as primary key) and a bank_account table (id as primary key, company_id as foreign key pointing to company.id), then there are actually 2 triggers on the bank_account table and also 2 triggers on the company table.

table_name	timing	trigger_name	function_name
bank_account	AFTER UPDATE	RI_ConstraintTrigger_c_1515961	RI_FKey_check_upd
bank_account	AFTER INSERT	RI_ConstraintTrigger_c_1515960	RI_FKey_check_ins
company	AFTER UPDATE	RI_ConstraintTrigger_a_1515959	RI_FKey_noaction_upd
company	AFTER DELETE	RI_ConstraintTrigger_a_1515958	RI_FKey_noaction_del

Initial creation of those triggers (when creating the foreing key) requires SHARE ROW EXCLUSIVE lock on those tables (it used to be ACCESS EXCLUSIVE lock in version 9.4 and earlier). This lock does not conflict with "data reading locks", but will conflict with all other locks, for example a simple INSERT/UPDATE/DELETE into company table.

Deletion of those triggers (when droping the foreign key, or the whole table) requires ACCESS EXCLUSIVE lock on those tables. This lock conflicts with every other lock!

So imagine a scenario, where you have a transaction A running that first did a simple SELECT from company table (causing it to hold an ACCESS SHARE lock for company table until the transaction is commited or rolled back) and is now doing some other work for 3 minutes. You try to drop the bank_account table in transaction B. This requires ACCESS EXCLUSIVE lock, which will need to wait until the ACCESS SHARE lock is released first. In addition of that all other transactions, which want to access the company table (just SELECT, or maybe INSERT/UPDATE/DELETE), will be queued to wait on the ACCESS EXCLUSIVE lock, which is waiting on the ACCESS SHARE lock.

Long running transactions and DDL changes require delicate handling.