Prev: Partitioning syntax
Next: [HACKERS] pg_dump does not honor namespaces when functions are used in index
From: Thom Brown on 17 Jun 2010 08:13
On 17 June 2010 12:31, Jean-Baptiste Quenot <jbq(a)caraldi.com> wrote:

> Dear hackers,
>
> I have a pretty nasty problem to submit to your careful scrutiny.
>
> Please consider the following piece of SQL code:
>
>
> CREATE SCHEMA bar;
> SET search_path = bar;
>
> CREATE FUNCTION bar() RETURNS text AS $$
> BEGIN
> RETURN 'foobar';
> END
> $$ LANGUAGE plpgsql IMMUTABLE;
>
> CREATE SCHEMA foo;
> SET search_path = foo;
>
> CREATE FUNCTION foo() RETURNS text AS $$
> BEGIN
> RETURN bar();
> END
> $$ LANGUAGE plpgsql IMMUTABLE;
>
> SET search_path = public;
>
> CREATE TABLE foobar (d text);
> insert into foobar (d) values ('foobar');
>
> set search_path = public, foo, bar;
> CREATE INDEX foobar_d on foobar using btree(foo());
>
>
> Run this on a newly created database, and dump it with pg_dump. You'll
> notice that the dump is unusable. Creating a new database from this
> dump will trigger the following error:
>
> ERROR: function bar() does not exist
> LINE 1: SELECT bar()
> ^
> HINT: No function matches the given name and argument types. You
> might need to add explicit type casts.
> QUERY: SELECT bar()
> CONTEXT: PL/pgSQL function "foo" line 2 at RETURN
>
> How can we fix this?
> --
> Jean-Baptiste Quenot
>
> --
>

I think Postgres doesn't check to see whether bar() exists in the current
search path when you create the foo() function, and since it isn't in the
foo() function's search path value, it fails to find the function when you
try to use it. It can probably be fixed (this specific case, not generally)
with:

ALTER FUNCTION foo.foo() SET search_path=foo, bar;

Thom
From: Greg Stark on 17 Jun 2010 11:30
On Thu, Jun 17, 2010 at 4:08 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote:
>> I actually wonder if we shouldn't automatically tag plpgsql functions
>> with the search_path in effect at the time of their creation (as if
>> the user had done ALTER FUNCTION ... SET search_path=...whatever the
>> current search path is...).
>
> That would be extremely expensive and not very backwards-compatible.
> In the case at hand, just writing "RETURN bar.bar();" would be the
> best-performing solution.
>

I wonder if we should have a mode for plpgsql functions where all name
lookups are done at definition time So the bar() function would be
resolved to bar.bar() and stored that way permanently so that pg_dump
dumped the definition as bar.bar().

That would be probably just as good as setting the search path on the
function for most users and better for some. It would have the same
problem with dynamic sql that a lot of things have though.

--
greg

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

From: Thom Brown on 17 Jun 2010 09:27
On 17 June 2010 14:20, Robert Haas <robertmhaas(a)gmail.com> wrote:

> On Thu, Jun 17, 2010 at 8:13 AM, Thom Brown <thombrown(a)gmail.com> wrote:
> > On 17 June 2010 12:31, Jean-Baptiste Quenot <jbq(a)caraldi.com> wrote:
> >>
> >> Dear hackers,
> >>
> >> I have a pretty nasty problem to submit to your careful scrutiny.
> >>
> >> Please consider the following piece of SQL code:
> >>
> >>
> >> CREATE SCHEMA bar;
> >> SET search_path = bar;
> >>
> >> CREATE FUNCTION bar() RETURNS text AS $$
> >> BEGIN
> >> RETURN 'foobar';
> >> END
> >> $$ LANGUAGE plpgsql IMMUTABLE;
> >>
> >> CREATE SCHEMA foo;
> >> SET search_path = foo;
> >>
> >> CREATE FUNCTION foo() RETURNS text AS $$
> >> BEGIN
> >> RETURN bar();
> >> END
> >> $$ LANGUAGE plpgsql IMMUTABLE;
> >>
> >> SET search_path = public;
> >>
> >> CREATE TABLE foobar (d text);
> >> insert into foobar (d) values ('foobar');
> >>
> >> set search_path = public, foo, bar;
> >> CREATE INDEX foobar_d on foobar using btree(foo());
> >>
> >>
> >> Run this on a newly created database, and dump it with pg_dump. You'll
> >> notice that the dump is unusable. Creating a new database from this
> >> dump will trigger the following error:
> >>
> >> ERROR: function bar() does not exist
> >> LINE 1: SELECT bar()
> >> ^
> >> HINT: No function matches the given name and argument types. You
> >> might need to add explicit type casts.
> >> QUERY: SELECT bar()
> >> CONTEXT: PL/pgSQL function "foo" line 2 at RETURN
> >>
> >> How can we fix this?
> >> --
> >> Jean-Baptiste Quenot
> >>
> >> --
> >
> > I think Postgres doesn't check to see whether bar() exists in the current
> > search path when you create the foo() function, and since it isn't in the
> > foo() function's search path value, it fails to find the function when
> you
> > try to use it. It can probably be fixed (this specific case, not
> generally)
> > with:
> >
> > ALTER FUNCTION foo.foo() SET search_path=foo, bar;
>
> I suppose that the root of the problem here is that foo() is not
> really immutable - it gives different results depending on the search
> path. It seems like that could bite you in a number of different
> ways.
>
> I actually wonder if we shouldn't automatically tag plpgsql functions
> with the search_path in effect at the time of their creation (as if
> the user had done ALTER FUNCTION ... SET search_path=...whatever the
> current search path is...). I suppose the current behavior could
> sometimes be useful but on the whole it seems more like a giant
> foot-gun which the user oughtn't to get unless they explicitly ask for
> it.
>
>
>
That wouldn't solve the problem in the above case since the search path at
the time of declaring the function was incorrect anyway as it didn't cover
the bar schema. It would fix cases where search paths are correctly set
before functions are created though. Unless there's a language-specific
parser to validate the content of functions, typos in function names will
cause the restoration of backups to fail.

Thom
From: Tom Lane on 17 Jun 2010 11:08
Robert Haas <robertmhaas(a)gmail.com> writes:
> I suppose that the root of the problem here is that foo() is not
> really immutable - it gives different results depending on the search
> path.

Yeah. The declaration of the function is broken --- it's not pg_dump's
fault that the function misbehaves.

> I actually wonder if we shouldn't automatically tag plpgsql functions
> with the search_path in effect at the time of their creation (as if
> the user had done ALTER FUNCTION ... SET search_path=...whatever the
> current search path is...).

That would be extremely expensive and not very backwards-compatible.
In the case at hand, just writing "RETURN bar.bar();" would be the
best-performing solution.

regards, tom lane

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

From: Robert Haas on 17 Jun 2010 09:20
On Thu, Jun 17, 2010 at 8:13 AM, Thom Brown <thombrown(a)gmail.com> wrote:
> On 17 June 2010 12:31, Jean-Baptiste Quenot <jbq(a)caraldi.com> wrote:
>>
>> Dear hackers,
>>
>> I have a pretty nasty problem to submit to your careful scrutiny.
>>
>> Please consider the following piece of SQL code:
>>
>>
>> CREATE SCHEMA bar;
>> SET search_path = bar;
>>
>> CREATE FUNCTION bar() RETURNS text AS $$
>> BEGIN
>> � �RETURN 'foobar';
>> END
>> $$ LANGUAGE plpgsql IMMUTABLE;
>>
>> CREATE SCHEMA foo;
>> SET search_path = foo;
>>
>> CREATE FUNCTION foo() RETURNS text AS $$
>> BEGIN
>> � �RETURN bar();
>> END
>> $$ LANGUAGE plpgsql IMMUTABLE;
>>
>> SET search_path = public;
>>
>> CREATE TABLE foobar (d text);
>> insert into foobar (d) values ('foobar');
>>
>> set search_path = public, foo, bar;
>> CREATE INDEX foobar_d on foobar using btree(foo());
>>
>>
>> Run this on a newly created database, and dump it with pg_dump. You'll
>> notice that the dump is unusable. �Creating a new database from this
>> dump will trigger the following error:
>>
>> ERROR: �function bar() does not exist
>> LINE 1: SELECT bar()
>> � � � � � � � ^
>> HINT: �No function matches the given name and argument types. You
>> might need to add explicit type casts.
>> QUERY: �SELECT bar()
>> CONTEXT: �PL/pgSQL function "foo" line 2 at RETURN
>>
>> How can we fix this?
>> --
>> Jean-Baptiste Quenot
>>
>> --
>
> I think Postgres doesn't check to see whether bar() exists in the current
> search path when you create the foo() function, and since it isn't in the
> foo() function's search path value, it fails to find the function when you
> try to use it.� It can probably be fixed (this specific case, not generally)
> with:
>
> ALTER FUNCTION foo.foo() SET search_path=foo, bar;

I suppose that the root of the problem here is that foo() is not
really immutable - it gives different results depending on the search
path. It seems like that could bite you in a number of different
ways.

I actually wonder if we shouldn't automatically tag plpgsql functions
with the search_path in effect at the time of their creation (as if
the user had done ALTER FUNCTION ... SET search_path=...whatever the
current search path is...). I suppose the current behavior could
sometimes be useful but on the whole it seems more like a giant
foot-gun which the user oughtn't to get unless they explicitly ask for
it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

 |  Next  |  Last
Pages: 1 2
Prev: Partitioning syntax
Next: [HACKERS] pg_dump does not honor namespaces when functions are used in index