Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 24BBA2E2DF1 for ; Sat, 2 Feb 2008 16:37:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 01812-01 for ; Sat, 2 Feb 2008 16:37:30 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 07C8B2E2DE4 for ; Sat, 2 Feb 2008 16:37:33 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m12KaFXH010017; Sat, 2 Feb 2008 15:36:15 -0500 (EST) To: "Peter Koczan" cc: "Alvaro Herrera" , pgsql-admin@postgresql.org Subject: Re: Legacy foreign keys In-reply-to: <4544e0330802021143n2d38dce8k4ff9ab271aa35c2a@mail.gmail.com> References: <4544e0330712101112r6aeccfcckef378a7ea1795e07@mail.gmail.com> <20427.1197324492@sss.pgh.pa.us> <4544e0330712111901k1fa50c5dgb26287aff018e98c@mail.gmail.com> <10859.1197428770@sss.pgh.pa.us> <4544e0330712120940i413e3ad6y867fc2a8744f623b@mail.gmail.com> <4544e0330801311613t5cc6741cx1464947c2215f198@mail.gmail.com> <18279.1201845349@sss.pgh.pa.us> <4544e0330802011030l477b0d8ek57909e75ff367128@mail.gmail.com> <4544e0330802011539j78ab6d2eofcf58e9442031115@mail.gmail.com> <17999.1201909980@sss.pgh.pa.us> <4544e0330802021143n2d38dce8k4ff9ab271aa35c2a@mail.gmail.com> Comments: In-reply-to "Peter Koczan" message dated "Sat, 02 Feb 2008 13:43:15 -0600" Date: Sat, 02 Feb 2008 15:36:15 -0500 Message-ID: <10016.1201984575@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/8 X-Sequence-Number: 28082 "Peter Koczan" writes: >> If your original database doesn't have all three triggers, then you had >> a problem already --- the FK constraint wasn't being enforced properly. > Ah, these were from before I became the main database admin. It was > probably some previous admin or programmer who thought he could be > clever and drop the other FK-related triggers. Or it could just be a long-ago oversight. In versions before about 7.3, it was possible to drop and recreate one of the two tables involved in an FK relationship without any impact on the other table's FK trigger(s). Depending on usage patterns you might never notice that the FK constraint was now only partially enforced. Hmm, actually the note in the code says that there were two separate bad things that had to happen to get into this situation: * The conversion is complex because a pre-7.3 foreign key involved three * separate triggers, which were reported separately in dumps. While the * single trigger on the referencing table adds no new information, we need * to know the trigger functions of both of the triggers on the referenced * table to build the constraint declaration. Also, due to lack of proper * dependency checking pre-7.3, it is possible that the source database had * an incomplete set of triggers resulting in an only partially enforced * FK constraint. (This would happen if one of the tables had been dropped * and re-created, but only if the DB had been affected by a 7.0 pg_dump bug * that caused loss of tgconstrrelid information.) regards, tom lane