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 <alvhe...@alvh.no-ip.org>
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: http://www.postgresql.org/mailpref/pgsql-hackers