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:
create table t1 (a integer primary key, b integer default 0);
insert into t1 (a) values (1);
create function f1() returns int as $$
declare
ret int;
begin
select a from t1 into ret where b < 1 for update;
update t1 set b = b + 1 where a = ret;
return ret;
end;
$$ language plpgsql;
select * from t1 where a = (select f1());
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.
I was under the impression that the transaction will have a consistent view of
the table, and so the subselect should see the same data as the outer select.
That's definitely not happening here, and I'm wondering why -- is it a property
of volatile functions? Do they get their own, separate view of the data, even
inside the same transaction?
Thanks for any insight on this puzzler,
-- Brian Palmer
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
