Re: Fix UNIQUE WITHOUT OVERLAPS + NULLS NOT DISTINCT + multiranges

Hi Paul, Andres and everyone,

Greetings of the day.

​I wanted to follow up on your report regarding temporal primary keys and
unique constraints misbehaving with NULLS NOT DISTINCT.

​The patch adding a parameter to index_recheck_constraint looks like a
solid approach for handling nulls correctly in multiranges.

​Additionally, I looked into whether a similar bug exists for regular
EXCLUDE constraints using non-strict operators (like IS NOT DISTINCT FROM).
My tests showed that these exclusion constraints still treat nulls as
distinct, which doesn't seem to be explicitly documented anywhere outside
of a code comment. Given that SP-GiST assumes strict operators and never
passes nulls to the opclass, we should decide whether to allow and fix this
behavior or explicitly forbid it.
​Let me know your thoughts on this, and whether you'd like me to send a
separate patch for the EXCLUDE case.
​Best regards,
Osama Abdul Qader

On Fri, 9 Oct, 2026, 9:47 am Paul A Jungwirth, <pj(at)illuminatedcomputing(dot)com>
wrote:

> 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(at)illuminatedcomputing(dot)com
>