Thread: Should we SetQuerySnapshot() between actions of a rule?
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
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
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
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
> 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(). > 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. Agreed. Actions generated by a rule should definitely all get the same snapshot. Thus I think we only want to CommandCounterIncrement inside one statement. For multiple statement statements, the main issue is usually to gain performance. So I guess we could also only CommandCounterIncrement there. Especially since for (session local) FK PK issues that is sufficient. Also in the light of implicit subtransactions I guess we will want to start only one subtransaction per multi statement. Seems to fit well with above. > 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. Whether rows that get committed during single statement execution should be instantly visible (in read committed) is imho a different consideration. It should definitely be handeled equivalenty between a rule action and a second pass at a table (e.g. for a join). Andreas
> -----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