Re: pg_dump: Sorted output, referential integrity statements - Mailing list pgsql-hackers

From Christof Petig
Subject Re: pg_dump: Sorted output, referential integrity statements
Date
Msg-id 3C10B18D.30CB3F7@petig-baender.de
Whole thread Raw
In response to Re: pg_dump: Sorted output, referential integrity statements  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
List pgsql-hackers
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




pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: New NLS status page
Next
From: Tom Lane
Date:
Subject: Re: Second call for platform testing