Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> If your theory is like that, I could see same effect without using
> PL/pgSQL. But I see following in a session using psql (original row's
> ctid = (0,2))
> [T1] begin;
> [T2] begin;
> [T1] update t1 set i = 1 where i = 1;
> [T2] update t1 set i = 1 where i = 1; <-- waiting for T1 committed/aborted
> [T1] end;
> [T2] select ctid, i from t1;
> test=# select ctid,i from t1;
> ctid | i
> -------+---
> (0,4) | 1
> (1 row)
> So I only see one row from the last select in T2?
I believe the reason for this is that in Read Committed mode,
each separate query from the client computes a new snapshot (see
SetQuerySnapshot calls in postgres.c). So, when your
"select ctid, i from t1" query executes, it computes a snapshot
that says T1 is committed, and then it doesn't see the row left
over from T1. On the other hand, your plpgsql function operates
inside a single client query and so it's using just one QuerySnaphot.
One way to make the results equivalent is to compute a new QuerySnapshot
for each SPI query. Quite aside from the cost of doing so, I do not
think it makes sense, considering that the previous QuerySnapshot must
be restored when we return from the function. Do we really want
functions to see transaction status different from what's seen outside
the function call? I doubt it.
The other way to make the results the same is to omit the
SetQuerySnapshot calls for successive client-issued queries in one
transaction. This could perhaps be defended on logical grounds,
but considering your complaint I'm not sure it would make people
happier.
regards, tom lane