Re: pg_dump and ALTER TABLE / ADD FOREIGN KEY

On Sat, 22 Jun 2002, Matthew T. O'Connor wrote:

> > However, others don't believe constraints other than foreign keys
> > should go unchecked.
> >
> > That said, is this functionality wanted outside of pg_dump /
> > pg_restore?
>
> pg_dump should reload a database as it was stored in the previous database.
> If your old data is not clean, pg_dump / restore is not a very good tool for
> cleaning it up. I think ignoring contrains is a good thing if it will load
> the data faster (at least when you are doing a database backup / restore).
> Why can't we do all alter table commands (that add constraints) after we load
> the data, that way we don't need to alter syntax at all.

That doesn't help. ALTER TABLE checks the constraint at the time the
alter table is issued since the constraint must be satisified by the
current data. Right now that check is basically run the trigger for each
row checking it, which is probably sub-optimal since it could be one
statement, but changing that won't prevent it from being slow on big
tables.