Re: pg_dump: Sorted output, referential integrity statements

Stephan Szabo wrote:

Since nobody answered concerning the sort issue, I guess
- nobody is planning or implementing this
- nobody disagrees this might be handy to have

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

The propose was mainly made to make the output more readable if you dump a
single table (per pg_dump call). This would also use portable sql commands so
it's easier to migrate data (given that you also specify -D).

Yours
Christof