Re: BUG #6019: invalid cached plan on inherited table

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
*/

"" <etdirloth(at)gmail(dot)com> writes:
> 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.

Don't hold your breath waiting for a fix for that :-(. There isn't any
support for detecting plan-invalidation events for SQL-language
functions, and in most situations it would be overkill because the plans
are only cached for the lifespan of one calling query anyway. The only
case where the plan could survive longer is this specific example of a
SQL function called from a "simple expression" in a plpgsql function,
and even then it only survives for one complete transaction.

I've thought for some time that SQL functions should be reimplemented
to cache information more the way plpgsql functions do, ie, with a
session-lifespan data structure for each function. If we did that
and made the individual query plans be stored in plancache, then
invalidation of this type would occur automatically --- and, not
insignificantly, the plans would survive long enough to pay back the
added overhead of tracking invalidation causes for them.

A somewhat related point is that people have occasionally complained
because all the queries in a SQL function are run through parse analysis
at once, causing things like

CREATE TABLE foo ... ;
INSERT INTO foo ... ;

to not work because the second command is analyzed before the first is
executed. If we were reimplementing SQL functions then it'd make sense
to try to do something about that at the same time.

None of this is on the TODO list, but I'll go fix that momentarily.

regards, tom lane

No big deal to me, I just couldn't verify that it may occur only within
single transaction - not even in documentation.

thanks for the answer
.et.

On Wed, May 11, 2011 at 6:17 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "" <etdirloth(at)gmail(dot)com> writes:
> > 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.
>
> Don't hold your breath waiting for a fix for that :-(. There isn't any
> support for detecting plan-invalidation events for SQL-language
> functions, and in most situations it would be overkill because the plans
> are only cached for the lifespan of one calling query anyway. The only
> case where the plan could survive longer is this specific example of a
> SQL function called from a "simple expression" in a plpgsql function,
> and even then it only survives for one complete transaction.
>
> I've thought for some time that SQL functions should be reimplemented
> to cache information more the way plpgsql functions do, ie, with a
> session-lifespan data structure for each function. If we did that
> and made the individual query plans be stored in plancache, then
> invalidation of this type would occur automatically --- and, not
> insignificantly, the plans would survive long enough to pay back the
> added overhead of tracking invalidation causes for them.
>
> A somewhat related point is that people have occasionally complained
> because all the queries in a SQL function are run through parse analysis
> at once, causing things like
>
> CREATE TABLE foo ... ;
> INSERT INTO foo ... ;
>
> to not work because the second command is analyzed before the first is
> executed. If we were reimplementing SQL functions then it'd make sense
> to try to do something about that at the same time.
>
> None of this is on the TODO list, but I'll go fix that momentarily.
>
> regards, tom lane
>