Greetings,
In doing some test on 8.3RC2, I was dismayed to discover that the pl/pgsql plan invalidation logic added doesn't
considerchanging the search_path to invalidate a plan.
Our case is where we have a number of schemas with identical table structures but differing table contents. We then
havefunctions which operate across the tables in those schemas. For our functions which build up a string and then
execute,everything is fine (though the command has to be re-planned every time). For those functions where we don't
actuallyneed to build up the query dynamically, things work provided we only work in one schema during a session.
If we change the search_path after having run the function, the function doesn't pick up on the new tables (it uses
thesame ones it used in the first run). This can be pretty frustrating and I had really hoped that the plan
invalidationadded in 8.3 would handle this case. Here's an example:
set search_path=sfrost; create table a (col1 integer); insert into a values (1);
create or replace function test1 () returns integer as $_$ declare myint integer; begin select into myint col1 from a;
returnmyint; end; $_$ language plpgsql;
set search_path=sfrost2; create table a (col1 integer); insert into a values (2);
set search_path=sfrost; select test1(); -- returns '1'
set search_path=sfrost2; select sfrost.test1(); -- *also* returns '1', instead of '2'
Would it be possible to have this case handled?
Thanks,
Stephen
