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