PostgreSQL - 禁用约束

Question

PostgreSQL - 禁用约束

postgresqlconstraints

87

我有一张表大约有500万行，其中有一个外键约束引用了另一张表的主键（也有大约500万行）。

我需要从这两个表中删除大约75000行。我知道如果启用外键约束来执行这个操作，将会花费无法接受的时间。

来自Oracle背景的我第一个想到的是禁用约束、删除、然后重新启用约束。PostGres似乎允许我在超级用户身份下禁用约束触发器（我不是，但我作为拥有/创建对象的用户登录），但那似乎不是我想要的。

另一个选择是删除该约束，然后重新添加它。我担心由于我的表格大小重建约束将需要很长时间。

有什么想法吗？

编辑：在Billy的鼓励下，我尝试进行删除而不更改任何约束，并且它超过了10分钟。但是，我发现我要删除的表具有自引用外键...重复（且非索引化）。

最后更新- 我删除了自引用外键，执行了删除操作，然后重新添加了它。Billy的解答都是对的，但不幸的是我不能将他的评论作为答案！

- azp74

4

如果处理5百万行数据需要这么长时间，那么很可能设置有误。 - Billy ONeal

什么？是删除还是重新启用约束条件？是的，很可能某些设置存在问题或者不够优化——这个数据库基本上是由Hibernate“构建”的（我与此无关）。 - azp74

14

从索引表中删除外键检查需要线性时间，删除75000+75000行 = 150000行。考虑最坏情况下每个FK检查需要19次比较（二分查找，lg（500万）= 19），以及每行比较可能需要20次机器比较，总共需要57000000次比较。假设每秒平均能进行十亿次比较的机器数量非常保守，这仍然应该在不到1秒的CPU时间内完成。从磁盘加载也不应该是主要问题，因为即使在500万行的情况下，表格也应该适合内存。 - Billy ONeal

1

好的Billy - 我会再尝试一次彻底删除...我相当确定上次我尝试它时（这项工作是我一个月前回来继续的）速度非常慢。 - azp74

6个回答

46

对我有效的方法是逐个禁用那些将参与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秒。

正如@amphetamachine在评论部分所述，您需要拥有对表的admin权限才能执行此任务。

- gersonZaragocin

1

请注意，执行“ALTER TABLE”命令的PostgreSQL用户必须是该表的所有者。 - amphetamachine

对我来说，执行ALTER TABLE命令非常缓慢。 - Wart

3

这并没有禁用“UNIQUE”验证，否则我可能做错了什么（也许“UNIQUE”约束没有被管理为“TRIGGER”）。 - Nate Anderson

3

@TheRedPea：也许是一个不可延迟的唯一约束...这个页面有更多信息：如何在我的postgresql中禁用所有约束？："...不可延迟的主键、唯一和排除约束没有关联的触发器，也不受影响。" - sol

这对我来说一切都很顺利。当触发器重新启用时，约束条件是否会再次进行检查，还是需要手动执行此操作？ - John Clements

显示剩余2条评论

27

如果您尝试DISABLE TRIGGER ALL并收到如下错误：permission denied: "RI_ConstraintTrigger_a_16428" is a system trigger（我在Amazon RDS上遇到了这个问题），请尝试以下方法：

set session_replication_role to replica;

如果成功，所有支撑表约束的触发器都会被禁用。现在你需要确保你的更改让数据库处于一致状态！完成后，使用以下命令重新启用会话中的触发器和约束：

```sql ENABLE TRIGGER ALL; ENABLE CONSTRAINT ALL; ```

set session_replication_role to default;

- Jonathan Fuerth

这不适用于最新版本的PostgreSQL。 - G.Dealmeida

2

在Postgres 12上运行良好。请注意，您需要是超级用户才能设置session_replication_role。 - eric.green

我已经在使用root了，但对我没有用。 - tom10271

用户“root”在postgresql中可能不是超级用户。默认的超级用户通常被称为“postgres”，所以您可以尝试使用它。您甚至可以使用“createuser --superuser”命令在数据库中创建一个新的超级用户来完成此任务，然后再删除该用户。 - Jonathan Fuerth

9

（本答案假设您的意图是删除这些表的所有行，而不仅仅是选择。）

我也曾经这样做，但那是作为测试套件的一部分。我找到了答案，并在 Stack Overflow上建议。使用 TRUNCATE TABLE如下：

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;

- Joe Lapp

6

我的PostgreSQL版本是9.6.8。

set session_replication_role to replica;

我的工作需要权限才能执行。

我使用超级用户登录psql。

sudo -u postgres psql

然后连接到我的数据库。

\c myDB

并运行：

set session_replication_role to replica;

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

- Weekend Baf

-12

禁用所有表约束

ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

-- 启用所有表约束

ALTER TABLE TableName CHECK CONSTRAINT ConstraintName

- Alam Usmani

4

PostgreSQL目前（截至v9.4）没有这个功能。 - Paul R Rogers

同意v9.4没有这个功能错误：在“NOCHECK”附近有语法错误第1行：ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName - happy_marmoset

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Magnus Hagander · Accepted Answer

根据之前的评论，这应该是一个问题。话说，有一条命令可能是你要找的 - 它会将约束设置为延迟，因此它们会在提交时进行检查，而不是在每次删除时进行检查。如果您只是删除所有行的一个大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;