FAQ
The following bug has been logged on the website:

Bug reference: 6517
Logged by: Rene van Paassen
Email address: rene.vanpaassen@gmail.com
PostgreSQL version: 9.1.2
Operating system: Fedora 16
Description:

I found some unexpected behaviour when changing the schema search path in
combination with plpgsql functions (may be true for other function types
too, did not check). This occurs both in 9.1.2 (on Fedora, 64 bit) and 8.4.9
(Centos 6, 32 bit). I created a small example run with psql, to demonstrate
this.

-- play with two schemas
create schema s1;
create schema s2;

-- create a table in s1 and add some values
set search_path to s1;
create table users ( u INT );
INSERT INTO users VALUES(1);
INSERT INTO users VALUES(2);

-- same for second schema
set search_path to s2;
create table users ( u INT );
INSERT INTO users VALUES(3);

-- directly evaluating the query is OK
set search_path to s1;
select count(*) from users;

set search_path to s2;
select count(*) from users;

-- now with a function
create language 'plpgsql'; -- only for 8.4.9
set search_path to public;

CREATE FUNCTION countusers()
RETURNS INT
AS $PROC$
BEGIN
RETURN count(*) FROM users;
END
$PROC$ LANGUAGE 'plpgsql' VOLATILE;

-- first run it with the users table in s1
set search_path to public,s1;
select countusers();

-- now it should run with the users table in s2,
-- but it actually runs with the table in s1 again
set search_path to public,s2;
select countusers();

Is this behaviour as it should be? It seems like the second query is
optimized away, despite the VOLATILE keyword on the function

I also saw bug report

http://archives.postgresql.org/pgsql-general/2011-05/msg00999.php

which seems to be a more specific version of this same problem.

Greetings,

René

p.s. Thanks for a wonderful database program. I fixed the problem for now in
my python web app by closing and opening the database before each change in
schema search path.

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 7 | next ›
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedMar 5, '12 at 11:52a
activeMar 14, '12 at 8:22a
posts7
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase