"SET search_path" clause ignored during function creation

Erwin Brandstetter

unread,

May 6, 2010, 10:26:06 AM5/6/10

to

As discussed in irc://freenode/postgresql (2010-05-06 16:20)
Function bodies are checked using the _current_ search_path instead of
the search_path supplied by the "SET search_path" clause.
This leads to wrong error messages during creation.
On the other hand it can make a function body pass the check, even
though it will raise an error is use.

Proposed solution: Function bodies should be checked with the
search_path provided by "SET search_path" an _not_ with the current
search path at the time pof creation.

Ho to reproduce the bug:

/*
event=# show search_path;
search_path
-------------
public
*/

CREATE SCHEMA foo;
CREATE TABLE foo.adr
( adr_id integer primary key,
note text);
INSERT INTO foo.adr VALUES (1, 'note from table foo.adr');

CREATE FUNCTION f_test()
RETURNS text AS
'SELECT note FROM adr where adr_id = 1'
LANGUAGE 'sql'
SET search_path=foo;
-- ERROR: relation "adr" does not exist
-- LINE 3: 'SELECT note FROM adr where adr_id = 1'

-- Function body is falsely checked with "search_path=
public" (current search_path) instead of "search_path=foo" AS it
should be!

-- If I disable check_function_bodies before creation, creation works
and the function call works and returns the value of foo.adr
correctly.
SET check_function_bodies=false;

Regards
Erwin Brandstetter

Takahiro Itagaki

unread,

May 10, 2010, 2:34:19 AM5/10/10

to

Erwin Brandstetter <brsa...@gmail.com> wrote:

> Function bodies are checked using the _current_ search_path instead of
> the search_path supplied by the "SET search_path" clause.
>

> Proposed solution: Function bodies should be checked with the
> search_path provided by "SET search_path" an _not_ with the current
> search path at the time pof creation.

Thanks for the report! Please check whether the attached patch
is the correct fix. An additional regression test is included.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

create_function_with_search_path.patch

Tom Lane

unread,

May 10, 2010, 1:56:54 PM5/10/10

to

Takahiro Itagaki <itagaki....@oss.ntt.co.jp> writes:
> Thanks for the report! Please check whether the attached patch
> is the correct fix. An additional regression test is included.

That's going to provoke "uninitialized variable" compiler warnings,
but otherwise it seems reasonably sane.

I don't particularly see the point of the added regression test.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Takahiro Itagaki

unread,

May 11, 2010, 12:59:00 AM5/11/10

to

Tom Lane <t...@sss.pgh.pa.us> wrote:

> Takahiro Itagaki <itagaki....@oss.ntt.co.jp> writes:
> > Thanks for the report! Please check whether the attached patch
> > is the correct fix. An additional regression test is included.
>
> That's going to provoke "uninitialized variable" compiler warnings,
> but otherwise it seems reasonably sane.

I applied a revised version that can surpress compiler warnings
to 9.0beta, 8.4 and 8.3.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

--