Re: Referential Integrity - Mailing list pgsql-sql

<p><tt><font size="2">Tom,</font></tt><p><tt><font size="2">Thanks for your reply. I did find some correspondance
betweenStephan Szabo <a href="mailto:sszabo@megazone23.bigpanda.com">mailto:sszabo@megazone23.bigpanda.com</a> and Jan
Wieck<a href="mailto:janwieck@Yahoo.com">mailto:janwieck@Yahoo.com</a> entitled "Subject: Re: Questions on RI spec
(poss.bugs)".</font></tt><p><tt><font size="2">I can see that if the delete statement called a function which deleted
thesame record then it might cause problems. It does however seem to me that if a constraint is not deferred then the
statementexecutor should see results of that command, otherwise the executor has lost consistency in the transaction.
Thedocs do say "A constraint that is not deferrable will be checked immediately after every command." But, I guess it
isa question of, having checked the constraint - when to fire the "on cascade delete" trigger? I am not sure exactly
whatSQL99 says, but the way it works seems wrong to me.</font></tt><p><tt><font size="2">I guess I could rewrite the
codeto update the relevant records instead of deleting and re-inserting - but then this becomes more complicated. If
youwant to replace a whole lot of related records it is much easier to do one delete and then reinsert
them.</font></tt><p><tt><fontsize="2">Had a look through the FAQs and TODO, but can't see anything related to this
problem.I'll see what replies I get from this before reporting it as a bug.</font></tt><p><tt><font
size="2">Regards,</font></tt><p><tt><fontsize="2">Alex.</font></tt><p><tt><font size="2">-----Original
Message-----</font></tt><br/><tt><font size="2">From: Tom Lane [<a
href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]</font></tt><br/><tt><font size="2">Sent: 10 February 2003
15:10</font></tt><br/><tt><font size="2">To: Alexander Stanier</font></tt><br /><tt><font size="2">Cc:
pgsql-sql@postgresql.org</font></tt><br/><tt><font size="2">Subject: Re: [SQL] Referential Integrity </font></tt><br
/><p><tt><fontsize="2">"Alexander Stanier" <alexander.stanier@egsgroup.com> writes:</font></tt><br /><tt><font
size="2">>[ expected ON DELETE CASCADE doesn't seem to happen in this context: ]</font></tt><p><tt><font
size="2">>CREATE FUNCTION test() RETURNS INT4 AS '</font></tt><br /><tt><font size="2">> DECLARE</font></tt><br
/><tt><fontsize="2">>     v_return INTEGER;</font></tt><br /><tt><font size="2">> BEGIN</font></tt><br
/><tt><fontsize="2">>   DELETE FROM alex</font></tt><br /><tt><font size="2">>         WHERE aid =3D
''1''</font></tt><br/><tt><font size="2">>           AND bid =3D ''1'';</font></tt><p><tt><font size="2">>  
INSERTINTO alex (aid,bid,itemdesc)</font></tt><br /><tt><font size="2">>             VALUES
(''1'',''1'',''OneOne'');</font></tt><p><tt><fontsize="2">>   INSERT INTO alexette (aid,bid,vcode)</font></tt><br
/><tt><fontsize="2">>                 VALUES (''1'',''1'',''V'');</font></tt><p><tt><font size="2">>   RETURN
0;</font></tt><br/><tt><font size="2">> END;</font></tt><br /><tt><font size="2">> ' LANGUAGE
'plpgsql';</font></tt><p><tt><fontsize="2">The cascaded deletes will be implemented at end of statement ---
which</font></tt><br/><tt><font size="2">I believe is always taken to be the end of the current
interactive</font></tt><br/><tt><font size="2">statement, ie, the "SELECT test()" you typed.  So they haven't
happened</font></tt><br/><tt><font size="2">yet at the time the function tries to do the
inserts.</font></tt><p><tt><fontsize="2">This is probably a bug, but IIRC, it's not entirely obvious what to
do</font></tt><br/><tt><font size="2">instead. I seem to recall some inconclusive discussions in
pgsql-hackers</font></tt><br/><tt><font size="2">about designing a better rule for when to fire the RI actions. 
Check</font></tt><br/><tt><font size="2">the list archives for details.</font></tt><p><tt>                        <font
size="2">regards,tom lane</font></tt>