Re: Extra check in 9.0 exclusion constraint unintended consequences

On Tue, Jul 5, 2011 at 12:26 PM, 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.

I think it's probably too late to go fiddling with the behavior of 9.0
at this point. If we change the text of error messages, there is a
chance that it might break applications; it would also require those
messages to be re-translated, and I don't think the issue is really
important enough to justify a change. I am happy to see us document
it better, though, since it's pretty clear that there is more
likelihood of hitting that error than we might have suspected at the
outset.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company