Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Is it possible that a rule generates multiple queries from
> a read(select)-only statement ? If so,the queries must
> be executed under the same snapshot in order to guaran
> tee read consistency from user's POV.
> As for non-select queries I'm not sure because read
> consistency doesn't have much meaning for them.
In SERIALIZABLE mode everything is done with the first snapshot obtained
*in the transaction*, which seems correct to me. In READ COMMITTED mode
a new snapshot is taken at every SetQuerySnapshot, which means later
commands in an xact can see data committed later than transaction start.
The issue here seems to be just how often we want to do
SetQuerySnapshot.
One thing that bothers me about the current setup is that
pg_exec_query_string delays calling SetQuerySnapshot until the last
possible moment before executing a query. In particular, parsing and
planning of the first query in a transaction will be done with no
snapshot at all! Is this good, and if so why?
I am inclined to think that we should do SetQuerySnapshot in the outer
loop of pg_exec_query_string, just before calling
pg_analyze_and_rewrite. This would ensure that parse/plan accesses to
the database have a snapshot, and would eliminate the question I raised
yesterday about whether ProcessUtility is missing SetQuerySnapshot
calls.
If we did that, then SetQuerySnapshot would be called once per user-
written command (defining a command as whatever the grammar produces
a single parsetree for, which is probably OK) so long as SPI functions
don't try to use pg_exec_query_string...
Then this'd also be an appropriate place to advance now(), if people
feel that's more appropriate behavior for now() than the existing one.
> I just remembered a report from Forest Wilkinson
> about a month ago [SQL] SQL functions not locking
> properly?
Yes, that was on my to-look-at list too. Not sure if it's related.
> Don't we have to distiguish simple procedure calls
> (select func();) and function calls as a part of a query ?
"select func()" looks like a query to me. I don't see how you are going
to make such a distinction in a useful way. If we had a CALL statement
distinct from function invocation in expressions, then maybe it'd make
sense for that context to act differently.
regards, tom lane
