
While looking at what needs to be done with some
of the referential actions to make them work
better under deferred constraints, I noticed something
which I think is a bug.

sszabo=> create table base (a int unique);
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'base_a_key' for
table 'base'
CREATE
sszabo=> create table deriv (a int references base(a) on update cascade, b
int);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
sszabo=> drop index base_a_key;
DROP
/* Note: the reason I drop the unique index is because
of the brokenness of our unique constraint for the a=a+1
update below, not because I don't want the constraint. */
sszabo=> insert into base values (2);
INSERT 783232 1
sszabo=> insert into base values (3);
INSERT 783233 1
sszabo=> insert into deriv values (2,1);
INSERT 783234 1
sszabo=> insert into deriv values (3,1);
INSERT 783235 1
sszabo=> update base set a=a+1;
UPDATE 2
sszabo=> select * from deriv;
 a | b 
---+---
 4 | 1
 4 | 1
(2 rows)

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. :)


Wed, 1 Aug 2001 10:10:54 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Problem with FK referential actions
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.

    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 :-)


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Wed, 1 Aug 2001 13:48:22 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
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.  



Wed, 1 Aug 2001 12:10:14 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Problem with FK referential actions