PostgreSQL - 禁用约束 [英] PostgreSQL - disabling constraints

问题描述

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

我需要从两个表中删除大约75000行。我知道，如果我尝试这样做与fk约束启用它将花费不可接受的时间量。

从Oracle背景中我的第一个想法是禁用约束，做删除&然后重新启用约束。 PostGres似乎允许我禁用约束触发器，如果​​我是一个超级用户（我不是，但我是作为拥有/创建对象的用户登录），但似乎不是我想要的。 p>

另一个选项是删除约束，然后重新启动它。我担心重建约束会根据我的表的大小而变。

任何想法？

编辑：在比利的鼓励之后，我试着做删除，而不改变任何约束，它需要超过10分钟。但是，我发现我要删除的表有一个自引用外键...重复（&非索引）。

最后更新

解决方案

按照以前的注释，这应该是一个问题。也就是说，有一个命令可能是你正在寻找的 - 它将设置约束以延迟，因此他们检查COMMIT，而不是每次删除。如果你只是对所有的行做一个大的删除，它不会有什么区别，但如果你这样做，它会的。

  SET CONSTRAINTS ALL DEFERRED 
  

案件。请注意，约束必须标记为 DEFERRABLE ，才能被延迟。例如：

  ALTER TABLE table_name 
 ADD CONSTRAINT constraint_uk UNIQUE（column_1，column_2）
 DEFERRABLE INITILEY IMMEDIATE ; 
  

然后可以在事务或函数中推迟约束，如下所示：

  CREATE或REPLACE FUNCTION f（）返回void AS 
 $ BODY $ 
 BEGIN 
 SET CONSTRAINTS ALL DEFERRED; 
 
  - 临时违反约束的代码... 
  -  UPDATE table_name ... 
 END; 
 $ BODY $ 
 LANGUAGE plpgsql VOLATILE 
 COST 100; 
  

I have a table with approx 5 million rows which has a fk constraint referencing the primary key of another table (also approx 5 million rows).

I need to delete about 75000 rows from both tables. I know that if I try doing this with the fk constraint enabled it's going to take an unacceptable amount of time.

Coming from an Oracle background my first thought was to disable the constraint, do the delete & then reenable the constraint. PostGres appears to let me disable constraint triggers if I am a super user (I'm not, but I am logging in as the user that owns/created the objects) but that doesn't seem to be quite what I want.

The other option is to drop the constraint and then reinstate it. I'm worried that rebuilding the constraint is going to take ages given the size of my tables.

Any thoughts?

edit: after Billy's encouragement I've tried doing the delete without changing any constraints and it takes in excess of 10 minutes. However, I have discovered that the table from which I'm trying to delete has a self referential foreign key ... duplicated (& non indexed).

Final update - I dropped the self referential foreign key, did my delete and added it back in. Billy's right all round but unfortunately I can't accept his comment as the answer!

解决方案

Per previous comments, it should be a problem. That said, there is a command that may be what you're looking to - it'll set the constraints to deferred so they're checked on COMMIT, not on every delete. If you're doing just one big DELETE of all the rows, it won't make a difference, but if you're doing it in pieces, it will.

SET CONSTRAINTS ALL DEFERRED

is what you are looking for in that case. Note that constraints must be marked as DEFERRABLE before they can be deferred. For example:

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

The constraint can then be deferred in a transaction or function as follows:

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;

这篇关于PostgreSQL - 禁用约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！