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 ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate