Re: pg_dump does not honor namespaces when functions are used in index

On 17 June 2010 14:20, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Jun 17, 2010 at 8:13 AM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> > On 17 June 2010 12:31, Jean-Baptiste Quenot <jbq(at)caraldi(dot)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