Re: Referential Integrity

Tom,

Thanks for your reply. I did find some correspondance between Stephan
Szabo mailto:sszabo(at)megazone23(dot)bigpanda(dot)com and Jan Wieck
mailto:janwieck(at)Yahoo(dot)com entitled "Subject: Re: Questions on RI spec
(poss. bugs)".

I can see that if the delete statement called a function which deleted
the same record then it might cause problems. It does however seem to me
that if a constraint is not deferred then the statement executor should
see results of that command, otherwise the executor has lost consistency
in the transaction. The docs do say "A constraint that is not deferrable
will be checked immediately after every command." But, I guess it is a
question of, having checked the constraint - when to fire the "on
cascade delete" trigger? I am not sure exactly what SQL99 says, but the
way it works seems wrong to me.

I guess I could rewrite the code to update the relevant records instead
of deleting and re-inserting - but then this becomes more complicated.
If you want to replace a whole lot of related records it is much easier
to do one delete and then reinsert them.

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.

Regards,

Alex.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 10 February 2003 15:10
To: Alexander Stanier
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Referential Integrity

"Alexander Stanier" <alexander(dot)stanier(at)egsgroup(dot)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