Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraintviolation [and 2 more messages] [and 1 more messages] - Mailing list pgsql-hackers

Robert Haas writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more
messages]"):
> On Wed, Dec 14, 2016 at 9:26 AM, Kevin Grittner <kgrittn@gmail.com> wrote:
> > considered.  Essentially, the position Ian has been taking is that
> > PostgreSQL should provide  the guarantee of (2) above.  As far as I
> > can see, that would require using S2PL -- something the community
> > ripped out of PostgreSQL because of its horrible performance and
> > has refused to consider restoring (for good reason, IMO).
> 
> I'm not sure Ian is intentionally taking that position.  Not all of us
> are as familiar with the ramifications of every serializability
> behavior we may want as you are.

Indeed.  I think it's fair to say that I'm totally unfamiliar with the
ramifications.  You might also fairly characterise me as naive; I had
certainly made some assumptions which it seems are known (around here)
to be both false and difficult to make true.

Robert Haas writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more
messages]"):
>  For example, imagine a transaction that queries pg_stat_activity or
> pg_locks and then makes decisions based on the contents thereof.

I agree that such behviour is unreasonable and should be excluded from
consistency guarantees!  I don't think (even very naive) application
programmers would disagree.  From my point of those tables are `part
of the innards', and expecting transactional behaviour from them is
clearly too optimistic.  (I guess that should be made clear somewhere
near where these kind of system tables are mentioned in the docs.)


Let me try to summarise my understanding of what the developers think
they can and intend to promise, about SERIALIZABLE transactions:
There is a consistent serialisation of all transactions whichsuccessfully commit; or which do not attempt to make any
changes.
A "consistent serialisation" means that there is an order in whichthe same transactions might have been executed giving
exactlytheanswers.  This includes, if applicable, the same errors.  (Thedatabase is free to generate synchronisation
failureerrors 40P01 and40001 whenever it chooses.)
 
A transaction which attempts to make any changes, and which does notcommit (whether because the application never asks
forCOMMIT, orbecause of reported synchronisation failure) might see internallyinconsistent data, or an
internally-consistentview which is notcompatible with any serialisation of other transactions.  Anapplication which
needsa coherent view should not rely on any of theinformation from such a transaction.
 
"Transactions which do not attempt to make any changes" excludes anytransactions whose subtransactions try to make
changes.Serialisationfailures in subtransactions might cause the parenttransaction to experience a serialisation
failuretoo.  "Try to makechanges" includes even DML statements which are bound to fail.
 

Is that an accurate statement of the current thinking ?

Ian.