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 ~{:-)
pj@illuminatedcomputing.com
