On Tue, 17 Apr 2007, Tom Lane wrote: > A recent discussion led me to the idea that FK triggers are fired > unnecessarily during an UPDATE if the foreign-key column(s) contain > any NULLs, because ri_KeysEqual() treats two nulls as unequal, > and therefore we conclude the row has changed when it has not. > I claim that both ri_KeysEqual() and ri_OneKeyEqual() could consider > two nulls to be equal.
For ri_KeysEqual, I think so, since we actually aren't testing equality as much as difference between the rows that might invalidate the constraint. And, it does seem like with the code in trigger.c that the other checks in the _upd functions in ri_triggers.c are redundant, but I'm vaguely afraid I've forgotten something. For ri_OneKeyEqual, I think like ri_AllKeysUnequal we know that the old row doesn't have NULLs in the places it's currently called (although I don't think this is commented). It seems like it should stay consistent with ri_KeysEqual and that not putting the foo = NULL or foo = DEFAULT seems better for the current calling cases besides. > Furthermore it seems like ri_AllKeysUnequal() should do so too; the case > can't arise at the moment because the sole caller already knows that one > of the key sets contains no nulls, but if this weren't so, the > optimization would be actively wrong if we concluded that two nulls were > unequal. Hmm, probably so, although at least this does appear to be commented at the calling site to mention that it's depending on the fact that there are no NULLs. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq