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

BUG #6517: Volatile function erroneously optimized,does not consider change in schema path

From: rene(dot)vanpaassen(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6517: Volatile function erroneously optimized,does not consider change in schema path
Date: 2012-03-05 11:52:24
Message-ID: E1S4WSm-0000yu-Mr@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6517
Logged by:          Rene van Paassen
Email address:      rene(dot)vanpaassen(at)gmail(dot)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. 


Responses

pgsql-bugs by date

Next:From: vygenDate: 2012-03-05 12:14:26
Subject: BUG #6518: archive_command stderr not in log if log_destination=syslog
Previous:From: skambhapatiDate: 2012-03-05 10:02:01
Subject: BUG #6516: how to mound data folder in remote location

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