I'm trying to delete a table (called
incoming) in a busy database with 100s of transactions per second. The table itself is empty (performed a
truncate table before). If I try to do
drop table incoming a lot of transactions on various other tables are waiting for
drop table to finish - obviously not good.
I began deleting indexes, constrains and columns 1 by 1 to identify what is blocking the table deletion and got to a foreign key constraint that was causing the lock. Here's the 'final'
incoming table schema:
create table incoming ( account_id integer constraint incoming_account_id_fkey references account );
From the blocked queries (taking the
ticket table below as an example) it doesn't look like the
account table queries are blocked but the queries on
ticket - a busy table that has a foreign key to the
account table. Why would deleting a FK in
incoming block queries in
Here's the outline again on the 3 tables:
incoming- this is where the foreign key I want to delete lives (see schema below).
account- foreign key reference table.
ticket- busy table (select, insert, update, etc..) that has a foreign key constraint to
What I tried doing:
I tried disabling the triggers on the table:
ALTER TABLE incoming DISABLE TRIGGER ALL; and making the FK DEFERRABLE:
ALTER TABLE incoming ALTER CONSTRAINT incoming_account_id_fkey DEFERRABLE;. Didn't help.
Here's the output of the
SELECT conname, conindid::regclass, confrelid::regclass, * FROM pg_constraint WHERE conrelid = 'incoming'::regclass: