pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
bad data with Foreign Key constraint
3+ messages / 2 participants
[nested] [flat]

* bad data with Foreign Key constraint
@ 2004-06-18 22:51  Lee Wu <Lwu@mxlogic.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Lee Wu @ 2004-06-18 22:51 UTC (permalink / raw)
  To: pgsql-admin

Hi All,

 

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?

 

Sample data from PG:

 

select rid from c except select rid from r;

 rid

-------------

     7686396

     7688003

     7906141

     7974816

     8033807

(5 rows)

 

\d c

                                  Table "public.c"

    Column     |           Type           |
Modifiers

---------------+--------------------------+-----------------------------
-----------------------

 cid           | integer                  | not null default
nextval('c_id_seq'::text)

 rid           | integer                  | not null

 others...|

Indexes: c_pkey primary key btree (cid),

         c_rid_idx btree (rid)

Foreign Key constraints: $1 FOREIGN KEY (rid) REFERENCES r(rid) ON
UPDATE NO ACTION ON DELETE CASCADE

 

Thank for your help!

 

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: bad data with Foreign Key constraint
@ 2004-06-18 23:36  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Lee Wu <Lwu@mxlogic.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2004-06-18 23:36 UTC (permalink / raw)
  To: Lee Wu <Lwu@mxlogic.com>; +Cc: pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: bad data with Foreign Key constraint
@ 2004-06-21 18:55  Lee Wu <Lwu@mxlogic.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Lee Wu @ 2004-06-21 18:55 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

Hi Tom,

We do not have rules on the tables. 

I find manipulating PG dictionary can allow violating RI data to enter
database.

Thanks,

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Friday, June 18, 2004 4:36 PM
To: Lee Wu
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] bad data with Foreign Key constraint 

"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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-06-21 18:55 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-18 22:51 bad data with Foreign Key constraint Lee Wu <Lwu@mxlogic.com>
2004-06-18 23:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-21 18:55 Re: bad data with Foreign Key constraint Lee Wu <Lwu@mxlogic.com>

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