Bug reference: 6147
Logged by: Jim McQuillan
Email address: jam@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
-- 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 $$
SELECT value INTO result FROM test_table;
$$ 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"'
SET search_path TO schema2;
\echo 'calling test_func should result in "data from schema2"'
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.