Skip site navigation (1) Skip section navigation (2)

BUG #6019: invalid cached plan on inherited table

From: "" <etdirloth(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6019: invalid cached plan on inherited table
Date: 2011-05-10 12:29:51
Message-ID: 201105101229.p4ACTpnU013375@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      6019
Logged by:          
Email address:      etdirloth(at)gmail(dot)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
*/

Responses

pgsql-bugs by date

Next:From: Martin PittDate: 2011-05-10 12:36:18
Subject: 9.1beta1 "collate" test failure
Previous:From: Fujii MasaoDate: 2011-05-10 12:02:49
Subject: Re: BUG #6018: ctrl +C cause data inconsistent for sync standby

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group