Re: Extra check in 9.0 exclusion constraint unintended consequences

Hi:

On Tue, Jul 5, 2011 at 11:26 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> 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.

That's what I'm doing now: using a where clause to workaround... it's easy, but
I was still amazed about what that error message meant...

Thanks.

> Thoughts?
>
> Regards,
>        Jeff Davis
>
>
>
>