Rory Campbell-Lange <rory@campbell-lange.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