Re: RI oddness

Max Khon wrote:
> hi, there!
>
> On Mon, 23 Apr 2001, Jan Wieck wrote:
>
> > I just got trapped by one of my own features in the
> > referential integrity area.
> >
> > The problem is, that the trigger run on the FK row at UPDATE
> > allways checks and locks the referenced PK, even if the FK
> > attributes didn't change. That's because if there'd be an ON
> > DELETE SET DEFAULTS and someone deletes a PK consisting of
> > all the FK's column defaults, we wouldn't notice and let it
> > pass through.
> >
> > The bad thing on it is now, if I have one XACT that locks the
> > PK row first, then locks the FK row, and I have another XACT
> > that just want's to update another field in the FK row, that
> > second XACT must lock the PK row in the first place or this
> > entire thing leads to deadlocks. If one table has alot of FK
> > constraints, this causes not really wanted lock contention.
> >
> > The clean way to get out of it would be to skip non-FK-change
> > events in the UPDATE trigger and do alot of extra work in the
> > SET DEFAULTS trigger. Actually it'd be to check if we're
> > actually deleting the FK defaults values from the PK table,
> > and if so we'd have to check if references exist by doing
> > another NO ACTION kinda test.
> >
> > Any other smart idea?
>
> read-write locks?

Just discussed it with Tom Lane while he'd been here in
Norfolk and it's even more ugly. We couldn't even pull out
the FK's column defaults at this time to check if we are
about to delete the corresponding PK because they might call
all kinds of functions with tons of side effects we don't
want.

Seems the only way to do it cleanly is to have the parser
putting the information which TLEs are *OLD* and which are
*NEW* somewhere and pass it all down through the executor
(remembering it per tuple in the deferred trigger queue) down
into the triggers.

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 #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com