Re: Bug in FOREIGN KEY - Mailing list pgsql-hackers

Here is another bug:

test=> begin;
BEGIN
test=> INSERT INTO primarytest2 VALUES (5,5);
INSERT 18757 1
test=> UPDATE primarytest2 SET col2=1 WHERE col1 = 5 AND col2 = 5;
ERROR:  deferredTriggerGetPreviousEvent: event for tuple (0,10) not
found

> 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 the transaction. 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 transaction end -
>         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 question really
>     disappeared from the PK table,  at  least  for  the  deferred
>     invocation at transaction end. This lookup is 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 know how 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 #
> 
> 
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026