postgresql SET CONSTRAINTS ALL DEFERRED 没有按预期工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28680817/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SET CONSTRAINTS ALL DEFERRED not working as expected
提问by user79074
In a PostgreSQL 9.3 database, if I define tables aand bas follows:
在一个PostgreSQL 9.3数据库时，如果我定义表a和b如下：
CREATE TABLE a(i integer);
ALTER TABLE a ADD CONSTRAINT pkey_a PRIMARY KEY (i);
CREATE TABLE b(j integer);
ALTER TABLE b add CONSTRAINT fkey_ij FOREIGN KEY (j)
REFERENCES a (i) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE;
INSERT INTO a(i) VALUES(1);
And then do the following:
然后执行以下操作：
START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO b(j) VALUES(2);
INSERT INTO a(i) VALUES(2);
COMMIT;
It produces the error below. Why is SET CONSTRAINTSnot having the desired effect?
它产生以下错误。为什么SET CONSTRAINTS没有达到预期的效果？
ERROR: insert or update on table "b" violates foreign key constraint "fkey_ij" SQL state: 23503 Detail: Key (j)=(2) is not present in table "a".
ERROR: insert or update on table "b" violates foreign key constraint "fkey_ij" SQL state: 23503 Detail: Key (j)=(2) is not present in table "a".
采纳答案by Erwin Brandstetter
Only DEFERRABLEconstraints can be deferred.
只能DEFERRABLE推迟约束。
Let me suggest superior alternatives first:
让我首先提出更好的替代方案：
1. INSERTin order
1.INSERT按顺序
Reverse the sequence of the INSERTstatementsand nothing needs to be deferred. Simplest and fastest - if at all possible.
颠倒语句的顺序，INSERT不需要任何延迟。最简单和最快 - 如果可能的话。
2. Single command
2. 单一指令
Do it in a single command. Then still nothing needs to be deferred, as non-deferrable constraints are checked after each commandand CTEs are considered to be part of single command:
在单个命令中完成。然后仍然不需要延迟，因为在每个命令之后检查不可延迟约束，并且 CTE 被认为是单个命令的一部分：
WITH ins1 AS (
INSERT INTO b(j) VALUES(2)
)
INSERT INTO a(i) VALUES(2);
While being at it, you can reuse the values for the first INSERT; safer / more convenient for certain cases or multi-row inserts:
在此期间，您可以重用第一个的值INSERT；对于某些情况或多行插入更安全/更方便：
WITH ins1 AS (
INSERT INTO b(j) VALUES(3)
RETURNING j
)
INSERT INTO a(i)
SELECT j FROM ins1;
But I need deferred constraints! (Really?)
但我需要延迟约束！（真的？）
ALTER TABLE b ADD CONSTRAINT fkey_ij FOREIGN KEY (j)
REFERENCES a (i) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; -- !!!
Then your original code works (a bit slower, as deferred constraints add cost).
然后你的原始代码工作（有点慢，因为延迟约束会增加成本）。
db<>fiddle here
db<>在这里摆弄
Related:
有关的：
My original answer quoted the manual:
我的原始答案引用了手册：
Referential actions other than the
NO ACTIONcheck cannot be deferred, even if the constraint is declared deferrable.
NO ACTION不能推迟检查以外的引用操作，即使约束被声明为可推迟的。
But that was misleading as it only applies to "referential actions", i.e. what happens ON UPDATEor ON DELETEto rows in the referenced table. The case at hand is not one of those - as @zer0hedge pointed out.
但这具有误导性，因为它仅适用于“引用操作”，即所发生的情况ON UPDATE或ON DELETE引用表中的行。手头的案例不是其中之一 - 正如@zer0hedge 指出的那样。
回答by Alexi Theodore
I agree with others that the right way to do it is in the right order - but there are just times when that is not a feasible option and something easier is needed to get the job done within the time budget.
我同意其他人的看法，正确的做法是按正确的顺序进行——但有时这不是一个可行的选择，需要更简单的方法才能在时间预算内完成工作。
In case this helps anyone, I made a procedure that will automate adding the deferred option to all FKs so that the
如果这对任何人有帮助，我制定了一个程序，将自动将延迟选项添加到所有 FK，以便
SET CONSTRAINTS ALL DEFERRED;
设置所有延迟的约束；
command will work. Use it only as necessary of course.
命令会起作用。当然，仅在必要时使用它。
DO
$$
DECLARE
temp_rec RECORD;
sql_exe TEXT;
BEGIN
sql_exe := $sql$
ALTER TABLE %1$s ALTER CONSTRAINT %2$s DEFERRABLE;
$sql$
;
FOR temp_rec IN
(select constraint_name, table_name from information_schema.table_constraints where constraint_type = 'FOREIGN KEY')
LOOP
EXECUTE format(sql_exe, temp_rec.table_name, temp_rec.constraint_name);
END LOOP;
END;
$$
LANGUAGE plpgsql
;
