Re: BUG #12330: ACID is broken for unique constraints - Mailing list pgsql-hackers

On Mon, Dec 29, 2014 at 9:44 AM, Greg Stark <stark@mit.edu> wrote:
> On Mon, Dec 29, 2014 at 3:31 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> 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)?
>
> The key question is not the type of error but whether the transaction
> saw another state previously.

[combining replies -- nikita, better not to top-post (FYI)]

How is that relevant?  Serialization errors only exist as a concession
to concurrency and performance.  Again, they should be returned as
sparsely as possible because they provide absolutely (as Tom pointed
out) zero detail to the application.   The precise definition of the
error is up to us, but I'd rather keep it to it's current rather
specific semantics.

On Mon, Dec 29, 2014 at 9:48 AM, Nikita Volkov <nikita.y.volkov@mail.ru> wrote:
> 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:
>
> 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 it states:
>
> It is fully ACID compliant
>
> Which is basically just a lie, until issues like this one get dealt with.

That's simply untrue: inconvenience != acid violation

Transaction levels provide certain guarantees regarding the state of
the data in the presence of concurrent overlapping operations.   They
do not define the mechanism of failure or how/when those failures
should occur.  To prove your statement, you need to demonstrate how a
transaction left the database in a bad state given concurrent activity
without counting failures.  Postgres can, and does, for example,
return concurrency type errors more aggressively than it needs to for
the 'repeatable read', level.  Point being, this is completely ok as
database implementation is free to understand that, just as it's free
to define precisely how and when it fails given concurrency as long as
those guarantees are provided.

merlin