Re: Bug in FOREIGN KEY

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(at)Yahoo(dot)com #
>
>
>

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