Re: set search_path in dump output considered harmful

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.

> 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;

> 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...

regards, tom lane