Re: Foreign keys in pg_dump

On Fri, 2002-09-06 at 01:19, Christopher Kings-Lynne wrote:
> OK,
>
> The argument about using ALTER TABLE/ADD FOREIGN KEY in dumps was that it
> caused an actual check of the data in the table, right? This was going to
> be much slower than using CREATE CONSTRAINT TRIGGER.
>
> So, why can't we do this in the SQL that pg_dump creates (TODO):
>
> CREATE TABLE ...
> ALTER TABLE/ADD FOREIGN KEY ...
> update catalogs and disable triggers that the ADD FOREIGN KEY just created
> ...
> COPY .. FROM ...
> \.
> update catalogs and enable triggers

The problem with this is you may enable a trigger that was disabled by
the user. It cannot be done to all triggers. We could figure out which
triggers were created for the foreign key via pg_depend, then re-enable
only those.

If we did most of this in a single transaction it should be fairly safe.

> Doesn't this give us the best of both worlds? ie. Keeps dependencies but
> does fast COPYing?
>
> Also, I think a new super-user (or owner) only SQL command would be nice
> (TODO):
>
> ALTER TABLE foo {DISABLE|ENABLE} TRIGGER { ALL | trigger_name [ ,... ] };

pg_dump shouldn't need to know that a trigger is involved for foreign
keys. A SET CONSTRAINTS DISABLED would be more appropriate in a binary
mode dump -- but I firmly believe that text mode dumps should run full
checks on the data to ensure the user didn't muck with it.