Re: Not understanding this behavior of a subselect + volatile function

On Sun, May 27, 2012 at 8:17 AM, Brian Palmer <brian(at)codekitchen(dot)net> wrote:
> There is behavior in the following code that has me confused, and I'd like to understand it, as it goes against how I thought that MVCC worked in psql:
> ...
>      select a from t1 into ret where b < 1 for update;
>      update t1 set b = b + 1 where a = ret;
> ...
> The final line, the select, will return the row as it was before the function ran, (1,0) instead of (1,1).  It's as if the outer select locked its view of the table in place before the inner select ran. What seems even stranger to me is that if a row is inserted at just the right moment, the inner function can select it and update it, then return its primary key, but the outer select won't even see that row, and so it will return 0 rows even though the row got updated.

As Frederic said, "Most curious! Most absurdly whimsical!"

The function is actually immaterial to this; the same thing occurs
with this single statement:

with t1upd as (update t1 set b = b + 1 where b < 1 returning a) select
* from t1 join t1upd using (a);

Poking around with the latter form of the statement and Google showed up this:

http://stackoverflow.com/questions/7191902/cannot-select-from-update-returning-clause-in-postgres

I don't fully understand the exact interactions between transactions,
snapshots, and statements, but according to the accepted answer on
stackoverflow, the entire statement "sees" the database as at the
beginning of the statement.

ChrisA