Lists: | pgsql-bugspgsql-hackers |
---|
From: | Erwin Brandstetter <brsaweda(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | "SET search_path" clause ignored during function creation |
Date: | 2010-05-06 14:26:06 |
Message-ID: | 9a425d37-9332-4c3b-80fd-683a7fdb6b0a@p2g2000yqh.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
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
From: | Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Erwin Brandstetter <brsaweda(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: "SET search_path" clause ignored during function creation |
Date: | 2010-05-10 06:34:19 |
Message-ID: | 20100510153419.9C58.52131E4D@oss.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
Erwin Brandstetter <brsaweda(at)gmail(dot)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
Attachment | Content-Type | Size |
---|---|---|
create_function_with_search_path.patch | application/octet-stream | 2.8 KB |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> |
Cc: | Erwin Brandstetter <brsaweda(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: "SET search_path" clause ignored during function creation |
Date: | 2010-05-10 17:56:54 |
Message-ID: | 13586.1273514214@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)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
From: | Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Erwin Brandstetter <brsaweda(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [HACKERS] "SET search_path" clause ignored during function creation |
Date: | 2010-05-11 04:59:00 |
Message-ID: | 20100511135859.D618.52131E4D@oss.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)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