Thread: pg_dump and ALTER TABLE / ADD FOREIGN KEY

With the pg_depend / pg_constraint implementation foreign keys are
applied to dumps via alter table / add foreign key (retains inter
table dependencies).

Some have expressed that this could be quite slow for large databases,
and want a type of:

SET CONSTRAINTS UNCHECKED;

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?

Or would the below be more appropriate?:
ALTER TABLE tab ADD FOREIGN KEY .... TRUST EXISTING DATA;

That is, it will not check pre-existing data to ensure it's proper.
The assumption being that pg_dump came from an already consistent
database.  Needs better wording.

--
Rod

> 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.

> Some have expressed that this could be quite slow for large databases,
> and want a type of:
>
> SET CONSTRAINTS UNCHECKED;
>
> However, others don't believe constraints other than foreign keys
> should go unchecked.

Well, at the moment remember taht all that other SET CONSTRAINTS commands
only affect foreign keys.  However, this is a TODO to allow deferrable
unique constraints.

> Or would the below be more appropriate?:
> ALTER TABLE tab ADD FOREIGN KEY .... TRUST EXISTING DATA;

Maybe instead of TRUST EXISTING DATA, it could be just be WITHOUT CHECK or
something that uses existing keywords?

Either way, it must be a superuser-only command.  I'm kinda beginning to
favour the latter now actually...

Except if we could make all constraints uncheckable, then restoring a dump
would be really fast (but risky!)

Chris

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.

On 2002.06.23 01:23 Christopher Kings-Lynne wrote:
> > Some have expressed that this could be quite slow for large
> databases,
> > and want a type of:
> >
> > SET CONSTRAINTS UNCHECKED;
> >
> > However, others don't believe constraints other than foreign keys
> > should go unchecked.
> 
> Well, at the moment remember taht all that other SET CONSTRAINTS
> commands
> only affect foreign keys.  However, this is a TODO to allow deferrable
> unique constraints.
> 
> > Or would the below be more appropriate?:
> > ALTER TABLE tab ADD FOREIGN KEY .... TRUST EXISTING DATA;
> 
> Maybe instead of TRUST EXISTING DATA, it could be just be WITHOUT
> CHECK or
> something that uses existing keywords?

WITHOUT CHECK doesn't sound right.   'Make a foreign key but don't 
enforce it'.

WITHOUT BACKCHECKING, WITHOUT ENFORCING CURRENT, ...

Anyway you look at it it's going to further break loading pgsql backups 
into another database.  Atleast the set constraints line will be 
errored out on most other DBs -- but the foreign key will still be 
created.

SET FKEY_CONSTRAINTS TO UNCHECKED;

> Except if we could make all constraints uncheckable, then restoring a
> dump
> would be really fast (but risky!)

No more risky than simply avoiding foreign key constraints.  A unique 
key is a simple matter to fix usually, foreign keys are not so easy 
when you get into the double / triple keys