Re: AW: Proposal: More flexible backup/restore via pg_dump

I think my previous message went dead...

> >This will happen for check constraints, but not for foreign key
> >constraints...
> >It actually adds the fk constraints later with CREATE CONSTRAINT TRIGGER
> >after the data dump is finished. And, if you do separate schema and data
> >dumps, the wierd statements at the top and bottom of the data dump turn
> >off triggers and then turn them on again (in the most painful way
possible).
>
> Thanks for this information!
>
> I had not seen those statements before; I have been mistakenly modifying
> 6.5.3 sources, not 7.0.2. I will incorporate them in my work. Is there any
> way of also disabling all constraint checking while loading the data?

Well, for unique you could remove/recreate the unique index. NOT NULL is
probably
not worth bothering with. Check constraints might be able to be turned off
for a table
by setting relchecks to 0 in the pg_class row and the resetting it after
data is loaded (sort
of like what we do on data only dumps for triggers).

The problem is that the create constraint trigger, playing with reltriggers
and playing with
relchecks doesn't guarantee that the data being loaded is correct. And if
you remove
and recreate a unique index, you might not get the index back at the end,
and then
you've lost the information that there was supposed to be a unique or
primary key on
the table.

It might be a good idea to have some sort of pg_constraint (or whatever)
that holds
this data, since that would also make it easier to make the constraint
naming SQL compliant
(no duplicate constraint names within schema - that includes automatically
generated ones),
and it might help if we ever try to make deferrable check/primary key/etc...