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 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 #