Re: pg_dump: Sorted output, referential integrity statements

On Thu, 6 Dec 2001, Christof Petig wrote:

> - pg_dump outputs referential constraints as 3 triggers (near to two
> different tables) per constraint. A mode which outputs the original
> statement (alter table ... add constraint) would be more sql standard
> conformant, portable and readable. But ... you might get into trouble if
> the referenced table creation command is output later.

There's some interesting timing things with this.  Pretty much the
alter statements have to be after the creates for all the tables at least
due to recursive constraints.  When you're using insert statements (-d)
since the restore doesn't appear to be in a transaction, all the data
needs to have been loaded as well (again due to recursive constraints).
In fact, there's *no* guarantee that even with a transaction that a
restore of the current database state statement by statement will succeed
since the user may have done odd things to insert the data.
If the data's already there, the alter table is going to check each row
for validity which can be kinda slow right now on big restores, we'd
probably need to make a better check.