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 <t...@sss.pgh.pa.us> wrote: > j-lists <jamisonli...@gmail.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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
