Re: BUG #2308: pg_dump -a does not respect referential dependencies

Jim Nasby <decibel(at)decibel(dot)org> writes:
> BTW, it would be really nice if we provided a better way to do this
> than manually dropping all the FK constraints and adding them back in
> later. Would it be difficult to allow deferring all constraints in
> the database during a specified transaction? That would allow for
> loading the data in a transaction and doing the constraint checking
> later...

You can try SET CONSTRAINTS ALL DEFERRED, but that only works for
constraints that are declared deferrable, which by default FK
constraints are not (stupid but that's what the spec requires).
In any case this would still have performance issues because the
behavior is tuned for transactions that update relatively small
numbers of rows. Drop/add constraint is a lot better choice in
the context of a bulk load.

I was toying just now with the idea of a pg_dump mode that would issue
the drop and re-add constraint commands for you. This would only help
for constraints that pg_dump knows of (ie were in the source database),
not any random new FK constraints that might be in the DB you are
loading into, but it'd sure beat doing it manually.

regards, tom lane