PostgreSQL - 禁用约束

Question

我有一个包含大约 500 万行的表，该表具有引用另一个表(也大约 500 万行)的主键的 fk 约束。

我需要从两个表中删除大约 75000 行。我知道，如果我尝试在启用 fk 约束的情况下执行此操作，将会花费 Not Acceptable 时间。

来自 Oracle 背景，我的第一个想法是禁用约束，执行删除，然后重新启用约束。如果我是 super 用户(我不是，但我以拥有/创建对象的用户身份登录)，PostGres 似乎允许我禁用约束触发器，但这似乎不是我想要的。

另一种选择是删除约束，然后恢复它。考虑到我的表的大小，我担心重建约束会花费很长时间。

有什么想法吗？

编辑:在 Billy 的鼓励下，我尝试在不更改任何约束的情况下进行删除，但它花费了超过 10 分钟的时间。但是，我发现我试图从中删除的表有一个自引用外键......重复(& 非索引)。

最终更新 - 我删除了自引用外键，删除并重新添加了它。Billy 完全正确，但不幸的是我不能接受他的评论作为答案!

Answer 1

按照之前的评论，应该是有问题的。也就是说，有一个命令可能是您正在寻找的 - 它会将约束设置为延迟，以便在 COMMIT 时检查它们，而不是在每次删除时检查它们。如果您只对所有行执行一个大的 DELETE，它不会有什么不同，但如果您是分段执行，它就会有所不同。

SET CONSTRAINTS ALL DEFERRED

就是您在那种情况下要找的东西。请注意，约束必须标记为 DEFERRABLE 才能被推迟。例如:

ALTER TABLE table_name
  ADD CONSTRAINT constraint_uk UNIQUE(column_1, column_2)
  DEFERRABLE INITIALLY IMMEDIATE;

然后可以按如下方式在事务或函数中延迟约束:

CREATE OR REPLACE FUNCTION f() RETURNS void AS
$BODY$
BEGIN
  SET CONSTRAINTS ALL DEFERRED;

  -- Code that temporarily violates the constraint...
  -- UPDATE table_name ...
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;