"约束条件定义为 DEFERRABLE INITIALLY IMMEDIATE，仍然是 DEFERRED 吗？"

Question

关于这个答案，我遇到了一个无法解释的现象。

版本：
PostgreSQL 9.1.2在x86_64-unknown-linux-gnu上，由gcc-4.4.real（Debian 4.4.5-8）4.4.5编译，64位

测试环境：

CREATE TEMP TABLE t (
  id  integer
, txt text
, CONSTRAINT t_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO t VALUES
  (1, 'one')
, (2, 'two');

1) UPDATE语句修改多行：

UPDATE t
SET    id = t_old.id
FROM   t t_old
WHERE (t.id, t_old.id) IN ((1,2), (2,1));

以下的UPDATE是有效的，尽管预计它不应该生效。约束被定义为INITIALLY IMMEDIATE，而我没有使用SET CONSTRAINTS。

我是否遗漏了什么或者这是一个（相当无害的）bug？

2）修改数据的CTE

因此，修改数据的CTE也可以工作。尽管它会在使用NOT DEFERRED的主键时失败：

WITH x AS (UPDATE t SET id = 1 WHERE id = 2)
UPDATE t SET id = 2 WHERE id = 1;

我引用CTE手册中的内容：

WITH语句中的子语句是同时与主查询并发执行的。因此，在WITH语句中使用数据修改语句时，指定更新实际发生的顺序是不可预测的。所有语句都使用相同的快照（参见第13章），因此它们不能“看到”目标表上其他语句对其产生的影响。

3）在一个事务中进行多个UPDATE语句

没有SET CONSTRAINTS，这将导致唯一性冲突-正如所预期的那样。

BEGIN;
-- SET CONSTRAINTS t_pkey DEFERRED;
UPDATE t SET id = 2 WHERE txt = 'one';
UPDATE t SET id = 1 WHERE txt = 'two';
COMMIT;

- Erwin Brandstetter

---

与Windows上的9.1.3版本相同。您应该将其报告为PostgreSQL团队的错误。 - user330315

我阅读文档的方式，认为更新应该失败。我会称之为一个错误。 - Mike Sherrill 'Cat Recall'

你能参考一下文档，看看为什么“上述UPDATE语句虽然不应该工作，但却可以正常运行”吗？ - A-K

@AlexKuznetsov：请查看下面的答案。还要关注评论中的链接，并考虑Peter Eisentraut在那里的回答。 - Erwin Brandstetter

@ErwinBrandstetter说：“SQL标准规定唯一性应该仅在语句结束时强制执行...为了获得符合标准的行为，将约束声明为DEFERRABLE但不是延迟的（即INITIALLY IMMEDIATE）”-这正是您正在做的事情，因此您的更新应该成功，因为在语句结束时所有ID都是唯一的。 - A-K

我很惊讶看到著名的Erwin Brandstetter提出问题而不是回答它，然后我发现那是8年前，在当时可能只是Postgres的一个错误而导致的混淆，一切都水落石出了。 - Jared Becksfort

Answer 1

我记得在PG9处于alpha状态时，曾经提出了几乎相同的问题。这是来自Tom Lane（高调的PG核心开发者）的回答：
http://archives.postgresql.org/pgsql-general/2010-01/msg00221.php

简而言之：不会修复。

并不是说我认同您的建议，即当前行为是一个漏洞。从相反的角度看待它：是NOT DEFERRABLE的行为是不正确的。

事实上，在此UPDATE中的约束违规在任何情况下都不应该发生，因为在UPDATE结束时，约束满足了。命令结束时的状态才是最重要的。单个语句执行期间的中间状态不应暴露给用户。

看起来，PostgreSQL通过在每个更新后检查重复项来实现不可推迟的约束，如果第一个重复项失败，则立即失败，这本质上是有缺陷的。但是这是一个已知的问题，可能与PostgreSQL一样古老。 如今的解决方法正是使用DEFERRABLE约束。有些讽刺的是，您将其视为不足之处，因为它没有失败，而在某种程度上，它本来就是解决问题的方法！

自PostgreSQL 9.1以来现状的总结

• NOT DEFERRABLE UNIQUE或PRIMARY KEY约束在每个行之后进行检查。

• DEFERRABLE约束设置为IMMEDIATE（通过INITIALLY IMMEDIATE或通过SET CONSTRAINTS）在每个语句之后进行检查。

• DEFERRABLE约束设置为DEFERRED（通过INITIALLY DEFERRED或通过SET CONSTRAINTS）在每个事务之后进行检查。

请注意UNIQUE/PRIMARY KEY约束的特殊处理。引用CREATE TABLE手册页面的话:

不可延迟的约束将在每个命令之后立即进行检查。

虽然在下面的兼容性部分中，非延迟唯一性约束中进一步表示：

当UNIQUE或PRIMARY KEY约束不可延迟时， 每次插入或修改行时PostgreSQL 立即检查唯一性。 SQL标准指出应该在语句结束时强制执行唯一性; 这在多个关键值更新的单个命令的情况下会有所不同。 要获得符合标准的行为，请将约束声明为DEFERRABLE但不是延迟的（即INITIALLY IMMEDIATE）。 请注意，这可能比立即唯一性检查慢得多。

我强调了粗体字。

如果需要任何FOREIGN KEY约束引用列，则DEFERRABLE不是一个选项，因为（根据文档）:

引用列必须是被引用表中不可延迟唯一或主键约束的列。

- Daniel Vérité

Answer 2

这里可能存在一些文档错误，但并非你展示的情况。如果你开启事务并尝试一个接一个地进行更新，它们会失败，但是如果单个语句使事情保持在良好状态，则不会报错。文档如下：

如果约束可延迟，则此子句指定检查约束的默认时间。如果约束最初为立即，则每个语句后都会进行检查。这是默认设置。如果最初延迟，则仅在事务结束时才进行检查。

这正是正在发生的事情。对我来说，惊讶的是DEFERRABLE的文档，其中部分内容如下：

不可延迟的约束将在每个命令之后立即检查。

没有DEFERRABLE INITIALLY IMMEDIATE选项，即使UPDATE语句（可能构成“命令”）使事情保持在良好状态，示例更新也会失败。也许文档应该修改为说NOT DEFERRABLE的约束是通过语句逐行修改而强制执行的？

- kgrittn

---

有些延迟，我按照你建议的方式向 [e-mail] 提出了修复方案。 - Erwin Brandstetter