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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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