The following bug has been logged on the website:
Bug reference: 6316
Logged by: Jon Erdman
Email address: [email protected]
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=#
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
