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

On 18 June 2010 09:31, Jean-Baptiste Quenot <jbq(at)caraldi(dot)com> wrote:

> 2010/6/17 Greg Stark <gsstark(at)mit(dot)edu>:
> > On Thu, Jun 17, 2010 at 4:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.
>
> +1 IMHO PG should dump the bar() function call as bar.bar() to be
> safe. Using fully qualified function name is what I did in my source
> code, to work around this problem.
> --
> Jean-Baptiste Quenot
>

While this problem doesn't result in a unrestorable dump, it does mean
manual intervention would be required when perform a restore. I would
expect all backups to restore, so anything like this which causes issue at
restore time should really have been caught before it got into the database
itself.

What is causing the error is the creation of the index which actually goes
to use the function. In the OP's example, this is deemed okay since it's
found in the search path, but in theory, we could end up with the following
cases:

CREATE SCHEMA bar;
SET search_path = bar;

/* bar.bar() function */

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;

CREATE SCHEMA moo;
SET search_path = moo;

/* moo.bar() function */

CREATE FUNCTION bar() RETURNS text AS $$
BEGIN
RETURN 'moobar';
END
$$ LANGUAGE plpgsql IMMUTABLE;

/* CASE 1 */

CREATE TABLE foobar (d text);
insert into foobar (d) values ('foobar');

/* This index will use foo.foo() which in turn will use moo.bar() */
set search_path = foo, moo, bar;
CREATE INDEX foobar_d on foobar using btree(foo.foo());

/* CASE 2 */

CREATE TABLE foobar2 (d text);
insert into foobar2 (d) values ('foobar2');

/* This index will use foo.foo() which in turn will use bar.bar() */
set search_path = foo, bar, moo;
CREATE INDEX foobar2_d on foobar2 using btree(foo.foo());

While both tables are using the same function, they won't have the same
functionality due to the difference in the search path at the time of the
index creation. If someone inadvertently ended up with this configuration,
it could be very difficult to debug why they have different results.

If functions are parsed and resovled to prefix all contained function calls
with the relevant schema, then it would also have to prefix it regardless of
whether it's also in the current schema or not as the call could otherwise
be misdirected by a search path change prior to the container function's
usage.

The alternative, which isn't really an alternative, is to prevent the user
from creating a function with references to other functions without a
schemaname... which obviously wouldn't be backwards compatible.

Thom