From: Tom Lane <tgl@sss.pgh.pa.us>
To: Lee Wu <Lwu@mxlogic.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: bad data with Foreign Key constraint
Date: Fri, 18 Jun 2004 19:36:17 -0400
Message-ID: <4699.1087601777@sss.pgh.pa.us> (raw)
In-Reply-To: <ECAB83AA52BCC043A0E24BBC00001024111265@mxhq-exch.corp.mxlogic.com>
References: <ECAB83AA52BCC043A0E24BBC00001024111265@mxhq-exch.corp.mxlogic.com>
"Lee Wu" <Lwu@mxlogic.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, Lwu@mxlogic.com
Subject: Re: bad data with Foreign Key constraint
In-Reply-To: <4699.1087601777@sss.pgh.pa.us>
References: <ECAB83AA52BCC043A0E24BBC00001024111265@mxhq-exch.corp.mxlogic.com> <4699.1087601777@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox