Re: plpgsql update bug?

On Fri, 25 May 2001, Vinod Kurup wrote:

> Hi,
>
> I think I've come across a bug in plpgsql. It happens in the following
> situation:
>
> I have 2 tables, one with a foreign key to the other.
> Inside a plpgsql function, I do:
> update row in table2
> delete that row in table2
> delete the referenced row in table1
>
> And I get a foreign key constraint error. I apologize if that's not clear,
> but hopefully the test case is more explanatory...

Okay, I think I may understand why this occurs. This is a
very similar problem to the defered constraints problem we
have. It doesn't realize that the fk row isn't there anymore
and shouldn't be checked.

My guess is that these statements are all treated as part of
a single statement when put inside the function which is why
they're treated differently than as separate statements in a
transaction.

I'm not sure whether or not this is actually a triggered data change
violation (I don't have a draft of 99 to check right now) as it's
attempting to delete a row that was previously modified in the statement
(assuming that it's treated as a single statement of course). I think the
triggered data change may only apply to updates though.

I think the following checks are needed (at least for the deferred case,
and this case as well). These checks only work for match full and
match unspecified, but we don't support match partial anyway:
On insert/update to fk check, can we see a row exist with the new values?
If not, we don't need to check, it's already been deleted or updated
again in which case we want the later trigger to act.
On delete/update from pk with no action, can we see a row with the old
values?
If so, we don't need to check, anything that succeeded before will
succeed now.

I'm a bit uncertain on the deferred cases with action. The spec is none
too clear about when the actions occur. Although it appears to me
that it's at statement time, not check time since it mentions things
like "marked for deletion" which I believe is a statement level thing
(with said rows deleted at the end of the statement before integrity
checks are applied).