RI question

Uh oh,

I think deferred RI constraints must only fire the actions
that remain after all commands during the entire transaction
are condensed to the total minimum required to get that
state, because deferred RI must only check what VISIBLY
happened during the transaction.

Thinking on the tuple level, a sequence of
INSERT,UPDATE,UPDATE must fire only one INSERT trigger, but
with the values of the last UPDATE. An UPDATE,DELETE sequence
is in fact a DELETE of the original tuple and an
INSERT,UPDATE,DELETE sequence is nothing.

That means that the recording mechnism of the trigger events
must be very smart on UPDATE and DELETE events, looking at
the x_min of the old tuple if that resulted from the current
transaction. If so, follow the events backward, disable
previous ones and change the new event into what it really
has to be.

But some problems remain unsolvable by this:

- PK has an ON DELETE CASCADE for FK
- BEGIN
- DELETE PK
- INSERT same PK
- COMMIT.

This really shouldn't invoke the cascading delete, because at
COMMIT the PK still is there. Same for a constraint that
forbids deletion of a PK while referenced by FK. Therefore
the deferred event recorder must check on INSERT any previous
DELETES for the same relation if the key does match and drop
both deferred triggers if so. Therefore it needs to know
which attributes build the PK of that relation
(<relname>_pkey guaranteed?).

Well, I think that's finally the death of RI over rules. The
code managing those rules during CREATE/ALTER TABLE would
become totally unmaintainable. And (sorry Vadim) it's the
death of SLT for this too because this event tracking must be
done on the tuple level.

It complicated the trigger approach too, but IMHO not too
bad. Anyway, some co-developer(s) doing the parser- and
utility-statement stuff (SET CONSTRAINTS ... etc.) would be
great.

Volunteers?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #