Thread: Foreign keys in pg_dump

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

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 [ ,... ] };

This is like MSSQL syntax (IIRC):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
aa-az_3ied.asp
Specifies that trigger_name is enabled or disabled. When a trigger is
disabled it is still defined for the table; however, when INSERT, UPDATE, or
DELETE statements are executed against the table, the actions in the trigger
are not performed until the trigger is re-enabled.


It would certainly tidy up the dumps a bit...

Chris

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.