Thread: pg_class_aclcheck: relation [oid] not found...

Howdy.  There's a bug in the handling of clean up temp tables.  It
seems as though there's a missing call to ReleaseSysCache around line
4237 of backend/commands/tablecmds.c... though adding such a call
doesn't solve the problem.  Hrm.  This one's pretty easy to reproduce
and there doesn't seem to be any mystery about how to trigger it:

CREATE SCHEMA s;
CREATE FUNCTION s.f()
        RETURNS BIGINT
        EXTERNAL SECURITY DEFINER
        AS '
BEGIN
        EXECUTE ''CREATE LOCAL TEMP TABLE t (
                a TEXT NOT NULL,
                b TEXT
        ) WITHOUT OIDS ON COMMIT DROP;'';
        EXECUTE ''CREATE UNIQUE INDEX t_key_udx ON t(a);'';

        INSERT INTO t (a, b) VALUES (''foo''::TEXT, ''bar''::TEXT);
        IF NOT FOUND THEN
                RAISE EXCEPTION ''Unable to insert t'';
        END IF;

        RETURN 0::BIGINT;
END;
' LANGUAGE 'plpgsql';

BEGIN;
SELECT s.f();
COMMIT;
BEGIN;
SELECT s.f();
COMMIT;

And the output:

test=3D# BEGIN;
BEGIN
test=3D# SELECT s.f();
 f
---
 0
(1 row)

test=3D# COMMIT;
COMMIT
test=3D# BEGIN;
BEGIN
test=3D# SELECT s.f();
ERROR:  pg_class_aclcheck: relation 2265016 not found
CONTEXT:  PL/pgSQL function f line 8 at SQL statement


What bothers me about this, however, is that the functional equivalent
performed outside of a pl/pgsql function works which leads me to
believe that it's a pl/pgsql problem:

BEGIN;
CREATE LOCAL TEMP TABLE t (
       a TEXT NOT NULL,
       b TEXT
) WITHOUT OIDS ON COMMIT DROP;
CREATE UNIQUE INDEX t_key_udx ON t(a);

INSERT INTO t (a, b) VALUES ('foo'::TEXT, 'bar'::TEXT);
COMMIT;
BEGIN;
CREATE LOCAL TEMP TABLE t (
       a TEXT NOT NULL,
       b TEXT
) WITHOUT OIDS ON COMMIT DROP;
CREATE UNIQUE INDEX t_key_udx ON t(a);

INSERT INTO t (a, b) VALUES ('foo'::TEXT, 'bar'::TEXT);
COMMIT;


Anyway, I hope this helps.  -sc

--=20
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:
> CREATE FUNCTION s.f()
>         RETURNS BIGINT
>         EXTERNAL SECURITY DEFINER
>         AS '
> BEGIN
>         EXECUTE ''CREATE LOCAL TEMP TABLE t (
>                 a TEXT NOT NULL,
>                 b TEXT
>         ) WITHOUT OIDS ON COMMIT DROP;'';
>         EXECUTE ''CREATE UNIQUE INDEX t_key_udx ON t(a);'';

>         INSERT INTO t (a, b) VALUES (''foo''::TEXT, ''bar''::TEXT);

This is not going to work more than once, because the INSERT caches
a plan that refers to the first-time-through temp table.

You could put the INSERT into an EXECUTE as well.  Or use a different
PL language that doesn't cache plans.

            regards, tom lane

> > CREATE FUNCTION s.f()
> >         RETURNS BIGINT
> >         EXTERNAL SECURITY DEFINER
> >         AS '
> > BEGIN
> >         EXECUTE ''CREATE LOCAL TEMP TABLE t (
> >                 a TEXT NOT NULL,
> >                 b TEXT
> >         ) WITHOUT OIDS ON COMMIT DROP;'';
> >         EXECUTE ''CREATE UNIQUE INDEX t_key_udx ON t(a);'';
>
> >         INSERT INTO t (a, b) VALUES (''foo''::TEXT, ''bar''::TEXT);
>
> This is not going to work more than once, because the INSERT caches
> a plan that refers to the first-time-through temp table.
>
> You could put the INSERT into an EXECUTE as well.  Or use a different
> PL language that doesn't cache plans.

Hrm...  this limitation makes temporary tables that drop on commit +
pl/pgsql unusable beyond the 1st transaction.  Is there a mechanism to
test to see if a relation in a plan is a temporary table?  It seems as
though in pl_exec.c that around 1926 it'd be possible to add a test to
see if the plan uses temporary tables and add a new member to struct
expr telling exec_stmt_execsql to free the plan around line 2016 when
its cleaning up after itself.

For the archives, there are two workarounds for this:

1) Don't use ON COMMIT DROP, instead use ON COMMIT DELETE ROWS.  This
   preserves the relation thus all cached plans are still valid.
   Before creating the temporary table, however, you have to test for
   its existence.  This came out at about 0.4ms.

2) Use a FOR-IN-EXECUTE statement.  It's slower, but works (~1.2ms
   instead of 0.2ms.  On thousands of inserts a second and it makes a
   big difference).

-sc


PS For the sake of completeness, returning the value from CURRVAL()
   takes ~0.3ms from pl/pgsql and only ~0.14ms outside of pl/pgsql.
   The difference is the runtime cost of using pl/pgsql which is
   pretty reasonable given pl/pgsql walks an AST.

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:
> Hrm...  this limitation makes temporary tables that drop on commit +
> pl/pgsql unusable beyond the 1st transaction.  Is there a mechanism to
> test to see if a relation in a plan is a temporary table?  It seems as
> though in pl_exec.c that around 1926 it'd be possible to add a test to
> see if the plan uses temporary tables and add a new member to struct
> expr telling exec_stmt_execsql to free the plan around line 2016 when
> its cleaning up after itself.

That's entirely the wrong way to go about it.  Temp tables are only one
manifestation of a general problem with stale plans.  For instance, if
someone drops an index that your query uses, you've got a problem.

What we need is a general mechanism that keeps track of all the DB
objects that a cached plan depends on (tables, views, functions, yadda
yadda) and invalidates the plan when any of those objects are dropped or
altered.  At the low level, such a mechanism already exists (the
syscache inval stuff) --- the problem is to connect it up to plan
caching.

            regards, tom lane