postgresql 最初定义为 DEFERRABLE 的约束仍然是 DEFERRED？

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

提问by Erwin Brandstetter

In connection with this answerI stumbled upon a phenomenon I cannot explain.

关于这个答案，我偶然发现了一个我无法解释的现象。

Version:
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

版本：
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu，由 gcc-4.4.real (Debian 4.4.5-8) 4.4.5 编译，64 位

Consider the following demo. Testbed:

考虑以下演示。试验台：

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 statement modifying multiple rows:

1) UPDATE 语句修改多行：

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

It seems there is a bug in the current implementation? The above UPDATE works though it should not. The constraint is defined INITIALLY IMMEDIATEand I did not use SET CONSTRAINTS.

当前的实现中似乎存在错误？上面的 UPDATE 有效，但它不应该。约束已定义INITIALLY IMMEDIATE，我没有使用SET CONSTRAINTS.

Am I missing something or is this a (rather harmless) bug?

我错过了什么还是这是一个（相当无害的）错误？

---

---

2) Data modifying CTE

2) 数据修改CTE

Consequently, a data modifying CTE works, too, though it fails with a NOT DEFERREDpk:

因此，修改 CTE 的数据也可以工作，尽管它因NOT DEFERREDpk失败：

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

I quote the manual on CTEs:

我引用了CTE的手册：

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables.

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

---

---

3) Multiple UPDATE statements in one transaction

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

Without SET CONSTRAINTS, this fails with a UNIQUE violation - as expected:

没有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;

回答by Daniel Vérité

I remember having raised an almost identical point when PG9 was in alpha state. Here was the answer from Tom Lane (high-profile PG core developer): http://archives.postgresql.org/pgsql-general/2010-01/msg00221.php

我记得在 PG9 处于 alpha 状态时提出了一个几乎相同的观点。这是 Tom Lane（知名 PG 核心开发人员）的回答：http: //archives.postgresql.org/pgsql-general/2010-01/msg00221.php

In short: won't fix.

简而言之：不会修复。

Not to say that I agree with your suggestion that the current behavior is a bug. Look at it from the opposite angle: it's the behavior of NOT DEFERRABLEthat is incorrect.

并不是说我同意你的建议，即当前的行为是一个错误。从相反的角度看它：这NOT DEFERRABLE是不正确的行为。

In fact, the constraint violation in this UPDATE should never happen in any case, since at the end of the UPDATE the constraint is satisfied. The state at the end of the command is what matters. The intermediate states during the execution of a single statement should not be exposed to the user.

事实上，这个 UPDATE 中的约束违反在任何情况下都不应该发生，因为在 UPDATE 结束时约束得到满足。命令末尾的状态很重要。单个语句执行期间的中间状态不应暴露给用户。

It seems like the PostgreSQL implements the non deferrable constraint by checking for duplicates after every row updated and failing immediately upon the first duplicate, which is essentially flawed. But this is a known problem, probably as old as PostgreSQL. Nowadays the workaround for this is precisely to use a DEFERRABLE constraint. And there is some irony in that you're looking at it as deficient because it fails to fail, while somehow it's supposed to be the solution to the failure in the first place!

似乎 PostgreSQL 通过在每行更新后检查重复项并在第一次重复时立即失败来实现不可延迟约束，这本质上是有缺陷的。但这是一个已知问题，可能与 PostgreSQL 一样古老。如今，解决此问题的方法正是使用 DEFERRABLE 约束。具有讽刺意味的是，您认为它有缺陷，因为它没有失败，而不知何故，它首先应该是解决失败的方法！

---

---

Summary of the status quo in PostgreSQL 9.1

PostgreSQL 9.1 现状总结

• NOT DEFERRABLEUNIQUEor PRIMARY KEYconstraints are checked after each row.

• DEFERRABLEconstraints set to IMMEDIATE(INITIALLY IMMEDIATEor via SET CONSTRAINTS) are checked after each statement.

• DEFERRABLEconstraints set to DEFERRED(INITIALLY DEFERREDor via SET CONSTRAINTS) are checked after each transaction.

• NOT DEFERRABLEUNIQUE或PRIMARY KEY约束在每行之后检查。

• DEFERRABLE在每个语句之后检查设置为IMMEDIATE(INITIALLY IMMEDIATE或 via SET CONSTRAINTS) 的约束。

• DEFERRABLE设置为DEFERRED(INITIALLY DEFERRED或 via SET CONSTRAINTS) 的约束在每次事务后检查。

Note the special treatment of UNIQUE/ PRIMARY KEYconstraints. Quoting the manual page for CREATE TABLE:

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

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

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

While it states further down in the Compatibilitysection under Non-deferred uniqueness constraints:

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

When a UNIQUEor PRIMARY KEYconstraint is not deferrable, PostgreSQL checks for uniqueness immediatelywhenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLEbut not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking.

当 a UNIQUEorPRIMARY KEY约束不可延迟时，每当插入或修改行时，PostgreSQL 都会立即检查唯一性。SQL 标准说唯一性应该只在语句的末尾强制执行 ；例如，当单个命令更新多个键值时，这会有所不同。要获得符合标准的行为，请将约束声明为DEFERRABLE但不延迟（即INITIALLY IMMEDIATE）。请注意，这可能比立即进行唯一性检查慢得多。

Bold emphasis mine.

大胆强调我的。

If you need any FOREIGN KEYconstraints to reference the column(s), DEFERRABLEis not an option because (per documentation):

如果您需要任何FOREIGN KEY约束来引用列，DEFERRABLE这不是一个选项，因为（每个文档）：

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

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

回答by kgrittn

There may be a slight documentation bug here, but not for the case you're showing. If you BEGIN a transaction and try the updates one at time, they fail, but if a single statementleaves things in a good state, it doesn't complain. The docs say:

此处可能存在轻微的文档错误，但不适用于您所展示的情况。如果您开始一个事务并一次尝试更新，它们会失败，但是如果单个语句使事情处于良好状态，它就不会抱怨。文档说：

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction.

如果约束是可延迟的，则该子句指定检查约束的默认时间。如果约束是 INITIALLY IMMEDIATE，则在每个语句之后检查它。这是默认设置。如果约束是 INITIALLY DEFERRED，则仅在事务结束时对其进行检查。

Which is exactly what seems to be happening. What is the surprise to me, given the documentation of DEFERRABLE, which says in part:

这正是似乎正在发生的事情。考虑到 的文档，我有什么惊喜DEFERRABLE，其中部分内容是：

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

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

Without the DEFERRABLE INITIALLY IMMEDIATEoptions, the example update fails, even though the UPDATEstatement (presumably constituting the "command") leaves things in a good state. Perhaps the docs should be modified to say that a NOT DEFERRABLEconstraint is enforced as each row is modifiedby a statement?

如果没有DEFERRABLE INITIALLY IMMEDIATE选项，即使UPDATE语句（大概构成“命令”）使事情处于良好状态，示例更新也会失败。也许应该修改文档以说明在每行都由语句修改时NOT DEFERRABLE强制执行约束？