In the 9.0 version of exclusion constraints, we added an extra check to
ensure that, when searching for a conflict, a tuple at least found
itself as a conflict. This extra check is not present in 9.1+.
It was designed to help diagnose certain types of problems, and is fine
for most use cases. A value is equal to itself (and therefore conflicts
with itself), and a value overlaps with itself (and therefore conflicts
with itself), which were the primary use cases. We removed the extra
check in 9.1 because there are other operators for which that might not
be true, like <>, but the use case is a little more obscure.
However, values don't always overlap with themselves -- for instance the
empty period (which was an oversight by me). So, Abel Abraham Camarillo
Ojeda ran into a rather cryptic error message when he tried to do that:
ERROR: failed to re-find tuple within index "t_period_excl"
HINT: This may be because of a non-immutable index expression.
I don't think we need to necessarily remove the extra check in 9.0,
because the workaround is simple: add a WHERE clause to the constraint
eliminating empty periods. Perhaps we could improve the error message
and hint, and add a note in the documentation.
Thoughts?
Regards,Jeff Davis