The following bug has been logged online:
Bug reference: 6019
Logged by:
Email address: etdirloth@gmail.com
PostgreSQL version: 9.0.4
Operating system: SLES 11 SP1 and WinXP SP3
Description: invalid cached plan on inherited table
Details:
Cached execution plan of SQL stored procedure (which select from inherited
table) executed from within PLPGSQL function is used even when inheritance
descendant is already removed. It behaves like a bug from older versions of
pgsql (v<8.3) when temporary tables created and removed from within
functions were still referenced by cached plans.
-- same behavior on linux and windows:
-- uname -a
-- SMP 2010-05-20 11:14:20 +0200 x86_64 x86_64 x86_64 GNU/Linux
-- select version();
-- PostgreSQL 9.0.3 on x86_64-suse-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit
-- Microsoft Windows XP [Version 5.1.2600] SP3
-- select version();
-- PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 32-bit
-- PostgreSQL 9.0.3, compiled by Visual C++ build 1500, 32-bit
-- to reproduce, execute following (in single transaction)
BEGIN;
-- cleanup
DROP TABLE IF EXISTS tst CASCADE;
-- create parent table
CREATE TABLE tst (id serial NOT NULL PRIMARY KEY);
-- create some partition
CREATE TABLE tst_1 (CONSTRAINT tst_1_id_check CHECK (id >= 0 AND id < 3))
INHERITS (tst);
CREATE OR REPLACE FUNCTION tst_gt_inner(arg tst.id%TYPE) RETURNS BOOLEAN AS
$$
-- select something from parent table
SELECT EXISTS (SELECT 1 FROM tst WHERE id > $1)
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION tst_gt_outer(arg tst.id%TYPE) RETURNS VOID AS $$
DECLARE
b BOOLEAN;
v VARCHAR;
BEGIN
-- this will output same OID as in the ERROR message (for the one below
it would be "tst_1,r,140828")
SELECT INTO v relname || ',' || relkind || ',' || oid FROM pg_class WHERE
relname = 'tst_1';
raise notice '%', v;
-- obtain result of tst_gt_inner from within plpgsql
b := tst_gt_inner(arg);
-- ...
END;
$$ LANGUAGE PLPGSQL VOLATILE;
-- insert some data
INSERT INTO tst_1 VALUES (1);
-- when executing sql function from within plpgsql function, plan for
tst_gt_inner will be cached
SELECT * FROM tst_gt_outer(3);
-- then drop partition
DROP TABLE tst_1;
-- calling it directly is still OK
SELECT * FROM tst_gt_inner(3);
-- try to perform tst_gt_outer second time will end in XX000
SELECT * FROM tst_gt_outer(3);
COMMIT;
-- result:
/*
ERROR: could not open relation with OID 140828
SQL state: XX000
Context: SQL function "tst_gt_inner" statement 1
PL/pgSQL function "tst_gt_outer" line 9 at assignment
*/
