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

On Thu, Apr 16, 2015 at 1:03 PM, <dportas(at)acm(dot)org> wrote:

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

​And at the moment you update 1 to become 2 you have two rows in the table
having x=2; even if that particular picture of the table is one that no
other statements could ever see.

​You are, not unexpectedly, assuming that constraints are evaluated only
after all rows has been processed - i.e., post-statement completion. While
this is possible (see below) it is not the default behavior. By default,
as each row is updated all of the relevant constraints are checked to see
if any have been violated.​

> 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

​Likely the documentation could use improvement here...everything necessary
to explain this behavior is documented but seemingly inadequately
cross-referenced.

http://www.postgresql.org/docs/devel/static/sql-set-constraints.html

​As Guillaume Lelarge notes you have to cause the constraint to be
evaluated in deferred mode Alternatively you can, I think, use a from
clause sub-select source that is ordered by (x DESC) to ensure that at no
time does the snapshot contain duplicate values for "x". Your example
proves this works in small circumstances but I'm not positive if the
executor guarantees to update the rows in the same order as the
sub-select. I am fairly certain that it does.

It is considerably more performant to evaluate constraints immediately -
and need to execute "UPDATE tbl SET x = x + 1" is infrequent...and one of
the few circumstances where this (order of row evaluation) problem arises.

David J.