Re: pg_dump: Sorted output, referential integrity

On Fri, Dec 07, 2001 at 03:16:26PM +0100, Christof Petig wrote:
> Philip Warner wrote:
> 
> Ah, yes. Now I remember it was you improving pg_dump.
> 
> > At 12:05 6/12/01 +0100, Christof Petig wrote:
> > >
> > >- pg_dump outputs the data unsorted
> >
> > Not quite correct; it outputs them in an order that is designed to improve
> > the chances of dependencies being satisfied, and improve the performance of
> > a full restore (a modified OID order).
> 
> That's perfect - unless you want to diff two pg_dumps

I've ran into this myself. However, I've never wanted to diff a full dump,
usually just schema comparisions - I usually _know_ which database has
the current data, I just want to be sure I can move it over. For schema
comparisions, it's easy enough to generate a 'diffable' file that reflects
the schema, something like:

select relname||'.'||attname from pg_class c, pg_attribute a
where attrelid=c.oid and attnum >0 and relname !~ '^pg' order by
relname,attname;

Hmm, I do see that sometimes it'd be nice to do a full diff, really. The
'oid order' was a nice hack to avoid having to do a full dependency
analysis on db objects, but they're not stable. I think with oids
going away as much as possible, anyway, we're probably going to have
to bite the bullet and do dependencies, one way or another. There are
a number of features that are often requested that all boil down to
knowing dependencies: dropping the auto-generated sequence for a serial,
along with the table - reparsing various functions/views/etc. when the
underlying tables are modified, etc.

Ross