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

On Thu, Dec 15, 2016 at 9:01 AM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:
> I also realized some other properties of read-only transactions
> that might interest you (and that I should probably document).
> Since the only way for a read-only transaction to be the on
> experiencing a serialization failure is if Tout commits before the
> read-only transaction (which is always Tin) acquires its snapshot,
> Tpivot is still running when Tin acquires its snapshot, Tpivot
> commits before a serialization failure involving Tin is detected,
> and *then* Tin reads a data set affected by the writes of Tpivot.
> Since a snapshot is only acquired when a statement is run which
> requires a snapshot, that means that a query run in an implicit
> transaction (i.e., there is no START or BEGIN statement to
> explicitly start it; the SELECT or other data-accessing statement
> automatically starts the transaction so it has a valid context in
> which to run) that does not write data can never return bad results
> nor receive a serialization failure. Nor can those things happen
> on the *first* or *only* non-writing statement in an explicit
> transaction.

I don't understand this argument. Every statement in a read-only,
serializable transaction runs with the same snapshot, so I don't see
how it can make a difference whether we're in the middle of running
the first statement or the tenth. Tpivot might commit in the middle
of executing the first statement of the transaction, which might then
-- later on during the execution of that same statement -- do
something that causes it to acquire a bunch more SIREAD locks. For
example, suppose the query involves calling a function which is
defined like this:

create or replace function getval(t text) returns integer as $$declare
q int; begin execute 'select aid from ' || t || ' limit 1;' into q;
return q; end$$ language plpgsql;

Obviously, every call to this function may grab an SIREAD lock on a new object.

Even without recourse to nested queries, I think we don't know which
index or heap pages will be locked at the start of execution. We
acquire them as we go along. At any point in that we could acquire
one which creates an rw-conflict with Tpivot, couldn't we?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company