The following bug has been logged online: Bug reference: 6019 Logged by: Email address: etdirl...@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 */ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
