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

Re: Integrity problem on 7.3.4

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
Cc: Postgresql Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Integrity problem on 7.3.4
Date: 2004-01-06 18:00:31
Message-ID: 17544.1073412031@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Rory Campbell-Lange <rory(at)campbell-lange(dot)net> writes:
> I have a table 'pages' with a foreign key constraint on another table,
> 'photo'. The idea is that one should not be able to insert a
> non-existant photo into a page. If I try to do this from the pgsql
> prompt the insert fails as it should. However if I run a function, one
> is able to add a row with an arbitrary n_photo_id (I discovered this
> after I added this test arbitrarily to my unit test regime).

Since the function contains an UPDATE, I suspect this may be the same
problem fixed here:

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

Please update to 7.3.5 and let us know if you still see the problem.

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Martin HamplDate: 2004-01-07 12:08:59
Subject: partial index on varchar-coloumn in 7.4.1
Previous:From: Bruce MomjianDate: 2004-01-06 17:56:34
Subject: Re: Disk usage

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