PostgreSQL 10 – Deleting Foreign Key Locks Selects

postgresql

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 ticket?

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 account.

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 pg_constraint:

SELECT conname, conindid::regclass, confrelid::regclass, * FROM pg_constraint WHERE conrelid = 'incoming'::regclass:

pg_constraint output

Best Answer

Foreign key constraints are implemented as system triggers in PostgreSQL.

This query shows you the triggers behind your foreign key constraint:

SELECT t.tgrelid::regclass AS table_name,
       t.tgname AS trigger_name,
       concat(
          CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE ' WHEN 64 THEN 'INSTEAD OF ' ELSE 'AFTER ' END,
          CASE WHEN t.tgtype & 4 > 0 THEN 'INSERT' END,
          CASE WHEN t.tgtype & 8 > 0 THEN 'DELETE' END,
          CASE WHEN t.tgtype & 16 > 0 THEN 'UPDATE' END
       ) AS fires,
       f.proname AS trigger_function
FROM pg_trigger AS t
   JOIN pg_proc AS f ON t.tgfoid = f.oid
   JOIN pg_constraint AS c ON t.tgconstraint = c.oid
WHERE c.conname = 'incoming_account_id_fkey';

 table_name |         trigger_name         |     fires     |   trigger_function   
------------+------------------------------+---------------+----------------------
 account    | RI_ConstraintTrigger_a_62074 | AFTER DELETE  | RI_FKey_noaction_del
 account    | RI_ConstraintTrigger_a_62075 | AFTER UPDATE  | RI_FKey_noaction_upd
 incoming   | RI_ConstraintTrigger_c_62076 | AFTER INSERT  | RI_FKey_check_ins
 incoming   | RI_ConstraintTrigger_c_62077 | AFTER UPDATE  | RI_FKey_check_upd
(4 rows)

You see that two of these triggers are defined on the target table, account.

If you drop table incoming, that will also drop the foreign key constraint and consequently drop the triggers on account, which requires a short ACCESS EXCLUSIVE lock on account.

That should not be disruptive, but it seems that you are running long transactions that involve account. Now the DROP TRIGGER on account has to queue behind the currently active transactions that hold locks on account, and all later transactions have to queue behind the DROP TRIGGER, which is what you observe.

You will have to find a time when there are no long running transactions involving account, then your DROP TABLE will finish quickly without blocking other sessions.

Side remark: choose bigint rather than integer for artificial primary key columns.

Related Question