A customer was hitting some misbehavior in one of their internal tests and
I tracked it down to plancache not behaving properly with
subtransactions: in particular, a plan is not being marked "dead" when
the subtransaction on which it is planned rolls back.  It was reported
in 8.4, but I can reproduce the problem on 9.0 too with this small

        drop schema alvherre cascade;
        drop schema test cascade;
        create schema test;
        create schema alvherre;
        set search_path = 'alvherre';

        create or replace function dummy(text) returns text language sql
        as $$ SELECT relname::text FROM pg_class c WHERE c.oid = $1::regclass 

        create or replace function broken(p_name_table text) returns void
        language plpgsql as $$
        v_table_full text := alvherre.dummy(p_name_table);

         create table test.stuffs (stuff text);
         SAVEPOINT a;
         select broken('nonexistant.stuffs');

         ROLLBACK TO a;
         select broken('test.stuffs');


The symptom is that the second call to broken() fails with this error

ERROR:  relation "" does not exist
CONTEXT:  SQL function "dummy" statement 1
PL/pgSQL function "broken" line 3 during statement block local variable 

Note that this is totally bogus, because the relation being referenced
does indeed exist.  In fact, if you commit the transaction and call the
function again, it works.

Also, the state after the first call is a bit bogus: if you repeat the
whole sequence starting at the BEGIN line, it causes a crash on 8.4.

I hacked up plancache a bit so that it marks plans as dead when the
subtransaction resource owner releases it.  It adds a new arg to
ReleaseCachedPlan(); if true, the plan is marked dead.  All current
callers, except the one in ResourceOwnerReleaseInternal(), use false
thus preserving the current behavior.  resowner sets this as true when
aborting a (sub)transaction.

I have to admit that it seems somewhat the wrong API, but I don't see a
better way.  (I thought above relcache or syscache inval, but as far as
I can't tell there isn't any here).  I'm open to suggestions.

Patch attached.

Álvaro Herrera <alvhe...@alvh.no-ip.org>

Attachment: 0001-Mark-a-cache-plan-as-dead-when-aborting-its-creating.patch
Description: Binary data

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to