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

Sadly, I don't have the luxury of blaming a gremlin for this one -- it
turned out to be a intermittent bug in my code, and the error message (from
the JDBC driver, not Postgres itself) inadvertently led me to think that
the tstzrange constructor in the WHERE clause was failing. The error
message was something like "... WHERE period && tstzrange(...). ERROR:
range lower bound must be less than or equal to range upper bound."

Basically, the query was calling a view that also constructs a tstzrange,
and it was that one that was failing (legitimately - d'oh - now fixed and
check constraints added...).

Thanks all for your responses, and my apologies for any wasted time from
this bug report.

Rob

On Tue, Mar 31, 2015 at 11:17 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>