Re: BUG #12330: ACID is broken for unique constraints

I believe, the objections expressed in this thread miss a very important
point of all this: the isolation property (the "I" in ACID) is violated.

Here’s a quote from the Wikipedia article on ACID
<http://en.wikipedia.org/wiki/ACID>:

The isolation property ensures that the concurrent execution of
transactions results in a system state that would be obtained if
transactions were executed serially, i.e., one after the other.

The original example proves that it's violated. Such behaviour can neither
be expected by a user, nor is even mentioned anywhere. Instead in the first
paragraph of the “About” section of the Postgres site
<http://www.postgresql.org/about/> it states:

It is fully ACID compliant

Which is basically just a lie, until issues like this one get dealt with.
​

2014-12-29 18:31 GMT+03:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

> On Mon, Dec 29, 2014 at 9:09 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> > Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> >> In other words, the current behavior is:
> >> txn A,B begin
> >> txn A inserts
> >> txn B inserts over A, locks, waits
> >> txn A commits. B aborts with duplicate key error
> >
> > What I'm proposing is that for serializable transactions B would
> > get a serialization failure; otherwise B would get a duplicate key
> > error. If the retry of B looks at something in the database to
> > determine what it's primary key should be it will get a new value
> > on the retry, since it will be starting after the commit of A. If
> > it is using a literal key, not based on something changed by A, it
> > will get a duplicate key error on the retry, since it will be
> > starting after the commit of A.
> >
> > It will either succeed on retry or get an error for a different
> > reason.
>
> In that case: we don't agree. How come duplicate key errors would be
> reported as serialization failures but not RI errors (for example,
> inserting a record pointing to another record which a concurrent
> transaction deleted)?
>
> IMO, serialization errors are an implementation artifact and should
> not mask well defined errors in SQL under any circumstances (or if
> they must, those cases should absolutely be as narrow as possible).
>
> merlin
>