BUG #6147: Changing search_path between invocations of a function ignored by 2nd invocation of function

The following bug has been logged online:

Bug reference: 6147
Logged by: Jim McQuillan
Email address: jam(at)Avairis(dot)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.