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

On Wed, May 21, 2014 at 7:48 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:

> 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> 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.
>

But that condition is checked for each constraint individually, not for all
constraints simultaneously. A table can be referenced on multiple
combinations of columns, so just one check may not suffice. I guess the
triggers could be organized into groups of identical firing criteria and
then checked only once per group, but that seems like a pretty obscure
optimization to make. I don't know how you would reorganize such groupings
in a concurrency safe way when constraints were added or removed.

Cheers,

Jeff