Re: BUG #13073: Uniqueness constraint incorrectly reports constraint violations

Le 16 avr. 2015 10:17 PM, <dportas(at)acm(dot)org> a écrit :
>
> The following bug has been logged on the website:
>
> Bug reference: 13073
> Logged by: David Portas
> Email address: dportas(at)acm(dot)org
> PostgreSQL version: 9.1.13
> Operating system: Debian Linux
> Description:
>
> Repro script:
> CREATE TABLE tbl1 (x INT NOT NULL PRIMARY KEY);
> INSERT INTO tbl1 VALUES (1),(2);
> UPDATE tbl1 SET x = x +1;
>
> Result:
>
> ERROR: duplicate key value violates unique constraint "tbl1_pkey"
> DETAIL: Key (x)=(2) already exists.
>
> Expected result: UPDATE should succeed because the constraint is not
> violated. The constraint should be evaluated against the complete
resulting
> table as per documentation: "unique with respect to all the rows in the
> table"[1].
>
> The expected result can be seen if the insertion order of the INSERTs is
> reversed:
> CREATE TABLE tbl1 (x INT NOT NULL PRIMARY KEY);
> INSERT INTO tbl1 VALUES (2),(1);
> UPDATE tbl1 SET x = x +1;
>
> Result: UPDATE succeeds. This is expected but is inconsistent with the
> previous result even though the two UPDATEs are logically equivalent.
>
> The same effect is seen if UNIQUE is specified instead of PRIMARY KEY.
>
> [1]http://www.postgresql.org/docs/9.1/static/ddl-constraints.html
>

This is expected. You need deferrable constraints to make that work.