Thread: RI checks during UPDATEs
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
"Simon Riggs" <simon@2ndquadrant.com> writes: > Any objections to implementing this? Only that it was done a long time ago --- see RI_FKey_keyequal_upd_pk/fk. > 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. People keep suggesting that, and the counterexample is always that you can't know what a BEFORE trigger might do to the row. regards, tom lane
On Tue, 2007-01-30 at 15:24 -0500, Tom Lane wrote: > "Simon Riggs" <simon@2ndquadrant.com> writes: > > Any objections to implementing this? > > Only that it was done a long time ago --- see > RI_FKey_keyequal_upd_pk/fk. OK, funny guy. :-) Its not very well documented, is all I can say. The code comments elsewhere in the file are very specific that the code applies to UPDATEs as well as INSERTs, hence my confusion. I'm relieved, actually, but still have a locking problem to resolve. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
On Jan 30, 2007, at 1:17 PM, Simon Riggs wrote: > 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. Other databases allow you to put a WHERE or CHECK clause on triggers, so that they will only fire if that evaluates to true. That would allow FK triggers to be defined as CREATE TRIGGER ... WHERE NEW.fk != OLD.fk and would obviously have other uses as well. Of course the question is how much time that would save vs just doing the same check in the trigger function itself. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)