Re: Integrity problem on 7.3.4

#1

Rory Campbell-Lange <rory (AT) campbell-lange (DOT) net> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Integrity problem on 7.3.4

comp.databases.postgresql.novice comp.databases.postgresql.novice

Re: Integrity problem on 7.3.4 - 01-06-2004 , 12:00 PM