My plpgsql function seems to be ignoring search_path when looking for
the right table to select from. I'm running 7.3.4. Below is my test
code. Am I doing something wrong?

TIA, Gene Chow

test=> create or replace function getval() returns varchar as '
test'> declare val varchar;
test'> begin
test'> select into val value from bar limit 1;
test'> return val;
test'> end;' language 'plpgsql';
CREATE FUNCTION

test=> create table public.bar ( value varchar );
CREATE TABLE

test=> insert into public.bar values ('public value');
INSERT 4012748 1

test=> create schema foo;
CREATE SCHEMA

test=> create table foo.bar ( value varchar );
CREATE TABLE

test=> insert into foo.bar values ('foo value');
INSERT 4012754 1

test=> set search_path to foo, public;
SET

test=> select *, getval() from bar;
value | getval
-----------+-----------
foo value | foo value
(1 row)

test=> set search_path to public;
SET

test=> select *, getval() from bar;
value | getval
--------------+-----------
public value | foo value
(1 row)

Search Discussions

  • Bruce Momjian at Sep 6, 2003 at 11:21 pm
    I think the problem is that the first time the function is called, it is
    compiled and cached for later use. At that time the function is bound
    to the table oid, so even though you change the search path, the cached
    copy still calls the old table.

    If you exit psql and re-enter, or change the schema _before_ you call
    the function for the first time, it should be fine.

    This highlights another problem with our plpgsql function caching.

    ---------------------------------------------------------------------------

    Eugene Chow wrote:
    My plpgsql function seems to be ignoring search_path when looking for
    the right table to select from. I'm running 7.3.4. Below is my test
    code. Am I doing something wrong?

    TIA, Gene Chow

    test=> create or replace function getval() returns varchar as '
    test'> declare val varchar;
    test'> begin
    test'> select into val value from bar limit 1;
    test'> return val;
    test'> end;' language 'plpgsql';
    CREATE FUNCTION

    test=> create table public.bar ( value varchar );
    CREATE TABLE

    test=> insert into public.bar values ('public value');
    INSERT 4012748 1

    test=> create schema foo;
    CREATE SCHEMA

    test=> create table foo.bar ( value varchar );
    CREATE TABLE

    test=> insert into foo.bar values ('foo value');
    INSERT 4012754 1

    test=> set search_path to foo, public;
    SET

    test=> select *, getval() from bar;
    value | getval
    -----------+-----------
    foo value | foo value
    (1 row)

    test=> set search_path to public;
    SET

    test=> select *, getval() from bar;
    value | getval
    --------------+-----------
    public value | foo value
    (1 row)


    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Eugene Chow at Sep 6, 2003 at 11:30 pm
    Thanks. If I had only read the manual before posting. I solved it by
    using 'execute' instead of 'select into' since the search_path is not
    static in my system.

    Gene
    On Saturday, September 6, 2003, at 04:21 PM, Bruce Momjian wrote:


    I think the problem is that the first time the function is called, it
    is
    compiled and cached for later use. At that time the function is bound
    to the table oid, so even though you change the search path, the cached
    copy still calls the old table.

    If you exit psql and re-enter, or change the schema _before_ you call
    the function for the first time, it should be fine.

    This highlights another problem with our plpgsql function caching.

    -----------------------------------------------------------------------
    ----

    Eugene Chow wrote:
    My plpgsql function seems to be ignoring search_path when looking for
    the right table to select from. I'm running 7.3.4. Below is my test
    code. Am I doing something wrong?

    TIA, Gene Chow

    test=> create or replace function getval() returns varchar as '
    test'> declare val varchar;
    test'> begin
    test'> select into val value from bar limit 1;
    test'> return val;
    test'> end;' language 'plpgsql';
    CREATE FUNCTION

    test=> create table public.bar ( value varchar );
    CREATE TABLE

    test=> insert into public.bar values ('public value');
    INSERT 4012748 1

    test=> create schema foo;
    CREATE SCHEMA

    test=> create table foo.bar ( value varchar );
    CREATE TABLE

    test=> insert into foo.bar values ('foo value');
    INSERT 4012754 1

    test=> set search_path to foo, public;
    SET

    test=> select *, getval() from bar;
    value | getval
    -----------+-----------
    foo value | foo value
    (1 row)

    test=> set search_path to public;
    SET

    test=> select *, getval() from bar;
    value | getval
    --------------+-----------
    public value | foo value
    (1 row)


    ---------------------------(end of
    broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania
    19073
  • Bruce Momjian at Sep 7, 2003 at 1:37 am

    Eugene Chow wrote:
    Thanks. If I had only read the manual before posting. I solved it by
    using 'execute' instead of 'select into' since the search_path is not
    static in my system.
    Yep, that's the workaround mentioned in the FAQ.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Tom Lane at Sep 7, 2003 at 1:27 am

    Bruce Momjian writes:
    This highlights another problem with our plpgsql function caching.
    It's a little disturbing to think that any change in SEARCH_PATH might
    force us to discard all cached plans. That could be expensive; and
    consider a function that deliberately sets SEARCH_PATH to ensure that
    it gets the tables it wants. You wouldn't want such a function to be
    unable to cache any plans across calls (not to mention blowing away
    every other function's plans, too).

    We'd probably better record with each plan the SEARCH_PATH it was
    generated with. Then, as long as that matches the current setting,
    we can re-use the plan.

    Of course, none of this is going to happen until someone gets around to
    creating infrastructure for flushing cached plans at need. Right at the
    moment the answer is going to have to be "don't do that".

    regards, tom lane
  • Bruce Momjian at Sep 7, 2003 at 1:40 am

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    This highlights another problem with our plpgsql function caching.
    It's a little disturbing to think that any change in SEARCH_PATH might
    force us to discard all cached plans. That could be expensive; and
    consider a function that deliberately sets SEARCH_PATH to ensure that
    it gets the tables it wants. You wouldn't want such a function to be
    unable to cache any plans across calls (not to mention blowing away
    every other function's plans, too).

    We'd probably better record with each plan the SEARCH_PATH it was
    generated with. Then, as long as that matches the current setting,
    we can re-use the plan.

    Of course, none of this is going to happen until someone gets around to
    creating infrastructure for flushing cached plans at need. Right at the
    moment the answer is going to have to be "don't do that".
    Yep. I was just surprised it highlighted another failure of cached
    plans.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Gaetano Mendola at Sep 7, 2003 at 5:23 pm

    "Bruce Momjian" wrote:
    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    This highlights another problem with our plpgsql function caching.
    It's a little disturbing to think that any change in SEARCH_PATH might
    force us to discard all cached plans. That could be expensive; and
    consider a function that deliberately sets SEARCH_PATH to ensure that
    it gets the tables it wants. You wouldn't want such a function to be
    unable to cache any plans across calls (not to mention blowing away
    every other function's plans, too).

    We'd probably better record with each plan the SEARCH_PATH it was
    generated with. Then, as long as that matches the current setting,
    we can re-use the plan.

    Of course, none of this is going to happen until someone gets around to
    creating infrastructure for flushing cached plans at need. Right at the
    moment the answer is going to have to be "don't do that".
    Yep. I was just surprised it highlighted another failure of cached
    plans.
    There is already a TODO for it ?


    Regards
    Gaetano Mendola
  • Bruce Momjian at Sep 7, 2003 at 9:59 pm

    Gaetano Mendola wrote:
    "Bruce Momjian" wrote:
    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    This highlights another problem with our plpgsql function caching.
    It's a little disturbing to think that any change in SEARCH_PATH might
    force us to discard all cached plans. That could be expensive; and
    consider a function that deliberately sets SEARCH_PATH to ensure that
    it gets the tables it wants. You wouldn't want such a function to be
    unable to cache any plans across calls (not to mention blowing away
    every other function's plans, too).

    We'd probably better record with each plan the SEARCH_PATH it was
    generated with. Then, as long as that matches the current setting,
    we can re-use the plan.

    Of course, none of this is going to happen until someone gets around to
    creating infrastructure for flushing cached plans at need. Right at the
    moment the answer is going to have to be "don't do that".
    Yep. I was just surprised it highlighted another failure of cached
    plans.
    There is already a TODO for it ?
    Yep:

    o Fix problems with complex temporary table creation/destruction
    without using PL/PgSQL EXECUTE, needs cache prevention/invalidation

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedSep 5, '03 at 11:36p
activeSep 7, '03 at 9:59p
posts8
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase