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

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