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

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=20
Description:=20=20=20=20=20=20=20=20

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,=20
-- 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=20

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

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

Greetings,=20

Ren=C3=A9

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

On Mon, Mar 5, 2012 at 6:52 AM,  <rene.vanpaassen@gmail.com> wrote:
> 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.

I have a vague feeling this is a known issue.  It sure seems like we
should handle it better, but I'm not sure how hard that would be to
implement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Mar 5, 2012 at 6:52 AM,  <rene.vanpaassen@gmail.com> wrote:
>> 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.

> I have a vague feeling this is a known issue.  It sure seems like we
> should handle it better, but I'm not sure how hard that would be to
> implement.

plpgsql intentionally caches the plan for the query as it was built with
the original search_path.  There's been talk of adjusting that behavior
but I'm worried that we might break as many cases as we fix ...

            regards, tom lane

On Mon, Mar 12, 2012 at 11:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, Mar 5, 2012 at 6:52 AM, =A0<rene.vanpaassen@gmail.com> wrote:
>>> 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 demonst=
rate
>>> this.
>
>> I have a vague feeling this is a known issue. =A0It sure seems like we
>> should handle it better, but I'm not sure how hard that would be to
>> implement.
>
> plpgsql intentionally caches the plan for the query as it was built with
> the original search_path. =A0There's been talk of adjusting that behavior
> but I'm worried that we might break as many cases as we fix ...

IMHO, the problem with the current behavior is that it's neither all
one thing nor all the other.  Using the definition-time search_path
seems defensible, and using the run-time search_path does, too.  But
we're not consistently doing either one, which doesn't seem good.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Mar 5, 2012 at 6:52 AM,  <rene.vanpaassen@gmail.com> wrote:
>
> set search_path to public;
>
> CREATE FUNCTION countusers()
> RETURNS INT
> AS $PROC$
> BEGIN
> =A0 =A0RETURN count(*) FROM users;
> END
> $PROC$ LANGUAGE 'plpgsql' VOLATILE;
>

i think you can workaround your problem using EXECUTE:

CREATE FUNCTION countusers()
RETURNS INT
AS $PROC$
DECLARE
   counter INT;
BEGIN
   EXECUTE 'SELECT count(*) FROM users' INTO counter;
=A0 =A0RETURN counter;
END
$PROC$ LANGUAGE 'plpgsql' VOLATILE;

--=20
Jaime Casanova=A0 =A0 =A0 =A0=A0 www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n

On 12 March 2012 16:32, Robert Haas <robertmhaas@gmail.com> wrote:

> On Mon, Mar 12, 2012 at 11:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Robert Haas <robertmhaas@gmail.com> writes:
> >> On Mon, Mar 5, 2012 at 6:52 AM,  <rene.vanpaassen@gmail.com> wrote:
> >>> 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.
> >
> >> I have a vague feeling this is a known issue.  It sure seems like we
> >> should handle it better, but I'm not sure how hard that would be to
> >> implement.
> >
> > plpgsql intentionally caches the plan for the query as it was built with
> > the original search_path.  There's been talk of adjusting that behavior
> > but I'm worried that we might break as many cases as we fix ...
>
> IMHO, the problem with the current behavior is that it's neither all
> one thing nor all the other.  Using the definition-time search_path
> seems defensible, and using the run-time search_path does, too.  But
> we're not consistently doing either one, which doesn't seem good.
>
>
Isn't this what the VOLATILE, STABLE and IMMUTABLE keywords should be for?
I don't like the current behaviour, because now VOLATILE is not volatile,
unless you close and re-open the database connection. There should at least
be a big fat warning about combining functions with changing search path
somewhere in the documentation.

Implementation-wise (but I have to admit I don't know the underlying code
at all), would it be possible to cache with the search_path as an index?


> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>



--=20
Ren=E9 van Paassen <Rene.vanPaassen@gmail.com>

On 12 March 2012 16:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Haas <robertmhaas@gmail.com> writes:
> > On Mon, Mar 5, 2012 at 6:52 AM,  <rene.vanpaassen@gmail.com> wrote:
> >> I found some unexpected behaviour when changing the schema search path
> in
> >> combination with plpgsql functions (may be true for other function typ=
es
> >> 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.
>
> > I have a vague feeling this is a known issue.  It sure seems like we
> > should handle it better, but I'm not sure how hard that would be to
> > implement.
>
> plpgsql intentionally caches the plan for the query as it was built with
> the original search_path.  There's been talk of adjusting that behavior
> but I'm worried that we might break as many cases as we fix ...
>

But since I can work around the problem by closing and opening the database
connection, the "original search_path" is thus the search path that the
function happened to run in for the first time with the current database
connection.

--=20
Ren=E9 van Paassen        | ______o____/_|     Rene.vanPaassen@gmail.com
                       <[___\_\_-----<               t: +31 15 2628685
                        |  o'                   mobile: +31 6 39846891