> 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.
Oh I see. So the "problem" is not specific to PL/pgSQL, but exists in
all our procedual languages.
> 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.
Ok, maybe another workaround might be adding a checking for cmax in
the subselect:
SELECT INTO myid i FROM t1 WHERE i = (SELECT i FROM t1 WHERE i = 1);
to make sure that cmax > 0?
--
Tatsuo Ishii