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

On 04/16/15 23:16, David Portas wrote:
> On 16 April 2015 at 21:39, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)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] 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