Re: foreign keys and RI triggers

On Thu, 26 May 2005, Neil Conway wrote:

> I spent a little while looking into a performance issue with a large
> UPDATE on a table with foreign keys. A few questions:
>
> (1) When a PK table is updated, we skip firing the per-row UPDATE RI
> triggers if none of the referenced columns in the PK table have been
> modified. However, AFAICS we do not apply a similar optimization for
> updates of foreign key tables: if a user does not modify the foreign key
> column, we needn't check for the presence of the FK column value in the
> primary key table. Is there a reason we don't implement this?

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. I'm not sure why it's doing the
transaction id check, but it looks like it will do an equals check at
least some of the time.

> (2) For per-row RI triggers of all kinds, we save the trigger under
> CurTransactionContext and invoke it at the end of the current query.
> There is not even overflow to disk (the report that prompted me to look
> into this was someone's database crashing because they kept running OOM
> when doing an UPDATE of a large table with FKs on a pretty lowend
> machine). While avoiding consuming a lot of memory for queued trigger
> execution is worth doing anyway, ISTM we needn't queue RI triggers in
> the first place. Is there a reason we can't just invoke after-row RI
> triggers immediately?

If I'm understanding the question, there's two things. First is deferred
constraints and the second is that 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;

In no action, that's not an error AFAIK because the constraint is
satisfied at end of statement. If the order of updates happened such that
the key=2 row were updated first we couldn't know whether or not the
constraint would be satisfied by later updates to the same table.