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

  • Robert Haas at Mar 12, 2012 at 3:04 pm

    On Mon, Mar 5, 2012 at 6:52 AM, 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
  • Tom Lane at Mar 12, 2012 at 3:16 pm

    Robert Haas writes:
    On Mon, Mar 5, 2012 at 6:52 AM, 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
  • Robert Haas at Mar 12, 2012 at 3:33 pm

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

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Jaime Casanova at Mar 12, 2012 at 5:43 pm

    On Mon, Mar 5, 2012 at 6:52 AM, wrote:
    set search_path to public;

    CREATE FUNCTION countusers()
    RETURNS INT
    AS $PROC$
    BEGIN
    RETURN 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;
    RETURN counter;
    END
    $PROC$ LANGUAGE 'plpgsql' VOLATILE;

    --
    Jaime Casanova         www.2ndQuadrant.com
    Professional PostgreSQL: Soporte 24x7 y capacitación
  • Rene van Paassen at Mar 14, 2012 at 8:13 am

    On 12 March 2012 16:32, Robert Haas wrote:
    On Mon, Mar 12, 2012 at 11:16 AM, Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Mon, Mar 5, 2012 at 6:52 AM, 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


    --
    René van Paassen <Rene.vanPaassen@gmail.com>
  • Rene van Paassen at Mar 14, 2012 at 8:22 am

    On 12 March 2012 16:16, Tom Lane wrote:

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

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

Related Discussions

Discussion Navigation
viewthread | post
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