Plan cache and name space behavior in 9.2

From: Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Plan cache and name space behavior in 9.2
Date: 2012-09-14 19:32:42
Message-ID: CAP7Qgm=z9hsd9kQdFR=LuC-TwBSXJRZsy+EWE5r_0Zn-c2NdYg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2012-09-14 19:50:37 Re: embedded list v2
Previous Message Peter Geoghegan 2012-09-14 19:30:47 Re: Closing CF 2012-06