Thread: Questions on RI spec (poss. bugs)

There's a message on -general about a possible
problem in the deferred RI constraints.  He was doing a
sequence like:
begindelete insert
end
and having it fail even though the deleted key was back in
place at the end.
My understanding of the spec is that that sequence should
have succeeded, but I could very well be wrong.  Changing the 
noaction check to fix this is probably fairly minimal (making
sure that there isn't now a key with the old value before checking
for violated rows would probably be sufficient for match full and
unspecified).  And I guess technically this could happen for
immediate constraints as well if a single update changed a key to
a new value and another to the old one so the constraint was still
satisifed.
But, this brings up a question for the referential actions.
It doesn't look like the actions are limited to whether or not the
row would be violating, but instead based on what row it was associated
with before.  (Makes sense, you'd want a cascade update to keep
the same associations).  But that made me wonder about exactly 
*when* the actions were supposed to take place for deferred constraints.
You could say at check time, but that doesn't make sense for RESTRICT
really, and restrict doesn't have any special wording I see in its
definition. So if you had a deferred on delete cascade constraint, and you
do begin; delete from pk; select * from fk; end;  do you see the fk rows
that were associated with the deleted pk rows?

Stephan Szabo wrote:
>
>    There's a message on -general about a possible
> problem in the deferred RI constraints.  He was doing a
> sequence like:
> begin
>  delete
>  insert
> end
> and having it fail even though the deleted key was back in
> place at the end.
   Isn't  that  (delete  and  reinsert  the  same  PK)  what the   standard means with "triggered data change
violation"?
   It is a second touching of a unique matching PK. And in  this   case the standard doesn't define a behaviour,
insteadit says   you cannot do so.
 
   In the case of reinserting a deleted PK, does the new PK  row   inherit the references to the old PK row? If so, an
ONDELETE   CASCADE must be suppressed - no?
 
   If I'm right that it  should  be  a  "triggered  data  change   violation",  the  problem  is  just changing into
onewe have   with delete/reinsert in the ON DELETE CASCADE  case.  Haven't   tested, but the current implementation
shouldn'tdetect it.
 


Jan


--

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

Jan Wieck writes:

> Stephan Szabo wrote:
> >
> >    There's a message on -general about a possible
> > problem in the deferred RI constraints.  He was doing a
> > sequence like:
> > begin
> >  delete
> >  insert
> > end
> > and having it fail even though the deleted key was back in
> > place at the end.
> 
>     Isn't  that  (delete  and  reinsert  the  same  PK)  what the
>     standard means with "triggered data change violation"?

Triggered data change violations can only occur if the same attribute is
changed twice during the same *statement*, not transaction.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Peter Eisentraut wrote:
> 
> Jan Wieck writes:
> 
> > Stephan Szabo wrote:
> > >
> > >    There's a message on -general about a possible
> > > problem in the deferred RI constraints.  He was doing a
> > > sequence like:
> > > begin
> > >  delete
> > >  insert
> > > end
> > > and having it fail even though the deleted key was back in
> > > place at the end.
> >
> >     Isn't  that  (delete  and  reinsert  the  same  PK)  what the
> >     standard means with "triggered data change violation"?
> 
> Triggered data change violations can only occur if the same attribute is
> changed twice during the same *statement*, not transaction.
>
Do we also get "Triggered data change violations" when we delete and
then 
insert on the FK side in a single transaction ?

I just had to remove a FK constraint because I could not figure ot where 
the violation was coming from ;(

-----------------
Hannu

On Tue, 21 Nov 2000, Jan Wieck wrote:

> Stephan Szabo wrote:
> >
> >    There's a message on -general about a possible
> > problem in the deferred RI constraints.  He was doing a
> > sequence like:
> > begin
> >  delete
> >  insert
> > end
> > and having it fail even though the deleted key was back in
> > place at the end.
> 
>     Isn't  that  (delete  and  reinsert  the  same  PK)  what the
>     standard means with "triggered data change violation"?
> 
>     It is a second touching of a unique matching PK. And in  this
>     case the standard doesn't define a behaviour, instead it says
>     you cannot do so.

As Peter said, it really looks like the 99 draft anyway means twice in a
single statement not transaction which is probably there to prevent
infinite loops. 

>     In the case of reinserting a deleted PK, does the new PK  row
>     inherit the references to the old PK row? If so, an ON DELETE
>     CASCADE must be suppressed - no?
I'm not sure because it's unclear to me whether ri actions are actually
deferred.  Restrict for example sounds like it occurs immediately on the
statement and it's not worded differently from others in the draft I have.
So, it's possible that the actions are supposed to occur immediately on
the statement, even if the constraint check is deferred.  I really don't
know, but it would explain a behavioral difference between restrict and
noaction that makes having both make sense (restrict prevents you from 
moving away - no action lets you move away as long as the constraint is
okay at check time).