Re: pg_dump does not honor namespaces when functions are used in index - Mailing list pgsql-hackers

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