On Thu, Jun 17, 2010 at 8:13 AM, Thom Brown <thombr...@gmail.com> wrote: > On 17 June 2010 12:31, Jean-Baptiste Quenot <j...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers