Re: Adding foreign key constraints without integrity check?

On 6/18/2006 11:41 PM, Wes wrote:

> Is there a way to add a foreign key constraint without having to wait for it
> to check the consistency of all existing records? If a database is being
> reloaded (pg_dumpall then load), it really shouldn't be necessary to check
> the referential integrity - or at least I should be able to stipulate that I
> am accepting that risk.
>
> My database reload is currently taking about 6 hours to load the data, 42
> hours to reindex, and about another 40 hours or so to check the foreign key
> constraints (about 1.2 billion rows). That's a very long time to be out of
> commission. I'd really like to eliminate that second 40 hours so I can get
> it down to a normal weekend.

The original implementation when the feature was new was to restore the
foreign key constraints at the end of the dump with CREATE CONSTRAINT
TRIGGER, which does not check the existing data. pg_dump was changed to
use ALTER TABLE instead. Maybe it would be good to have the old
behaviour as an option?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #