Re: Problem with FK referential actions

On Wed, 1 Aug 2001, Jan Wieck wrote:

> Stephan Szabo wrote:
> > The output from the select, should I believe be (3,1), (4,1)
> > not (4,1), (4,1). I think we're violating General Rule 4 (I think
> > that's it) on the referential constraint definition ("For every
> > row of the referenced table, its matching rows, unique matching
> > rows, and non-unique matching rows are determined immediately
> > before the execution of any SQL-statement. No new matching
> > rows are added during the execution of that SQL-statement.")
> > because when the update cascade gets done for the 2 row, we've
> > changed the (2,1) to (3,1) which then gets hit by the update
> > cascade on the 3 row.
> >
> > I was wondering if you had any thoughts on an easy way around
> > it within what we have. :)
>
> I think you're right in that it is a bug and where the
> problem is. Now to get around it isn't easy. Especially in
> the deferred constraint area, it is important that the
> triggers see the changes made during all commands. But for
> the cascade to hit the right rows only, the scans (done with
> key qualification) would have to be done with a scan command
> counter equal to the original queries command counter.

I was afraid you were going to say something like that (basically
travelling short periods backwards in time). :( Is this something
that already can be done or would it require new support structure?

Also, I'm unconvinced that referential actions on deferred constraints
actually defer, or at least the rows they act on don't defer (excepting
no action of course) given general rule 4, unless the statement
it's referring to is the commit, but then general rule 5 (for example)
doesn't make sense since the pk rows aren't marked for deletion during
the commit (unless I'm really missing something).

> The old (more buggy?) behaviour should've been this annoying
> "triggered data change violation". But some folks thought
> it'd be a good idea to rip out that bug. See, these are the
> days when you miss the old bugs :-)

:) Actually, I think that technically what we're doing actually
is a triggered data change violation as well, since it's within
one statement.