Re: set search_path in dump output considered harmful

On Thu, Jul 13, 2006 at 07:17:31PM -0400, Tom Lane wrote:
> Phil Frost <indigo(at)bitglue(dot)com> writes:
> > I've recently migrated one of my databases to using veil. This involved
> > creating a 'private' schema and moving all tables to it.
> > ...
> > In doing so, I found to my extreme displeasure that although the
> > database continues to function flawlessly, I can no longer restore dumps
> > produced by pg_dump even after hours of manual tweaking. In all cases,
> > this is due to search_path being frobbed during the restore.
>
> No, not one of these things can be blamed on pg_dump.
>
> > CASE 1: serial column not in the same schema as associated table
>
> This is not supported.

This fact is also not documented, and allowed without warning. Things
that people can do to get the database into an unsupported state are
most usually called "bugs".

> > CASE 2: default set to the serial sequence of another table
>
> This is actually the same thing as #1, because you did
> > alter sequence private.t_i_seq set schema public;

Agreed.

> > CASE 3: functions containing unqualified function references
>
> This is a buggy function definition, plain and simple. It will fail
> just as much in the original database as in the dumped/restored one;
> just invoke it with a search_path other than what it's expecting.
>
> There has been talk of attaching a search_path setting to each function
> so that it's independent of the caller's search_path, but the
> performance hit seems a bit daunting. In any case it's not pg_dump's
> fault that this feature doesn't exist...

I don't see anything in the documentation that says I must schema
qualify every name reference in functions. Yes, I could break my
function by changing the search_path but I bet this is true of most
functions out there. Do you schema qualifiy all your operator
references? Does this mean most functions are buggy? If this is the
case, perhaps a warning should be issued! The fact is that search_path
is an important setting that should not be frobbed lightly. You wouldn't
arbitrarily change the header file's you have included in a C program,
or clobber your OS's dynamic linker search path, would you?

If pg_dump would only output qualified names always instead of setting
search_path arbitrarily, this would not be a problem. I could run an
"alter database" prior to restoring the dump as I have done on my
production database and the dump could restore normally. Given that
pg_dump already does this for many of the statements it outputs, I don't
think it would be impossible to do it for all of them.

I do not see any good arguments for the current behaviour. One was made
that it allows one to change the schema of a collection of objects by
editing a single line, but in fact this isn't true because half of the
output statements contain a schema qualification dispite that
search_path has been set. If there are any others that I am missing,
please tell me.

Other people have complained that the lack of schema qualified names
makes the dump harder to read. Yesterday on IRC another user was looking
for help because his backup wouldn't restore, and it seems the problem
is related to search_path.