Re: [HACKERS] Re: [PATCHES] pg_dump primary keys

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Couldn't we solve this by the simple expedient of dumping all the
>> objects in the database in OID order?

> Wow, great idea! That might actually solve all (well, most) pg_dump
> related problems once and for all. Of course how you get all objects in
> the database in oid order is to be determined.

I think it would take some restructuring in pg_dump: instead of
processing each type of database object separately, it would have to
grab some info (at least the OIDs and types) for all the different
objects in the DB, then sort this info by OID, and finally get the
details and produce the output for each object in OID order.

This would still fail in some pathological cases involving ALTER --- for
example, make a table, later create a new datatype, and then ALTER TABLE
ADD COLUMN of that datatype. So the next refinement would be to examine
dependencies and do a topological sort rather than a simple sort by OID.
We'd still have to restructure pg_dump as above, though, and "examining
dependencies" is not exactly trivial for function bodies in unknown PL
languages...

If we had ALTER FUNCTION, which we don't but should, I think it would
actually be possible to create circular dependencies for which there is
*no* dump order that will work :-(. So I'm not sure it's worth the
trouble to add dependency extraction and a topological sort algorithm
to pg_dump rather than just sorting by OID. Dumping in OID order will
solve 99% of the problem with a fraction of the work.

regards, tom lane