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

Le 16 avr. 2015 10:17 PM, <dportas@acm.org> a =C3=A9crit :
>
> The following bug has been logged on the website:
>
> Bug reference:      13073
> Logged by:          David Portas
> Email address:      dportas@acm.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 =3D x +1;
>
> Result:
>
> ERROR:  duplicate key value violates unique constraint "tbl1_pkey"
> DETAIL:  Key (x)=3D(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 =3D 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.