[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] Re: [Xen-devel] [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation
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
|
![]() |
Lists.xenproject.org is hosted with RackSpace, monitoring our |