Re: Bug in FOREIGN KEY

Bruce Momjian wrote:
> > Bruce Momjian writes:
> >
> > > ERROR:  triggered data change violation on relation "primarytest2"
> >
> > We're getting this report about once every 48 hours, which would make it a
> > FAQ.  (hint, hint)
> >
>
>
> First time I heard of it.  Does anyone know more details?
   Think  I misinterpreted the SQL3 specs WR to this detail. The   checks must be made per statement,  not  at  the
transaction  level.  I'll  try  to fix it, but we need to define what will   happen with referential actions in the
case of  conflicting   actions on the same key - there are some possible conflicts:
 
   1.  DEFERRED ON DELETE NO ACTION or RESTRICT
       Do  the referencing rows reference to the new PK row with       the  same  key  now,  or  is  this  still  a
constraint      violation?  I  would say it's not, because the constraint       condition is satisfied at the end of
thetransaction. How       do other databases behave?
 
   2.  DEFERRED ON DELETE CASCADE, SET NULL or SET DEFAULT
       Again  I'd  say  that  the  action  should  be suppressed       because a matching PK row is present at
transactionend -       it's  not  the same old row, but the constraint itself is       still satisfied.
 
   Implementing it that way (if it is correct that way) requires   that  the  RI-triggers  check that the key in
questionreally   disappeared from the PK table,  at  least  for  the  deferred   invocation at transaction end. This
lookupis not required in   the immediate case, so it would be  possible  to  retain  the   current  performance  here,
but we'd  need a mechanism that   tells the trigger if it is actually invoked in  immediate  or   deferred mode. Don't
knowhow to do that right now.
 
   To  fix  it now, I'd tend to remove the triggered data change   check in the trigger queue (where the error is
coming from)   and  add  the  extra  PK lookup to the triggers for 7.1. Then   think about the suppress of  it  with
an immediate/deferred   flag mechanism for 7.2.
 


Jan

--

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