The following bug has been logged on the website:

Bug reference: 6316
Logged by: Jon Erdman
Email address: postgresql@thewickedtribe.net
PostgreSQL version: 9.1.1
Operating system: Ubuntu
Description:


Hi Tom! :)

So, found this in 8.3 but tested and it effects everything up to 9.1.1. If
search_path on a function is set to anything, calls to set_config() with
is_local = true inside that function have no effect.

See test case and output below:

BEGIN;

CREATE OR REPLACE FUNCTION public.setting_bug_true()
RETURNS VOID LANGUAGE plpgsql AS
$$
BEGIN
PERFORM pg_catalog.set_config( 'search_path', 'pg_catalog', true );
END;
$$
SET search_path = public
;

CREATE OR REPLACE FUNCTION public.setting_bug_false()
RETURNS VOID LANGUAGE plpgsql AS
$$
BEGIN
PERFORM pg_catalog.set_config( 'search_path', 'pg_catalog', false );
END;
$$
SET search_path = public
;

SET search_path = public;
SHOW search_path;

SELECT public.setting_bug_true();
\echo Search path should now be pg_catalog
SHOW search_path;

SET search_path = public;
SHOW search_path;

SELECT public.setting_bug_false();
\echo Oddly, if is_local is false, it *does* work
SHOW search_path;

ALTER FUNCTION public.setting_bug_true() SET search_path = DEFAULT;

SET search_path = public;
SHOW search_path;

SELECT public.setting_bug_true();
\echo Take search_path off the function and it works!?! /me smells a bug...
SHOW search_path;

ROLLBACK;





And the output:

postgres@[local]/cnuapp_dev:5437=# \i ~/bug.sql
BEGIN
Time: 0.070 ms
CREATE FUNCTION
Time: 0.208 ms
CREATE FUNCTION
Time: 0.164 ms
SET
Time: 0.055 ms
search_path
-------------
public
(1 row)

Time: 0.025 ms
setting_bug_true
------------------

(1 row)

Time: 0.138 ms
Search path should now be pg_catalog
search_path
-------------
public
(1 row)

Time: 0.022 ms
SET
Time: 0.019 ms
search_path
-------------
public
(1 row)

Time: 0.023 ms
setting_bug_false
-------------------

(1 row)

Time: 0.085 ms
Oddly, if is_local is false, it *does* work
search_path
-------------
pg_catalog
(1 row)

Time: 0.021 ms
ALTER FUNCTION
Time: 0.051 ms
SET
Time: 0.014 ms
search_path
-------------
public
(1 row)

Time: 0.018 ms
setting_bug_true
------------------

(1 row)

Time: 0.108 ms
Take search_path off the function and it works!?! /me smells a bug...
search_path
-------------
pg_catalog
(1 row)

Time: 0.018 ms
ROLLBACK
Time: 0.050 ms
postgres@[local]/cnuapp_dev:5437=#

Search Discussions

  • Tom Lane at Dec 2, 2011 at 6:27 am

    postgresql@thewickedtribe.net writes:
    So, found this in 8.3 but tested and it effects everything up to 9.1.1. If
    search_path on a function is set to anything, calls to set_config() with
    is_local = true inside that function have no effect.
    Isn't that exactly what the CREATE FUNCTION reference page says?

    The SET clause causes the specified configuration parameter to
    be set to the specified value when the function is entered, and
    then restored to its prior value when the function exits. SET
    FROM CURRENT saves the session's current value of the parameter
    as the value to be applied when the function is entered.

    If a SET clause is attached to a function, then the effects of a
    SET LOCAL command executed inside the function for the same
    variable are restricted to the function: the configuration
    parameter's prior value is still restored at function
    exit. However, an ordinary SET command (without LOCAL) overrides
    the SET clause, much as it would do for a previous SET LOCAL
    command: the effects of such a command will persist after
    function exit, unless the current transaction is rolled back.


    regards, tom lane
  • Jon Erdman at Dec 2, 2011 at 6:48 am
    DOH! *facepalm* Thanks Tom!

    I read docs on set_config() and didn't think to check CREATE FUNCTION.
    It does make sense, and now that you point it out, I do remember that
    too. I didn't think to check it because this function is *not* secdef,
    which is when you'd normally set a search_path on it (a dev
    accidentally set it on a non-secdef function).

    Thanks, and apologies for the false alarm.

    /me slinks away sheepishly...
    - --
    Jon T Erdman (aka StuckMojo)
    PostgreSQL Zealot
    On 12/02/2011 12:27 AM, Tom Lane wrote:
    postgresql@thewickedtribe.net writes:
    So, found this in 8.3 but tested and it effects everything up to
    9.1.1. If search_path on a function is set to anything, calls to
    set_config() with is_local = true inside that function have no
    effect.
    Isn't that exactly what the CREATE FUNCTION reference page says?

    The SET clause causes the specified configuration parameter to be
    set to the specified value when the function is entered, and then
    restored to its prior value when the function exits. SET FROM
    CURRENT saves the session's current value of the parameter as the
    value to be applied when the function is entered.

    If a SET clause is attached to a function, then the effects of a
    SET LOCAL command executed inside the function for the same
    variable are restricted to the function: the configuration
    parameter's prior value is still restored at function exit.
    However, an ordinary SET command (without LOCAL) overrides the SET
    clause, much as it would do for a previous SET LOCAL command: the
    effects of such a command will persist after function exit, unless
    the current transaction is rolled back.


    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedDec 2, '11 at 6:21a
activeDec 2, '11 at 6:48a
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Jon Erdman: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase