PostgreSQL - 禁用约束

admin

PostgreSQL - 禁用约束

sql

我有一个大约有 500 万行的表，它有一个引用另一个表的主键的 fk 约束（也大约有 500 万行）。

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

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

另一种选择是删除约束，然后恢复它。鉴于我的表的大小，我担心重建约束需要很长时间。

有什么想法吗？

编辑：在比利的鼓励下，我尝试在不改变任何约束的情况下进行删除，这需要超过 10 分钟。但是，我发现我试图从中删除的表有一个自引用外键......重复（& 非索引）。

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

阅读 225

2021-07-01

共1个答案

admin

根据之前的评论，这应该是一个问题。也就是说，有一个命令可能正是您想要的——它会将约束设置为延迟，以便在 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;

2021-07-01