BUG #13073: Uniqueness constraint incorrectly reports constraint violations

Le 16 avr. 2015 10:17 PM, <dportas@acm.org> a écrit :

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

On Thu, Apr 16, 2015 at 1:03 PM, <dportas@acm.org> wrote:

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

On 16 April 2015 at 21:39, David G. Johnston <david.g.johnston@gmail.com> wrote:

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.

Thanks. It's interesting that the default behaviour is to compromise
ACID compliance with a result that is, logically speaking,
non-deterministic. This appears to be inconsistent with the ISO SQL
standard [1]I only have the SQL 1999 and 2003 documentation to hand. In both cases Section 10 of the Foundation document specifies that immediate constraint checking (whether deferrable or not) occurs "on completion of any SQL-statement". and with other SQL DBMSs.

David

[1]: I only have the SQL 1999 and 2003 documentation to hand. In both cases Section 10 of the Foundation document specifies that immediate constraint checking (whether deferrable or not) occurs "on completion of any SQL-statement".
cases Section 10 of the Foundation document specifies that immediate
constraint checking (whether deferrable or not) occurs "on completion
of any SQL-statement".

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 04/16/15 23:16, David Portas wrote:

On 16 April 2015 at 21:39, David G. Johnston <david.g.johnston@gmail.com> wrote:

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.

Thanks. It's interesting that the default behaviour is to compromise
ACID compliance with a result that is, logically speaking,
non-deterministic. This appears to be inconsistent with the ISO SQL
standard [1] and with other SQL DBMSs.

I don't see how this compromises ACID compliance. If anything, it makes
the consistency checks more strict (not allowing violated constraint
mid-transaction).

As for the SQL standard compliance, the documentation [1]http://www.postgresql.org/docs/9.1/static/sql-createtable.html says this:

When a UNIQUE or PRIMARY KEY 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 but not deferred (i.e., INITIALLY
IMMEDIATE). Be aware that this can be significantly slower than
immediate uniqueness checking.

In other words, this is a known difference, this default behavior was
chosen because

(a) it has performance benefits
(b) is more appropriate for most cases
(c) does *not* compromise any consistency guarantees (but may cause
false positives), and
(d) there's a way to make it standard-compliant behavior by setting
the constraint DEFERRABLE.

[1]: http://www.postgresql.org/docs/9.1/static/sql-createtable.html

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Thu, Apr 16, 2015 at 2:16 PM, David Portas <dportas@acm.org> wrote:

On 16 April 2015 at 21:39, David G. Johnston <david.g.johnston@gmail.com>
wrote:

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.

Thanks. It's interesting that the default behaviour is to compromise
ACID compliance with a result that is, logically speaking,
non-deterministic. This appears to be inconsistent with the ISO SQL
standard [1] and with other SQL DBMSs.

David

[1] I only have the SQL 1999 and 2003 documentation to hand. In both
cases Section 10 of the Foundation document specifies that immediate
constraint checking (whether deferrable or not) occurs "on completion
of any SQL-statement".

​This needs to be corrected in the documentation:

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

beginning of page: IMMEDIATE constraints are checked at the end of each
statement.
[...]
end of page: ​Also,PostgreSQL checks non-deferrable uniqueness constraints
immediately, not at end of statement as the standard would suggest.

As is the case with transaction isolation a table summarizing the possible
combinations and resultant check timing would probably be quite useful. I
am unsure whether deferrable, but not deferred, immediate checks are done
are statement end or for each record - the qualification at the end only
speaks to "non-deferrable" ones. Regardless, the cavet seems important
enough to make in the main body and not leave solely relegated to a
compatibility note.

You are correct as to the standard non-conformance. My understanding is
that the performance gains outweighed the conformity loss - and/or that
changing it hasn't met the level of need necessary to introduce a
regression in existing code.

However, it does not compromise ACID compliance. It is simply not as
lenient as it could be. If the statement executes to completion it will
have all ACID properties otherwise it will fail and the previously ACID
compliant result will remain.​

David J.