Re: pg_dump search path issue

Elijah Zupancic <elijah@zupancic.name> writes:
> In the SQL dump, you will notice that the SET search_path = xxx values
> will often not include the public schema which holds the functions
> needed to properly recreate tables that depend on extensions.

All the cases I've seen of this involve user-defined functions that are
broken, often dangerously so.  A function should not assume that it's
being called with any particular search_path; if it's intended for use in
a multi-schema database, good practice is to either explicitly qualify
names or use a SET clause to force the search_path to be what it expects.

> It seems like the code that generates the SET search_path should check
> to see if any of the objects it is dumping depend on functions that
> use the public schema.

If that didn't involve solving the halting problem, we might try to do
it.  But for better or worse, functions in Postgres are mostly black boxes
so far as callers are concerned.  It's not possible for pg_dump to know
that some function has an expectation of being invoked with a particular
search path.

            regards, tom lane