My understanding is that an UPDATE statement will fire exactly the same
number of RI checks as does an INSERT, in all cases.
ISTM possible that we could optimise away some RI checks in the case of
UPDATEs. This might or might not save some cycles but it will definitely
reduce the amount of locking taking place on referenced tables.
A heavily updated referencing table can cause a stream of locks against
a referenced table. Attempts to UPDATE the row on the referenced table
could be severely hampered since only an UPDATE of the PK of the
referenced table really needs to cause a cross-check.
I see nothing in the SQL Standard that requires these checks to be made
for an UPDATE, only that the integrity must not be violated.
We know the attribute numbers of the keys for any particular trigger, so
it seems possible to make an equality comparison between the old and new
attribute values. If the values are similar, we can skip the check
altogether. This seems cheaper than executing a statement to compare the
new against the value in the referenced table.
Any objections to implementing this?
It would be even better if there was some way of not executing the
trigger at all if we knew that the UPDATE statement doesn't SET the FK
columns. That would require us to pass information about the potentially
changed columns as part of the TriggerData data structure. That could be
passed as an additional bitmap through to constraint triggers, so that
they can return immediately if they have nothing to do - though that
check makes more sense to perform *before* the trigger is queued for
later execution.
Comments?
-- Simon Riggs EnterpriseDB http://www.enterprisedb.com