Skip site navigation (1) Skip section navigation (2)

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

From: "Jim McQuillan" <jam(at)Avairis(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6147: Changing search_path between invocations of a function ignored by 2nd invocation of function
Date: 2011-08-04 03:36:49
Message-ID: 201108040336.p743anoI001552@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

pgsql-bugs by date

Next:From: simonrodanDate: 2011-08-04 04:26:31
Subject: Re: BUG #6120: Problem running post-install step in Mac OS X Lion (GM)
Previous:From: Craig RingerDate: 2011-08-04 01:21:17
Subject: Re: BUG #6143: connection problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group