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

On Thu, Apr 16, 2015 at 2:16 PM, David Portas <dportas(at)acm(dot)org> 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.
>
> 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.