Re: serialization failure why?

On 29 June 2015 at 21:13, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
> > On 17 June 2015 at 13:52, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> >> Filipe Pina <filipe(dot)pina(at)impactzero(dot)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