sql - 约束定义的 DEFERRABLE INITIALLY IMMEDIATE 仍然是 DEFERRED？

Question

关于 this answer我偶然发现了一个我无法解释的现象。

版本:
x86_64-unknown-linux-gnu 上的 PostgreSQL 9.1.2，由 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。

我是不是遗漏了什么或者这是一个(相当无害的)错误？

2) 数据修改CTE

因此，数据修改 CTE 也有效。虽然它失败并显示 NOT DEFERRED pk:

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

我引用 manual on CTEs :

The sub-statements in WITH are executed concurrently with each otherand with the main query. Therefore, when using data-modifyingstatements in WITH, the order in which the specified updatesactually happen is unpredictable. All the statements are executed withthe same snapshot (see Chapter 13), so they cannot "see" eachothers' effects on the target tables.

3) 一个事务中的多个 UPDATE 语句

如果没有 SET CONSTRAINTS，这会因 UNIQUE 违规而失败 - 正如预期的那样:

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

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)被检查 < strong>在每个语句之后。

• DEFERRABLE 约束设置为 DEFERRED(INITIALLY DEFERRED 或通过 SET CONSTRAINTS)被检查 < strong>在每次交易之后。

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

A constraint that is not deferrable will be checked immediately after every command.

虽然它在 Non-deferred uniqueness constraints 下的兼容性 部分进一步说明:

When a UNIQUE or PRIMARY KEY constraint is not deferrable,PostgreSQL checks for uniqueness immediately whenever a row isinserted or modified. The SQL standard says that uniqueness should beenforced only at the end of the statement; this makes a differencewhen, for example, a single command updates multiple key values. Toobtain standard-compliant behavior, declare the constraint asDEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be awarethat this can be significantly slower than immediate uniqueness checking.

大胆强调我的。

如果您需要任何FOREIGN KEY 约束来引用列，则DEFERRABLE 不是一个选项，因为(per documentation):

The referenced columns must be the columns of a non-deferrable uniqueor primary key constraint in the referenced table.