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?
(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?
(Hmm, I suppose we would need to defer firing the trigger until the
command ID is incremented if the foreign key references its own table.
But even so, this should not be an issue for non-self-referential
foreign keys.)
(3) This is minor, but AFAICS RI_FKey_check_upd() is not used --
RI_FKey_check_ins() is used to validate both inserts and updates on
tables with foreign keys (see tablecmds.c circa 4423). Both functions
are just wrappers over RI_FKey_check() anyway. This is rather confusing;
would anyone object if I removed both functions and made RI_FKey_check()
public?
-Neil
