Skip site navigation (1) Skip section navigation (2)

Re: bad data with Foreign Key constraint

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Lee Wu" <Lwu(at)mxlogic(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: bad data with Foreign Key constraint
Date: 2004-06-18 23:36:17
Message-ID: 4699.1087601777@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
"Lee Wu" <Lwu(at)mxlogic(dot)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

In response to

pgsql-admin by date

Next:From: Chris FjellDate: 2004-06-18 23:42:18
Subject: restoring a database from a file copy
Previous:From: Elielson FontaneziDate: 2004-06-18 23:16:36
Subject: mssql and postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group