On 29 June 2015 at 21:13, Kevin Grittner <kgri...@ymail.com> wrote: > Simon Riggs <si...@2ndquadrant.com> wrote: > > On 17 June 2015 at 13:52, Kevin Grittner <kgri...@ymail.com> wrote: > >> Filipe Pina <filipe.p...@impactzero.pt> wrote: > > >>> if drop the foreign key constraint on stuff_ext table there are > >>> no failures at all… > >> > >> It is my recollection that we were excluding the queries used to > >> enforce referential integrity constraints from the conflict > >> tracking, so I am surprised you are seeing this. What is the exact > >> version you are using (as reported by the version() function)? > > > > I don't see any mechanism for excluding anything from > > serializable checks, so I can't see how that would work. > > It is a matter of where calls to PredicateLockXxx and > CheckForSerializableConflictXxx calls were inserted into, for > example, heap and index AM code. At least I think we omitted > placing some at locations which were known to be used for RI > enforcement; but apparently some more generic code is exercised by > the RI trigger execution which can still trigger serialization > failures based on FKs. > > > I can't find any mention of serializability concerns in the RI > > code itself. > > It is mentioned in the README-SSI file. > > > AFAIK it would be strange to exclude FK checks from > > serializability checks, since they represent a valid observation > > of an intermediate state. > > The idea that this is OK is based on the observations in the paper > "Automating the Detection of Snapshot Isolation Anomalies" by > Sudhir Jorwekar, Alan Fekete, Krithi Ramamritham, and S. > Sudarshan[1]. To quote a key sentence from that paper: >
So we are saying we can exclude FK checks from serialization, but we do not, yet. Since the FK checks run with a special snapshot it should be simple to exclude them. > > Mat Views are excluded but I don't understand why that should be > > the case. There is no documented explanation. > > Good point; it should be documented. Basically, since the matview > is a materialized copy of data from other relations from some prior > point in time, the race conditions caught by SSI would be trivial > compared to those likely to exist based on the elapsed time since > the last REFRESH; so it would be kind of silly to try to enforce > the more subtle interactions while ignoring the big, glaring, > obvious one. It would be a bit like treating a laceration of > someone's hand when they were not breathing -- it's not the thing > to worry about. As we enhance matviews to have associated > freshness information and especially once we use them like indexes > to optimize queries this will deserve a close look, as there is > likely to be something meaningful we can do at that time. > We should add that as a code comment. Thanks for complete answers to those questions. -- Simon Riggs http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services