@@ -822,3 +822,171 @@ TIP 5: Have you checked our extensive FAQ?
822822
823823http://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+