Skip to content

Commit 07d89f6

Browse files
bmomjianbmomjian
committed
Add to TODO:
 * With disabled triggers, allow pg_dump to use ALTER TABLE ADD FOREIGN KEY Add to trigger TODO.detail.
1 parent c49b6f4 commit 07d89f6

File tree

1 file changed

+168
-0
lines changed

1 file changed

+168
-0
lines changed

‎doc/TODO.detail/trigger

Lines changed: 168 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -822,3 +822,171 @@ TIP 5: Have you checked our extensive FAQ?
822822

823823
http://www.postgresql.org/users-lounge/docs/faq.html
824824

825+
From pgsql-hackers-owner+M28358@postgresql.org Fri Sep 6 01:19:36 2002
826+
Return-path: <pgsql-hackers-owner+M28358@postgresql.org>
827+
Received: from postgresql.org (postgresql.org [64.49.215.8])
828+
by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g865JY225103
829+
for <pgman@candle.pha.pa.us>; Fri, 6 Sep 2002 01:19:35 -0400 (EDT)
830+
Received: from localhost (postgresql.org [64.49.215.8])
831+
by postgresql.org (Postfix) with ESMTP
832+
id 15A1C475B47; Fri, 6 Sep 2002 01:19:37 -0400 (EDT)
833+
Received: from postgresql.org (postgresql.org [64.49.215.8])
834+
by postgresql.org (Postfix) with SMTP
835+
id 5D8C9475FC5; Fri, 6 Sep 2002 01:19:33 -0400 (EDT)
836+
Received: from localhost (postgresql.org [64.49.215.8])
837+
by postgresql.org (Postfix) with ESMTP id 50F2C475E88
838+
for <pgsql-hackers@postgresql.org>; Fri, 6 Sep 2002 01:19:29 -0400 (EDT)
839+
Received: from houston.familyhealth.com.au (unknown [203.59.48.253])
840+
by postgresql.org (Postfix) with ESMTP id 633FA4759E8
841+
for <pgsql-hackers@postgresql.org>; Fri, 6 Sep 2002 01:19:27 -0400 (EDT)
842+
Received: (from root@localhost)
843+
by houston.familyhealth.com.au (8.11.6/8.11.6) id g865JQh24183
844+
for pgsql-hackers@postgresql.org; Fri, 6 Sep 2002 13:19:26 +0800 (WST)
845+
(envelope-from chriskl@familyhealth.com.au)
846+
Received: from mariner (mariner.internal [192.168.0.101])
847+
by houston.familyhealth.com.au (8.11.6/8.9.3) with SMTP id g865JPk24139
848+
for <pgsql-hackers@postgresql.org>; Fri, 6 Sep 2002 13:19:25 +0800 (WST)
849+
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
850+
To: "Hackers" <pgsql-hackers@postgresql.org>
851+
Subject: [HACKERS] Foreign keys in pg_dump
852+
Date: Fri, 6 Sep 2002 13:19:44 +0800
853+
Message-ID: <GNELIHDDFBOCMGBFGEFOKEBMCEAA.chriskl@familyhealth.com.au>
854+
MIME-Version: 1.0
855+
Content-Type: text/plain;
856+
charset="iso-8859-1"
857+
Content-Transfer-Encoding: 7bit
858+
X-Priority: 3 (Normal)
859+
X-MSMail-Priority: Normal
860+
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
861+
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
862+
Importance: Normal
863+
X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/)
864+
X-Virus-Scanned: by AMaViS new-20020517
865+
Precedence: bulk
866+
Sender: pgsql-hackers-owner@postgresql.org
867+
X-Virus-Scanned: by AMaViS new-20020517
868+
Status: OR
869+
870+
OK,
871+
872+
The argument about using ALTER TABLE/ADD FOREIGN KEY in dumps was that it
873+
caused an actual check of the data in the table, right? This was going to
874+
be much slower than using CREATE CONSTRAINT TRIGGER.
875+
876+
So, why can't we do this in the SQL that pg_dump creates (TODO):
877+
878+
CREATE TABLE ...
879+
ALTER TABLE/ADD FOREIGN KEY ...
880+
update catalogs and disable triggers that the ADD FOREIGN KEY just created
881+
...
882+
COPY .. FROM ...
883+
\.
884+
update catalogs and enable triggers
885+
886+
Doesn't this give us the best of both worlds? ie. Keeps dependencies but
887+
does fast COPYing?
888+
889+
Also, I think a new super-user (or owner) only SQL command would be nice
890+
(TODO):
891+
892+
ALTER TABLE foo {DISABLE|ENABLE} TRIGGER { ALL | trigger_name [ ,... ] };
893+
894+
This is like MSSQL syntax (IIRC):
895+
896+
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
897+
aa-az_3ied.asp
898+
Specifies that trigger_name is enabled or disabled. When a trigger is
899+
disabled it is still defined for the table; however, when INSERT, UPDATE, or
900+
DELETE statements are executed against the table, the actions in the trigger
901+
are not performed until the trigger is re-enabled.
902+
903+
904+
It would certainly tidy up the dumps a bit...
905+
906+
Chris
907+
908+
909+
---------------------------(end of broadcast)---------------------------
910+
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
911+
912+
From pgsql-hackers-owner+M28381@postgresql.org Fri Sep 6 09:34:27 2002
913+
Return-path: <pgsql-hackers-owner+M28381@postgresql.org>
914+
Received: from postgresql.org (postgresql.org [64.49.215.8])
915+
by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g86DYQ201524
916+
for <pgman@candle.pha.pa.us>; Fri, 6 Sep 2002 09:34:26 -0400 (EDT)
917+
Received: from localhost (postgresql.org [64.49.215.8])
918+
by postgresql.org (Postfix) with ESMTP
919+
id C0CA0476E5C; Fri, 6 Sep 2002 09:34:19 -0400 (EDT)
920+
Received: from postgresql.org (postgresql.org [64.49.215.8])
921+
by postgresql.org (Postfix) with SMTP
922+
id C788C476A92; Fri, 6 Sep 2002 09:34:16 -0400 (EDT)
923+
Received: from localhost (postgresql.org [64.49.215.8])
924+
by postgresql.org (Postfix) with ESMTP id 5CD18475EF0
925+
for <pgsql-hackers@postgresql.org>; Fri, 6 Sep 2002 09:34:12 -0400 (EDT)
926+
Received: from squire.barchord.com (squire.barchord.com [216.194.67.18])
927+
by postgresql.org (Postfix) with ESMTP id 2A0AB476EAE
928+
for <pgsql-hackers@postgresql.org>; Fri, 6 Sep 2002 09:34:11 -0400 (EDT)
929+
Received: from [10.0.2.49] (nat.inquent.com [216.6.14.45])
930+
by squire.barchord.com (Postfix) with ESMTP
931+
id D4B60415; Fri, 6 Sep 2002 09:34:14 -0400 (EDT)
932+
Subject: Re: [HACKERS] Foreign keys in pg_dump
933+
From: Rod Taylor <rbt@zort.ca>
934+
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
935+
cc: Hackers <pgsql-hackers@postgresql.org>
936+
In-Reply-To: <GNELIHDDFBOCMGBFGEFOKEBMCEAA.chriskl@familyhealth.com.au>
937+
References: <GNELIHDDFBOCMGBFGEFOKEBMCEAA.chriskl@familyhealth.com.au>
938+
Content-Type: text/plain
939+
Content-Transfer-Encoding: 7bit
940+
X-Mailer: Ximian Evolution 1.0.8
941+
Date: 06 Sep 2002 09:34:21 -0400
942+
Message-ID: <1031319261.3555.9.camel@jester>
943+
MIME-Version: 1.0
944+
X-Virus-Scanned: by AMaViS new-20020517
945+
Precedence: bulk
946+
Sender: pgsql-hackers-owner@postgresql.org
947+
X-Virus-Scanned: by AMaViS new-20020517
948+
Status: ORr
949+
950+
On Fri, 2002-09-06 at 01:19, Christopher Kings-Lynne wrote:
951+
> OK,
952+
>
953+
> The argument about using ALTER TABLE/ADD FOREIGN KEY in dumps was that it
954+
> caused an actual check of the data in the table, right? This was going to
955+
> be much slower than using CREATE CONSTRAINT TRIGGER.
956+
>
957+
> So, why can't we do this in the SQL that pg_dump creates (TODO):
958+
>
959+
> CREATE TABLE ...
960+
> ALTER TABLE/ADD FOREIGN KEY ...
961+
> update catalogs and disable triggers that the ADD FOREIGN KEY just created
962+
> ...
963+
> COPY .. FROM ...
964+
> \.
965+
> update catalogs and enable triggers
966+
967+
The problem with this is you may enable a trigger that was disabled by
968+
the user. It cannot be done to all triggers. We could figure out which
969+
triggers were created for the foreign key via pg_depend, then re-enable
970+
only those.
971+
972+
If we did most of this in a single transaction it should be fairly safe.
973+
974+
> Doesn't this give us the best of both worlds? ie. Keeps dependencies but
975+
> does fast COPYing?
976+
>
977+
> Also, I think a new super-user (or owner) only SQL command would be nice
978+
> (TODO):
979+
>
980+
> ALTER TABLE foo {DISABLE|ENABLE} TRIGGER { ALL | trigger_name [ ,... ] };
981+
982+
pg_dump shouldn't need to know that a trigger is involved for foreign
983+
keys. A SET CONSTRAINTS DISABLED would be more appropriate in a binary
984+
mode dump -- but I firmly believe that text mode dumps should run full
985+
checks on the data to ensure the user didn't muck with it.
986+
987+
988+
989+
990+
---------------------------(end of broadcast)---------------------------
991+
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
992+

0 commit comments

Comments
 (0)