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

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 3 | next ›
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