Re: Apparent referential integrity bug in PL/pgSQL

On Fri, 18 Oct 2002, Brian Blaha wrote:

> I have a function that operates on two tables A and B, such that B has a
> foreign key on A, as follows:
> INSERT INTO A (...) several times
> INSERT INTO B (...) several times, with foreign keys pointing to the new
> members of A
> DELETE FROM A (...), possibly including some of the newly added members
>
> Even though B's foreign key is defined ON DELETE CASCADE, I get a
> referential integrity
> violation when I run this function. If instead, I comment out the DELETE
> statement, start a
> transaction block, run the function, run the DELETE statement, and end
> the transaction, no
> errors occur. To run those statements with one function call, I need to
> split the INSERTs and
> DELETEs into separate functions, and call them separately from a third
> function. I am using
> version 7.2.2. Has this been corrected in the beta versions or can
> someone confirm this for me?

No it hasn't, but I see what's happening. The rows from b are being
checked before they are deleted by the trigger that runs after the check.
I think that to do this case right, all of the ref actions would need
to be done before any of the noaction or check triggers. This is
technically what the spec says to do afaict, but I don't think we'd seen a
case before where it matters.