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

Merlin Moncure <mmoncure@gmail.com> wrote:

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

That is false.  They provide an *extremely* valuable piece of
information which is not currently available when you get a
duplicate key error -- whether the error occurred because of a race
condition and will not fail for the same cause if retried.

> The precise definition of the error is up to us, but I'd rather
> keep it to it's current rather specific semantics.

The semantics are so imprecise that Tom argued that we should
document that transactions should be retried from the start when
you get the duplicate key error, since it *might* have been caused
by a race condition.

I'm curious how heavily you use serializable transactions, because
I have trouble believing that those who rely on them as their 
primary (or only) strategy for dealing with race conditions under 
high concurrency would take that position.

As for the fact that RI violations also don't return a
serialization failure when caused by a race with concurrent
transactions, I view that as another weakness in PostgreSQL.  I
don't think there is a problem curing one without curing the other
at the same time.  I have known of people writing their own
triggers to enforce RI rather than defining FKs precisely so that
they can get a serialization failure return code and do automatic
retry if it is caused by a race condition.  That's less practical
to compensate for when it comes to unique indexes or constraints.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company