X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D692FD1B1B7 for ; Fri, 18 Jun 2004 20:36:28 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63654-05 for ; Fri, 18 Jun 2004 23:36:27 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1DA3ED1B170 for ; Fri, 18 Jun 2004 20:36:23 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5INaHfH004700; Fri, 18 Jun 2004 19:36:17 -0400 (EDT) To: "Lee Wu" Cc: pgsql-admin@postgresql.org Subject: Re: bad data with Foreign Key constraint In-reply-to: References: Comments: In-reply-to "Lee Wu" message dated "Fri, 18 Jun 2004 16:51:27 -0600" Date: Fri, 18 Jun 2004 19:36:17 -0400 Message-ID: <4699.1087601777@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/239 X-Sequence-Number: 13899 "Lee Wu" writes: > I have found that there are some foreign keys in our detail tables while > there are not primary keys in master tables. > Yes, we have FK constraints on detail tables. > I could not think how it can happen. PG (7.3.2) does not have "disable > constraint" like Oracle, does it? Hm, you don't have any weird rules on these tables do you? I see the following in the 7.3.5 commit logs: 2003-10-30 22:57 wieck * src/: backend/utils/adt/ri_triggers.c, test/regress/expected/foreign_key.out, test/regress/sql/foreign_key.sql (REL7_3_STABLE): Fix for possible referential integrity violation when a qualified ON INSERT rule split the query into one INSERT and one UPDATE where the UPDATE then hit's the just created row without modifying the key fields again. In this special case, the new key slipped in totally unchecked. Jan Also, I find the following in 7.3.3: 2003-03-27 09:33 tgl * src/: backend/commands/trigger.c, backend/executor/execMain.c, include/commands/trigger.h (REL7_3_STABLE): GetTupleForTrigger must use outer transaction's command counter for time qual checking, not GetCurrentCommandId. Per test case from Steve Wolfe. I don't recall the implications of this one in detail anymore, but I think it was triggered by updates inside functions. You might want to check the archives from back then to see if what Steve was doing looks anything like stuff your own apps do. regards, tom lane