Re: BUG #15060: Row in table not found when using pg function in an expression

>>>>> "Marko" == Marko Tiikkaja <marko@joh.to> writes:

 >>> It looks like what's going on here is that SPI does GetCachedPlan -
 >>> which is where planning will happen - _before_ establishing the new
 >>> snapshot in the non-read-only case (read_only is false here because
 >>> the calling function, test_bug(), is volatile).

 >> Yeah, I came to the same conclusion. I think it's basically
 >> accidental that the given test case works before 9.2: the reason
 >> seems to be that in 9.1, the plancache doesn't pass through the
 >> parameter list containing the value of "my_text", so that the
 >> planner is unable to speculatively execute get_bug_id(). The order
 >> of operations in _SPI_execute_plan is just as wrong though.

 Marko> I'm not sure I understand. When's the snapshot used for planning
 Marko> actually taken here?

GetCachedPlan will use either whatever snapshot is already set, if there
is one, or it will set one of its own (actually at least two: separate
snapshots for revalidate + parse analysis and for planning).

In the case of a volatile plpgsql function, the snapshot in which the
function was called will, I believe, still be the active snapshot at the
relevant point, so calls made in planning won't see the function's own
changes.

The recent introduction of procedures exposes this interesting little
variation in behavior (pg11 only):

create table bug (id integer, d text);
create or replace function getbug(text) returns integer
  language plpgsql stable
  as $$
    declare
      b_id integer;
    begin
      select into b_id id from bug where d = $1;
      if not found then
        raise info 'bug % not found',$1;
      else
        raise info 'bug % id %',$1,b_id;
      end if;
      return b_id;
    end;
$$;

truncate table bug;
do $$
  begin
    insert into bug values (1,'foo');
    perform * from bug where id = getbug('foo');
  end;
$$;
INFO:  bug foo not found
INFO:  bug foo id 1

truncate table bug;
do $$
  begin
    commit;
    insert into bug values (1,'foo');
    perform * from bug where id = getbug('foo');
  end;
$$;
INFO:  bug foo id 1
INFO:  bug foo id 1

I assume that what's going on here is that the commit, which ends the
transaction in which the DO was invoked and begins a new one, doesn't
set a new active snapshot in the new transaction, and so planning of the
perform in the second case is taking new snapshots inside GetCachedPlan.

-- 
Andrew (irc:RhodiumToad)