Re: foreign keys and RI triggers

On Thu, 26 May 2005, Stephan Szabo wrote:

> On Fri, 27 May 2005, Neil Conway wrote:
>
> > Stephan Szabo wrote:
> > > Are you sure? RI_FKey_Check seems to have a section on
> > > TRIGGER_FIRED_BY_UPDATE which seems to check if the keys are equal if the
> > > old row wasn't part of this transaction.
> >
> > Well, regardless of how RI_FKey_Check() itself works, ISTM there is no
> > need to enqueue the RI trigger in the first place. That's when the
> > update-on-PK-table optimization is applied -- see trigger.c circa 3005.
> > The specific case I was looking at resulted in the postgres backend
> > allocating a few hundred MB just to store all the pending RI triggers,
> > even though the UPDATE in question didn't change the foreign key field,
> > so it didn't matter a great deal how quickly RI_FKey_Check() was able to
> > bail out.
>
> Okay, I can't think of cases even with triggers and the like where
> removing the check on equal valued rows would give appreciably different
> results, but I haven't thought too hard about it.

Err, except the case that Tom mentions in his message.