Postgres何时检查唯一约束？

Question

我有一个列名为sort_order的列，它有一个唯一约束条件。 以下SQL在Postgres 9.5上失败：

UPDATE test
SET sort_order = sort_order + 1;

-- [23505] ERROR: duplicate key value violates unique constraint "test_sort_order_key"
--   Detail: Key (sort_order)=(2) already exists.

显然，如果在更新前sort_order的值是唯一的，那么更新后它们仍将保持唯一。为什么呢？

在Oracle和MS SQL上相同的语句可以正常工作，但在MySQL和SQLite上会失败。

---

下面是SQL Fiddle的完整设置代码：

DROP TABLE IF EXISTS test;
CREATE TABLE test (
  val        TEXT,
  sort_order INTEGER NOT NULL UNIQUE
);

INSERT INTO test
VALUES ('A', 1), ('B', 2);

- blubb

Answer 1

Postgres决定在不同的时间检查类型为IMMEDIATELY的约束，而不是SQL标准中提出的时间。具体来说，SET CONSTRAINTS的文档说明（重点在于我）：“当插入或修改行时，NOT NULL和CHECK约束总是立即检查（而不是在语句结束时）。未声明DEFERRABLE的唯一性和排他性约束也会立即检查。” Postgres选择使用导致sort_order临时冲突并立即失败的计划执行此查询。注意，这意味着对于相同的模式和数据，相同的查询可能会根据执行计划而成功或失败。 您需要将约束设置为DEFERRABLE或DEFERRABLE INITIALLY DEFERRED，以延迟验证约束直到事务结束或执行语句SET CONSTRAINTS ... IMMEDIATE的点。 @HansGinzel的评论补充说明： 对于COPY，似乎在复制所有数据后才测试（即使是立即）约束。

- blubb

---

Postgres没有“对IMMEDIATELY的含义有不同的想法”。这是在检查唯一约束条件的实现中与标准文档记录的差异。 - ypercubeᵀᴹ

@ypercubeᵀᴹ：谢谢，我已经更正了措辞。你知道在SQL标准的哪里规定了这一点吗？这样回答会更加完整。不幸的是，我没有它的副本。请随意编辑我的答案 :-) - blubb

对于COPY操作，似乎即使使用IMMEDIATE选项，约束也是在所有数据被复制后才进行测试。 - Hans Ginzel

@HansGinzel 谢谢，这是一个有趣的事实！我会把它包含在答案中，让它更加显眼。 - blubb