postgresql – 约束定义DEFERRABLE INITIALLY IMMEDIATE仍然是DEFERRED？

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

版：
Postgresql 9.1.2 on 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),1));

在当前的实现中似乎有一个bug？上面的UPDATE工作虽然不应该.约束是立即初始化,我没有使用SET CONSTRAINTS.

我错过了什么,还是这个(相当无害)的bug？

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 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.

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;

当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 DEFERYABLE UNIQUE或PRIMARY KEY约束在每行之后被检查.
>每个语句后都会检查设置为立即(立即初始或通过SET约束)的DEFERRABLE约束.
> DEFACTABLE约束设置为DEFERRED(INITIALLY DEFERRED或通过SET CONSTRAINTS)在每个事务之后被检查.

请注意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@H_502_73@ or PRIMARY KEY@H_502_73@ constraint is not deferrable,Postgresql checks for uniqueness immediately whenever 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 DEFERRABLE@H_502_73@ but not deferred (i.e.,INITIALLY IMMEDIATE@H_502_73@). Be aware that 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 unique or primary key constraint in the referenced table.

postgresql – 约束定义DEFERRABLE INITIALLY IMMEDIATE仍然是DEFERRED？

猜你在找的Postgre SQL相关文章