Re: [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]

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(at)gmail(dot)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 which
successfully commit; or which do not attempt to make any changes.

A "consistent serialisation" means that there is an order in which
the same transactions might have been executed giving exactly the
answers. This includes, if applicable, the same errors. (The
database is free to generate synchronisation failure errors 40P01 and
40001 whenever it chooses.)

A transaction which attempts to make any changes, and which does not
commit (whether because the application never asks for COMMIT, or
because of reported synchronisation failure) might see internally
inconsistent data, or an internally-consistent view which is not
compatible with any serialisation of other transactions. An
application which needs a coherent view should not rely on any of the
information from such a transaction.

"Transactions which do not attempt to make any changes" excludes any
transactions whose subtransactions try to make changes.
Serialisation failures in subtransactions might cause the parent
transaction to experience a serialisation failure too. "Try to make
changes" includes even DML statements which are bound to fail.

Is that an accurate statement of the current thinking ?

Ian.