FAQ
The following bug has been logged online:

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
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.

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 1 | next ›
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedAug 4, '11 at 3:36a
activeAug 4, '11 at 3:36a
posts1
users1
websitepostgresql.org
irc#postgresql

1 user in discussion

Jim McQuillan: 1 post

People

Translate

site design / logo © 2021 Grokbase