Re: 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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)