Re: foreign keys and RI triggers

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.

> > If I'm understanding the question, there's two things. First is deferred
> > constraints
>
> Right -- obviously we can't fire RI triggers for deferred constraints
> immediately. Immediate constraints are the common case, though.
>
> > constraints happen after the entire statement.
> > In a case like:
> > insert into pk values(2);
> > insert into pk values(1);
> > insert into fk values(2);
> > update pk set key=key+1;
>
> Hmm, good point. But ISTM there are still some circumstances in which we
> can safely check the RI trigger immediately, rather than at end of
> statement. For example, updating the FK table, inserting into the FK
> table, or deleting from the PK table.

Unfortunately, I don't think so, if my assumption that user triggers are
supposed to happen before constraint checks is true. In that case, we
must wait until not only the action but all triggers fired by that action
happen in order to run the constraint check because a trigger could make
an otherwise invalid row valid.