Re: BUG #12908: tstzrange constructor fails when used in WHERE clause

rward(at)uberlogik(dot)com writes:
> Queries that construct a tstzrange in the where clause fail with the error
> message "ERROR: range lower bound must be less than or equal to range upper
> bound. SQL state: 2200". This error happens despite the fact that the range
> bounds are correct (i.e. lower bound < upper).

> Simplest example of a query that would fail with this error:

> select period from foo
> where period && tstzrange('2015-03-25 12:00:00+00'::timestamptz, '2015-03-26
> 12:00:00+00'::timestamptz, '[]')

> However, I can't seem to reproduce the conditions that trigger this bug.
> Once that unknown condition is triggered though, the incorrect behavior
> described above happens every time.

I'm suspicious that the triggering event for this is a type cache flush;
but it's hard to see how that would work exactly, because typcache.c never
flushes the cache fields for range-type properties. Still, you might work
on the assumption that the user-level triggering event is some DDL
operation that affects a type definition --- not necessarily one with any
direct connection to the failing query --- and see if you can get to a
repeatable way to reproduce the issue.

Also, once you've gotten a backend into the failing state, it would be
useful to attach to it with gdb, set a breakpoint at errfinish, and get a
stack trace from the point of the error report. The error must be coming
from range_serialize, but it might be a mistake to assume that the direct
caller of that is the tstzrange() constructor. (This line of thought
would lead to the idea that there's bad statistics for a range column,
or some other mechanism that would cause the planner to try to construct
a bogus range value on its way to calculating selectivity estimates.
In that case, just doing an ANALYZE might cause the error to appear or
disappear.)

regards, tom lane