Not understanding this behavior of a subselect + volatile function

Поиск
Список
Период
Сортировка
От Brian Palmer
Тема Not understanding this behavior of a subselect + volatile function
Дата
Msg-id 7C43E7F5-5569-490D-B094-63EEF1B84972@codekitchen.net
обсуждение исходный текст
Ответы Re: Not understanding this behavior of a subselect + volatile function  (Chris Angelico <rosuav@gmail.com>)
Re: Not understanding this behavior of a subselect + volatile function  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
There is behavior in the following code that has me confused, and I'd like to understand it, as it goes against how I
thoughtthat 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
theouter select locked its view of the table in place before the inner select ran. What seems even stranger to me is
thatif a row is inserted at just the right moment, the inner function can select it and update it, then return its
primarykey, 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
seethe same data as the outer select. That's definitely not happening here, and I'm wondering why -- is it a property
ofvolatile 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




В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Davis
Дата:
Сообщение: Re: Forcefully adding a CHECK constrained
Следующее
От: Chris Angelico
Дата:
Сообщение: Re: Not understanding this behavior of a subselect + volatile function