Re: Integrity problem on 7.3.4

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