Re: pg_dump | pg_sql: insert commands and foreign key constraints

Alan Hodgson wrote:
> On Tuesday 23 September 2008, William Garrison <postgres@mobydisk.com>
> wrote:
>> 1) other workarounds
>> 2) someone else who can confirm that this bug is either fixed, or not
>> fixed.  If it is supposedly fixed, then I guess I need to make a smaller
>> version of my database to demonstrate the problem.
>
> AFAIK, the dumps created by pg_dump create all the constraints after the
> table data is all loaded - there are no foreign keys in place when the data
> is restored, so conflicts are not possible.

It's a data only dump; the constraints already exist as part of the
schema, and the SQL generated by pg_dump won't disable them for the load
(as that might permit invalid data to be loaded; there's no guarantee
that the constraints currently defined are the same ones as were present
when the dump was taken).

If you really want to load the data, at present you either need to load
the tables in the right order, either by manually chopping and changing
the dump or by using a -Fc dump and pg_restore, or you need to disable
triggers before the load and accept the risk of invalid data being loaded.

IIRC a patch was circulating (maybe applied to 8.4?) that tries to map
foreign-key relationships and where possible dump data in dependency
order so that data-only dumps without circular foreign key references
will restore correctly with no special user action.

--
Craig Ringer