I just noticed that there is an inconsistency between the way that
PREPARE executes multiple queries (since PREPARE itself accepts
only one SQL statement, any such multiple queries must have been
generated by rule expansion) and the way that it is done in
pg_exec_query_string().  The latter will do a SetQuerySnapshot()
between actions of the rule, the former only does
CommandCounterIncrement().

If you're in a serializable transaction then there's no difference
in behavior.  But in READ COMMITTED mode this means that later actions
in the rule may be able to see the effects of other transactions that
committed while earlier actions were running.

ISTM we had better make the behavior consistent between PREPARE and
interactive execution.  But which one do we want?  I could see an
argument that it'd be best for all the actions of a rule to see a
consistent snapshot of the state of other transactions; and not doing
the extra SetQuerySnapshot() calls would save some cycles.
But perhaps we had better stick to our historical behavior.
pg_exec_query_string has done a SetQuerySnapshot per query for a long
time, and I can't recall anyone ever complaining about it.

Come to think of it, SPI also does CommandCounterIncrement but not
SetQuerySnapshot between querytrees; therefore this inconsistency
also affects SPI-based operations such as PL functions.

Comments?

			regards, tom lane


Thu, 01 May 2003 11:55:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Should we SetQuerySnapshot() between actions of a rule?
On Thu, May 01, 2003 at 11:55:45AM -0400, Tom Lane wrote:
> interactive execution.  But which one do we want?  I could see an
> argument that it'd be best for all the actions of a rule to see a
> consistent snapshot of the state of other transactions; and not doing
> the extra SetQuerySnapshot() calls would save some cycles.
> But perhaps we had better stick to our historical behavior.
> pg_exec_query_string has done a SetQuerySnapshot per query for a long
> time, and I can't recall anyone ever complaining about it.

Can't you get the consistent snapshot by running SERIALIZABLE anyway?

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Thu, 1 May 2003 12:32:56 -0400
Andrew Sullivan <andrew@libertyrms.info>
Re: Should we SetQuerySnapshot() between actions of a rule?
Andrew Sullivan <andrew@libertyrms.info> writes:
> On Thu, May 01, 2003 at 11:55:45AM -0400, Tom Lane wrote:
>> interactive execution.  But which one do we want?  I could see an
>> argument that it'd be best for all the actions of a rule to see a
>> consistent snapshot of the state of other transactions; and not doing
>> the extra SetQuerySnapshot() calls would save some cycles.

> Can't you get the consistent snapshot by running SERIALIZABLE anyway?

Yeah, but that may do more than you want (like force a rollback on
update conflicts...)

			regards, tom lane


Thu, 01 May 2003 13:35:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Should we SetQuerySnapshot() between actions of a rule? 
On Thu, May 01, 2003 at 01:35:20PM -0400, Tom Lane wrote:
> > Can't you get the consistent snapshot by running SERIALIZABLE anyway?
> 
> Yeah, but that may do more than you want (like force a rollback on
> update conflicts...)

True enough, but that's the same problematic choice you have in an
interactive session (unless I misunderstood).

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Thu, 1 May 2003 13:59:00 -0400
Andrew Sullivan <andrew@libertyrms.info>
Re: Should we SetQuerySnapshot() between actions of a rule?
> -----Original Message-----
> From: Tom Lane
> 
> I just noticed that there is an inconsistency between the way that
> PREPARE executes multiple queries (since PREPARE itself accepts
> only one SQL statement, any such multiple queries must have been
> generated by rule expansion) and the way that it is done in
> pg_exec_query_string().  The latter will do a SetQuerySnapshot()
> between actions of the rule, the former only does
> CommandCounterIncrement().
> 
> If you're in a serializable transaction then there's no difference
> in behavior.  But in READ COMMITTED mode this means that later actions
> in the rule may be able to see the effects of other transactions that
> committed while earlier actions were running.
> 
> ISTM we had better make the behavior consistent between PREPARE and
> interactive execution.  But which one do we want?  I could see an
> argument that it'd be best for all the actions of a rule to see a
> consistent snapshot of the state of other transactions;

Is it appropriate to use the word *snapshot* for queries other than
read-only ones ? 

regards,
Hiroshi Inoue


Sat, 3 May 2003 23:17:18 +0900
"Hiroshi Inoue" <inoue@tpf.co.jp>
Re: Should we SetQuerySnapshot() between actions of a rule?