Re: [Xen-devel] [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

[Kevin Grittner <kgrittn@xxxxxxxxx>]

On Fri, Dec 9, 2016 at 12:26 PM, Ian Jackson <ian.jackson@xxxxxxxxxxxxx> wrote:

> In summary: PostgreSQL only provides transaction serialisability for
> successful transactions.  Even with SERIALIZABLE, transactions may
> fail due to spurious and "impossible" constraint violations.
>
> As a result, I need to make osstest retry transactions not only on
> explicitly reported serialisation failures and deadlocks, but also on
> integrity violations.
>
> It is not clear to me from the thread
>   WIP: Detecting SSI conflicts before reporting constraint violations
> which was on pgsql-hackers earlier this year, whether it is only
> unique constraint violations which may spuriously occur.
>
> Can anyone from the PostgreSQL hacker community advise ?  The existing
> documentation patch just talks about "successful" transactions, and
> uses unique constraints as an example.  In principle this leaves open
> the possibility that the transaction might fail with bizarre and
> unpredictable error codes, although I hope this isn't possible.

It is, especially prior to 9.6.

> It would be good to narrow the scope of the retries in my system as
> much as possible.

If I recall correctly, the constraints for which there can be
errors appearing due to concurrent transactions are primary key,
unique, and foreign key constraints.  I don't remember seeing it
happen, but it would not surprise me if an exclusion constraint can
also cause an error due to a concurrent transaction's interaction
with the transaction receiving the error.

The good news is that in 9.6 we were able to change many of these
to serialization failures if there was an explicit check for a
violation before creating the conflict.  For example, if there is a
primary key (or unique constraint or unique index) and within a
transaction you SELECT to test for the presence of a particular
value (or set of values, in the case of a multi-column constraint
or index) and it is not found, an attempt to insert that runs into
an insert from a concurrent transaction will now generate a
serialization failure.

> I'm hoping to get an authoritative answer here but it seems that this
> is a common problem to which there is still not yet a definitive
> solution.  I would like there to be a definitive solution.

I'm afraid that even though the frequency of getting some error
other than a serialization failure due to the actions of a
concurrent transaction, even if the failing transaction could not
have failed in the absence of the concurrent transaction and is
likely to succeed on retry, has gone down in 9.6, it has not been
eliminated.  You are forced to choose between performing some
limited number of retries for constraint violations or presenting
users with error messages which "should not be possible".
Obviously, the former means you will sometimes retry when there is
no hope of the retry succeeding and give up on retries when a retry
is likely to work, and the latter can confuse users and waste their
time.  :-(

> If I get a clear answer I'll submit a further docs patch to pgsql :-).

Of course, code patches to improve the situation are also welcome!  :-)

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

_______________________________________________
Xen-devel mailing list
Xen-devel@xxxxxxxxxxxxx
https://lists.xen.org/xen-devel