Re: Do foreign key triggers get ran even if the key's value doesn't change?

On Wednesday, May 21, 2014, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

>
> On Wed, May 21, 2014 at 1:11 PM, Joe Van Dyk <joe(at)tanga(dot)com<javascript:_e(%7B%7D,'cvml','joe(at)tanga(dot)com');>
> > wrote:
>
>> I came across http://bonesmoses.org/2014/05/14/foreign-keys-are-not-free/
>> which seems to indicate so.
>>
>> When I run the following test script, having 50 foreign keys takes
>> about twice as long to do the update. Is there a reason for that?
>> Seems like the RI triggers wouldn't have to run on updates if the
>> value doesn't change.
>>
>
> That's kind of a question of definitions. Perhaps the trigger itself
> doesn't need to run, but the code that decides whether the trigger needs to
> run does need to run. Where do you draw the line around what is the
> trigger proper and what is just infrastructure?
>
> However you wish to define it, change your function so that it actually
> does change the key field, and see how much slower that is than the
> behavior where you update the row without updating the key.
>
>

I was expecting that the RI update triggers would have a "when (new.key is
distinct from old.key)" condition on them, which would mean that the number
of referencing tables wouldn't matter.

> Cheers,
>
> Jeff
>