PostgreSQL 禁用约束

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

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

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

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

有什么想法吗？

在 Billy 的鼓励下，我尝试在不改变任何约束的情况下删除，这个过程需要超过10分钟。然而，我发现我试图从中删除的表有一个自引用的外键... 重复(& 非索引)。

最后更新-我删除了自引用的外键，做了删除，并添加回来。比利是对的，但不幸的是，我不能接受他的评论作为答案！

130120 次浏览
小开
最佳答案

根据之前的评论，这应该是一个问题。也就是说，有一个命令可能就是您正在寻找的——它将约束设置为延迟，因此它们将在 COMMIT 上检查，而不是在每次删除时检查。如果您只是对所有行执行一次大的删除操作，那么结果不会有什么不同，但是如果是分段执行，那么结果会有所不同。

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;
小开

禁用所有表约束

ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

——启用所有表约束

ALTER TABLE TableName CHECK CONSTRAINT ConstraintName
小开

(这个答案假设您的意图是删除这些表的所有行，而不仅仅是一个选择。)

我也不得不这样做，但作为测试套件的一部分。我找到了答案，建议 其他地方。使用 截断表如下:

TRUNCATE TABLE <list-of-table-names> [RESTART IDENTITY] [CASCADE];

下面快速删除表 table1、 table2和 table3中的所有行，前提是没有从未列出的表中引用这些表的行:

TRUNCATE TABLE table1, table2, table3;

只要所列表之间存在引用，PostgreSQL 就会删除所有行，而不会考虑参照完整性。如果列出的表以外的其他表引用这些表中的一行，则查询将失败。

但是，您可以限定查询，以便它也截断所有引用列出的表的表(尽管我还没有尝试这样做) :

TRUNCATE TABLE table1, table2, table3 CASCADE;

默认情况下，这些表的序列不会重新编号。新行将继续执行序列的下一个数字。重新启动序列号:

TRUNCATE TABLE table1, table2, table3 RESTART IDENTITY;
小开

对我有效的方法是逐一禁用 DELETE操作中涉及到的那些表的 TRIGGERS。

ALTER TABLE reference DISABLE TRIGGER ALL;
DELETE FROM reference WHERE refered_id > 1;
ALTER TABLE reference ENABLE TRIGGER ALL;

解决方案在版本9.3.16中工作。在我的案例中，执行 DELETE操作的时间从45分钟增加到14秒。

如@ampetamachine 在评论部分所述，您需要对表具有 admin特权才能执行此任务。

小开

如果您尝试 DISABLE TRIGGER ALL并得到类似于 permission denied: "RI_ConstraintTrigger_a_16428" is a system trigger的错误(我在 Amazon RDS 上得到了这个) ，请尝试以下操作:

set session_replication_role to replica;

如果成功，将禁用表约束下的所有触发器。现在该由您来确保您的更改使 DB 处于一致的状态了！

完成后，重新启用会话的触发器和约束:

set session_replication_role to default;
小开

我的 PostgreSQL 是9.6.8。

set session_replication_role to replica;

为我工作，但我需要许可。

我用超级用户登录 psql。

sudo -u postgres psql

然后连接到我的数据库

\c myDB

然后跑:

set session_replication_role to replica;

现在我可以使用约束从表中删除。


提交答案