Re: Foreign key verification trigger conditions

Hi Tom,
Thank you for pointing out the condition under which this occurs, I
had not made the connection that the check was only occurring when the
value in the other columns with foreign keys are null. I agree 100%
that a strict key equality check that is in general use in the
database should not return true for null = null. But I believe we can
always come to the conclusion that a foreign key constraint is
satisfied if all of the key values are null since that effectively
means that the relationship is not present. Searching for ri_KeysEqual
leads me to this discussion of the same topic:
http://archives.postgresql.org/pgsql-hackers/2007-04/msg00803.php

Would there be any interest in implementing this change? Should I be
reporting a bug to get it into the development queue?
(My apologies that I have neither the skills nor the resources to work
on it myself.)

Thanks,
-J

On Mon, Jun 1, 2009 at 9:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> j-lists <jamisonlists(at)gmail(dot)com> writes:
>> I have an update statement that affects every row in a given table.
>> For that table it changes the value in a single column, which itself
>> has a foreign key constraint. The table has an additional 9 foreign
>> keys, some of which reference large tables.
>> My expectation would be that only the changed column would be checked
>> against the foreign key of interest, instead I find that all the
>> foreign keys are checked when this statement is executed.
>
> What your test case actually seems to show is that the skip-the-trigger
> optimization doesn't fire when the column value is NULL.  Which is
> because ri_KeysEqual() doesn't consider two nulls to be equal.  It's
> possible we could change that but I'd be worried about breaking other
> cases that are actually semantically critical...
>
>                        regards, tom lane
>