Hi,
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
script:
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 $$
declare
v_table_full text := alvherre.dummy(p_name_table);
begin
return;
end;
$$;
BEGIN;
create table test.stuffs (stuff text);
SAVEPOINT a;
select broken('nonexistant.stuffs');
ROLLBACK TO a;
select broken('test.stuffs');
rollback;
The symptom is that the second call to broken() fails with this error
message:
ERROR: relation "" does not exist
CONTEXT: SQL function "dummy" statement 1
PL/pgSQL function "broken" line 3 during statement block local variable initialization
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 <alvherre@alvh.no-ip.org>
