Hi Hackers, Andres reported that temporal primary keys and unique constraints can misbehave with NULLS NOT DISTINCT.[0] They work for ranges, which are not lossy in a GiST index, but multiranges call index_recheck_constraint, which never treats nulls as matching.
This patch adds a parameter to index_recheck_constraint so we can say what kind of null handling we want. I was curious if the same bug existed for regular EXCLUDE constraints. They don't have a NULLS NOT DISTINCT option, but what if the user specifies a non-strict operator? The attached SQL file (exclusion-nonstrict-operator.sql) shows that such an exclusion constraint ignores the operator's non-strictness and still treats nulls as distinct: with a non-strict IS NOT DISTINCT FROM operator, it rejects a duplicate non-null value but accepts two NULLs. Perhaps that is not a bug, but I couldn't find it documented anywhere that exclusion constraints assume a strict operator (except for a comment in check_exclusion_or_unique_constraint). If we wanted to allow them, we should add a fix similar to the one here. But maybe forbidding them is better? In SP-GiST, they would never work: SP-GiST never passes nulls to the opclass, because it assumes every opclass has only strict operators.[1] I'm happy to send a separate patch for the EXCLUDE case. [0] https://www.postgresql.org/message-id/kcyaok346iwocfdourf2oojgtz7ggxmh2ugis7qqhggs4qfzc2%40cu6pkox4rfwp [1] https://www.postgresql.org/docs/current/spgist-extensibility.html Yours, -- Paul ~{:-) [email protected]
v1-0001-Fix-UNIQUE-NULLS-NOT-DISTINCT-with-WITHOUT-OVERLA.patch
Description: Binary data
exclusion-nonstrict-operator.sql
Description: Binary data
