Re: Restoring a postgres database

On Wed, Jul 09, 2003 at 09:15:41AM +0200, Dennis Björklund wrote:
> On Wed, 9 Jul 2003, Tom Lane wrote:
>
> > > schema dump searching for dependancies. You can then use tsort to dump out
> > > the order that things should be restored in.
> >
> > Now that we have dependency tracking on the server side (see pg_depend)
> > this should be just a small matter of programming. No one's tackled it
> > yet though.
>
> It's not enough to just order things. To get something that always works
> one need code that can break up cycles in the graph. With alter table and
> other constructs it's easy to create objects that depend on each other.

Ah yes, but tsort tells you where the loop is and would be able to break it.
For example, if it were a function you would make a list of CREATE FUNCTION
with dummy bodies at the beginning and then a list of CREATE OR REPLACE
FUNCTION at the end with the real bodies. If there is an issue with the
DEFAULT of a table field it could break it out to an ALTER TABLE SET
DEFAULT.

In fact, you could take the ultra pessimistic route and change pg_dump to
dump in such a way that it will always work. That should be possible. Say
in the order:

languages, types, domains, functions with dummy bodies, tables in top-down
order but no defaults, functions with real bodies, table fields defaults,
triggers.

I've never had a database with recursive dependancies so maybe I'm
underestimating the problems here.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington