The following bug has been logged online: Bug reference: 6147 Logged by: Jim McQuillan Email address: j...@avairis.com PostgreSQL version: 9.0.4 Operating system: Ubuntu 10.04 x86 32 and 64 bit Description: Changing search_path between invocations of a function ignored by 2nd invocation of function Details:
-- -- Calling a function twice, changing the schema search_path between calls -- The 2nd invocation doesn't notice the search_path has changed -- DROP SCHEMA IF EXISTS schema1 CASCADE; DROP SCHEMA IF EXISTS schema2 CASCADE; CREATE OR REPLACE FUNCTION public.test_func() RETURNS text AS $$ DECLARE result text; BEGIN SELECT value INTO result FROM test_table; RETURN result; END; $$ LANGUAGE plpgsql VOLATILE; CREATE SCHEMA schema1; CREATE SCHEMA schema2; CREATE TABLE schema1.test_table ( value text ); CREATE TABLE schema2.test_table ( value text ); INSERT INTO schema1.test_table ( value ) VALUES ( 'data from schema1' ); INSERT INTO schema2.test_table ( value ) VALUES ( 'data from schema2' ); SET search_path TO schema1; \echo 'calling test_func should result in "data from schema1"' SELECT public.test_func(); SET search_path TO schema2; \echo 'calling test_func should result in "data from schema2"' SELECT public.test_func(); ---------------------------------------------------------------- I found reference to this problem in the todo list but it's from Jan 2008 and there was a suggestion of waiting until 8.4. Well... here we are with 9.0.4 and it doesn't seem to be getting any attention. I'm not complaining. I know everybody has more than enough things to work on. I just wanted to bring it up and see if anybody has had a chance to think about it. Given the choice between taking a performance hit for invalidating the cache versus getting unexpected results by not following the new search_path, I'd prefer the performance hit. To me, correct results is preferred over fast results any day. I've already worked around this problem by using EXECUTE but that only happened after a customer got pretty irritated with us because our software wasn't behaving the way they expected. btw, Postgresql rocks and you guys do awesome work. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
