PostgreSQL 10:删除外键锁选择

Question

我正在尝试删除繁忙数据库中的一个表(名为incoming)，该表每秒有100个事务。表本身是空的(之前执行了一个truncate table )。如果我尝试执行drop table incoming，那么许多其他表上的事务都是由等待完成的--显然不是很好。

我开始删除索引、约束和列1×1，以确定是什么阻止了表的删除，并得到了导致锁的外键约束。下面是‘最后’incoming表模式：

create table incoming
(
    account_id integer
        constraint incoming_account_id_fkey
            references account
);

从阻塞的查询中(以下面的ticket表为例)，看起来不像account表查询被阻塞，而是ticket上的查询--一个对account表有外键的繁忙表。为什么在incoming中删除FK会阻止ticket中的查询？

下面是三个表的大纲：

• incoming --这就是我想要删除的外键的位置(请参阅下面的模式)。
• account -外键引用表.
• ticket -繁忙表(选择、插入、更新等)它对account有一个外键约束。

我试着做的是：

我尝试禁用表：ALTER TABLE incoming DISABLE TRIGGER ALL;上的触发器，并使FK可推迟：ALTER TABLE incoming ALTER CONSTRAINT incoming_account_id_fkey DEFERRABLE;。帮不上忙。

下面是pg_constraint的输出：

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

Answer 1

外键约束在PostgreSQL中作为系统触发器实现。

此查询显示外键约束后面的触发器：

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)

您可以看到其中两个触发器是在目标表account上定义的。

如果删除表incoming，也会删除外键约束，从而在account上删除触发器，这需要在account上设置一个简短的ACCESS EXCLUSIVE锁。

这不应该是干扰性的，但似乎您正在运行涉及account的长事务。现在，DROP TRIGGER on account必须在持有account锁的当前活动事务之后排队，而以后的所有事务都必须在DROP TRIGGER后面排队，这就是您所观察到的。

您必须找到一个时间，即没有涉及account的长时间运行的事务，那么您的DROP TABLE将很快完成，而不会阻塞其他会话。

Side注释：为人工主键列选择bigint而不是integer。