Обсуждение: BUG #6316: function search_path causes set_config() is_local = true to have no effect

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


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 =3D true inside that function have no effect.=20

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&#=
39;, true );
END;
$$
SET search_path =3D public
;

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

SET search_path =3D public;
SHOW search_path;

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

SET search_path =3D 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 =3D DEFAULT;

SET search_path =3D 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=3D# \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=3D#

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


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
-----BEGIN PGP SIGNATURE-----
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk7YdKgACgkQRAk1+p0GhSFeHwCeJGNfW9VDeJ68EVZ/trLN6PpZ
SIsAnRlj/wr3YO+MF1lSeEEP1UzukhDI
=ZV2E
-----END PGP SIGNATURE-----