Thread: Plan cache and name space behavior in 9.2

I came across a new behavior in 9.2 with nested plpgsql functions and
temporary table.

create or replace function chck(tname text) returns void as $$
declare cnt int;
begin select count(*) from pg_attribute where attrelid = tname::regclass::oid   into cnt;
end;
$$ language plpgsql;

create or replace function train(tname text) returns text as $$
declare
begin perform chck(tname); return 'OK';
end;
$$ language plpgsql;

create or replace function test(tname text) returns text as $$
declare result text;
begin result = train(tname); return result;
end;
$$ language plpgsql;

drop table if exists tbl;
create table tbl(a int);
select test('tbl'); -- call 1
drop table if exists tbl;

create temp table tbl(a int);
select test('tbl'); -- call 2
drop table tbl;

I expected success in tname::regclass in the function chck(), but it
actually fails for your first run in the session.  The second run of
this script will succeed.  I assume it's the cause is the new plan
cache behavior.  On the first run of the script, the temporary
namespace was not created when the chck() is called at call 1 , and it
saves namespace, then it searches the old saved namespace for 'tbl' at
call 2, fails to find 'tbl'.  Removing the call 1 call it runs
successfully.  The second  run of this script in the same session
succeeds because the temporary namespace has been created in the first
run.  I confirmed it runs fine in 9.1  Is this a bug in 9.2 or an
expected behavior in the new plan cache?

Thanks,
-- 
Hitoshi Harada

Hitoshi Harada <umi.tanuki@gmail.com> writes:
> I expected success in tname::regclass in the function chck(), but it
> actually fails for your first run in the session.

Really?  Not for me.

In the example as given, I see success for "call 1" and then an error at
"call 2", which is occurring because we're trying to replan the query
with the original search_path, which doesn't include the temp schema
since it didn't exist yet.  (The planner is trying to estimate the value
of the expression 'tname::regclass::oid', which is why this happens
with the old search_path setting --- it wouldn't fail at runtime.)

A replan would have failed in previous versions too, but that's much
less likely in previous versions since you'd need to see a relcache
invalidation on one of the referenced tables to make one happen.

There's been periodic discussion of how the plan cache ought to interact
with search_path anyway --- maybe it shouldn't try to restore the
previous search_path setting but rather view current search_path as part
of the cache lookup key.  I'd be hesitant to back-patch such a behavior
change, though.
        regards, tom lane

On Fri, Sep 14, 2012 at 12:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hitoshi Harada <umi.tanuki@gmail.com> writes:
>> I expected success in tname::regclass in the function chck(), but it
>> actually fails for your first run in the session.
>
> Really?  Not for me.
>
> In the example as given, I see success for "call 1" and then an error at
> "call 2", which is occurring because we're trying to replan the query
> with the original search_path, which doesn't include the temp schema
> since it didn't exist yet.

I'm saying the same thing actually.  I see success for call 1 and
error at call 2, which was not observed in 9.1 and older.

> A replan would have failed in previous versions too, but that's much
> less likely in previous versions since you'd need to see a relcache
> invalidation on one of the referenced tables to make one happen.

I don't think so.  I tried it in 9.1 and succeeded.  I found this
during the test of an external module that has been running back to
8.4.  So I wonder if we could say this is a behavior change or a bug.

And I agree the replan failure would be sane if the function was
marked as immutable or stable, but all the functions I defined in the
example is volatile.  I'm not sure how others feel, but at least it's
surprising to me that the call 2 keeps the state of call 1 though it
is a volatile function.  I have not been tracking the periodic
discussion of plan cache vs search_path, but what is the beneficial
use case of the new behavior?

Thanks,
-- 
Hitoshi Harada