Thread: Referential Integrity
<p><font face="Comic Sans MS" size="2">Dear pgsql-sql,</font><p><font face="Comic Sans MS" size="2">(Third time of
tryingto post this message - apologies if any previous attempts subsequently succeed!! Ignore them this one states the
casemore clearly)</font><p><font face="Comic Sans MS" size="2">I have the following scenario in
pg7.3.1.:</font><p><fontface="Courier New" size="2">CREATE TABLE alex (aid varchar(40) NOT NULL,</font><br /><font
face="CourierNew" size="2"> bid varchar(100) NOT NULL,</font><br /><font face="Courier New"
size="2"> itemdesc varchar(100),</font><br /><font face="Courier New" size="2"> CONSTRAINT
alex_pkeyPRIMARY KEY (aid, bid));</font><p><font face="Courier New" size="2">CREATE TABLE alexette (aid varchar(40) NOT
NULL,</font><br/><font face="Courier New" size="2"> bid varchar(100) NOT NULL,</font><br /><font
face="CourierNew" size="2"> vcode varchar(1),</font><br /><font face="Courier New"
size="2"> CONSTRAINT alexette_pkey PRIMARY KEY (aid, bid));</font><p><font face="Courier New" size="2">ALTER
TABLEalexette </font><br /><font face="Courier New" size="2">ADD CONSTRAINT alexette_fk_1 FOREIGN KEY
(aid,bid)</font><br/><font face="Courier New" size="2"> REFERENCES alex (aid,bid)</font><br /><font face="Courier
New"size="2"> ON DELETE CASCADE;</font><p><font face="Courier New" size="2">INSERT INTO alex VALUES
('1','1','OneOne');</font><p><fontface="Courier New" size="2">INSERT INTO alexette VALUES ('1','1','V');</font><p><font
face="CourierNew" size="2">CREATE FUNCTION test() RETURNS INT4 AS '</font><br /><font face="Courier New"
size="2">DECLARE</font><br/><font face="Courier New" size="2"> v_return INTEGER;</font><br /><font face="Courier
New"size="2">BEGIN</font><br /><font face="Courier New" size="2"> DELETE FROM alex</font><br /><font face="Courier
New"size="2"> WHERE aid = ''1''</font><br /><font face="Courier New" size="2"> AND bid =
''1'';</font><p><fontface="Courier New" size="2"> INSERT INTO alex (aid,bid,itemdesc)</font><br /><font face="Courier
New"size="2"> VALUES (''1'',''1'',''OneOne'');</font><p><font face="Courier New" size="2"> INSERT INTO
alexette(aid,bid,vcode)</font><br /><font face="Courier New" size="2"> VALUES
(''1'',''1'',''V'');</font><p><fontface="Courier New" size="2"> RETURN 0;</font><br /><font face="Courier New"
size="2">END;</font><br/><font face="Courier New" size="2">' LANGUAGE 'plpgsql';</font><br /><p><font face="Comic Sans
MS"size="2">Please could someone tell me why running the function test() causes the following error:</font><p><span
lang="en-gb"><fontface="Comic Sans MS" size="2">"Cannot insert a duplicate key into unique index
alexette_pkey</font><fontface="Times New Roman">"</font></span><p><span lang="en-us"><font face="Comic Sans MS"
size="2">Theforeign key with "on delete cascade" should ensure that the delete statement within this function should
deletethe records from both alex and alexette. Indeed a simple "delete from alex" demonstrates that this does indeed
work.However, it appears that within the function this is not happening or that the insert statements cannot see the
fullextent of the changes made by the delete statement. Whichever, surely this is wrong? Or am I missing
something?</font></span><p><spanlang="en-us"><font face="Comic Sans MS" size="2">Regards,</font></span><p><span
lang="en-us"><fontface="Arial" size="2">Alexander Stanier</font></span><span lang="en-gb"></span><span
lang="en-gb"><fontface="Arial" size="2">.</font></span><span lang="en-us"></span><span lang="en-us"></span><br /><span
lang="en-gb"><fontface="Arial" size="2"><a href="mailto:alex@egsgroup.com">mailto:alex@egsgroup.com</a></font></span>
"Alexander Stanier" <alexander.stanier@egsgroup.com> writes:
> [ expected ON DELETE CASCADE doesn't seem to happen in this context: ]
> CREATE FUNCTION test() RETURNS INT4 AS '
> DECLARE
> v_return INTEGER;
> BEGIN
> DELETE FROM alex
> WHERE aid =3D ''1''
> AND bid =3D ''1'';
> INSERT INTO alex (aid,bid,itemdesc)
> VALUES (''1'',''1'',''OneOne'');
> INSERT INTO alexette (aid,bid,vcode)
> VALUES (''1'',''1'',''V'');
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql';
The cascaded deletes will be implemented at end of statement --- which
I believe is always taken to be the end of the current interactive
statement, ie, the "SELECT test()" you typed. So they haven't happened
yet at the time the function tries to do the inserts.
This is probably a bug, but IIRC, it's not entirely obvious what to do
instead. I seem to recall some inconclusive discussions in pgsql-hackers
about designing a better rule for when to fire the RI actions. Check
the list archives for details.
regards, tom lane
On Mon, 10 Feb 2003, Tom Lane wrote:
> "Alexander Stanier" <alexander.stanier@egsgroup.com> writes:
> > [ expected ON DELETE CASCADE doesn't seem to happen in this context: ]
>
> > CREATE FUNCTION test() RETURNS INT4 AS '
> > DECLARE
> > v_return INTEGER;
> > BEGIN
> > DELETE FROM alex
> > WHERE aid =3D ''1''
> > AND bid =3D ''1'';
>
> > INSERT INTO alex (aid,bid,itemdesc)
> > VALUES (''1'',''1'',''OneOne'');
>
> > INSERT INTO alexette (aid,bid,vcode)
> > VALUES (''1'',''1'',''V'');
>
> > RETURN 0;
> > END;
> > ' LANGUAGE 'plpgsql';
>
> The cascaded deletes will be implemented at end of statement --- which
> I believe is always taken to be the end of the current interactive
> statement, ie, the "SELECT test()" you typed. So they haven't happened
> yet at the time the function tries to do the inserts.
>
> This is probably a bug, but IIRC, it's not entirely obvious what to do
> instead. I seem to recall some inconclusive discussions in pgsql-hackers
> about designing a better rule for when to fire the RI actions. Check
> the list archives for details.
Yeah, I don't think we came to a conclusion. Looking at SQL99, the
"correct" behavior seems to be that the actions occur as part of the
actual delete or update action so that effectively at the "same" point in
time the dependant rows are changed, using (I think) 14.14 and 14.20 as a
guide. This is significantly different from what we do AFAICT though. :(
(For example, I'm not sure if 14.14 implies that after triggers from the
statement inside the function to occur as part of that statement rather
than after the function ends)
<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>