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

On Thursday, March 26, 2015, <rward@uberlogik.com> wrote:

The following bug has been logged on the website:

Bug reference: 12908
Logged by: Rob Ward
Email address: rward@uberlogik.com <javascript:;>
PostgreSQL version: 9.4.1
Operating system: Windows 8.1
Description:

Summary: Under certain conditions, when I use a tstzrange constructor in
the WHERE clause of a query, Postgresql incorrectly gives the error: ERROR:
range lower bound must be less than or equal to range upper bound

Details:

Two days ago, I switched a new app's database from PostgreSQL 9.3 to 9.4.1.
It's a new app, so was no legacy data, so I just recreated the schema on
the
new server (on my local development machine running Win 8.1).

There were no changes to the schema, but I quickly began running into a
showstopper bug:

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.

Clues that may help:

1) The queries in question have been working fine under months of intensive
testing on Postgres 9.3

2) When a query fails, it will always fail no matter what dates/times I put
in the constructor. The problem will only go away if I drop the schema and
recreate all the tables and start again.

3) If a query fails as described, the same query in a different database on
the same server will work ok.

4) If I copy the constructor and run it stand-alone, that constructor will
work correctly as expected - e.g. select tstzrange('2015-03-25
12:00:00+00'::timestamptz, '2015-03-26 12:00:00+00'::timestamptz, '[]')

I appreciate that this is a tough bug to diagnose with no clear way to
reproduce it. Unfortunately given that it's a showstopper I have to revert
back to 9.3 for the moment, but am happy to do any diagnostics, etc to help
track this one down.

Thanks,

What happens if your disconnect the session that's irate saw the error?
Do other sessions to the same so begin experiencing this error after the
first incidence?
What happens if you stop and start the database once the error manifests?
Can you observe the phase of the moon when the first occurrence happens? :)
Do you know what a gremlin is? :)

David J.

rward@uberlogik.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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Sorry Tom - OP sent this to me only. Operator Error.

David J.

---------- Forwarded message ----------
From: Rob Ward <rob@uberlogik.com>
Date: Tue, Mar 31, 2015 at 8:22 AM
Subject: Re: [BUGS] BUG #12908: tstzrange constructor fails when used in
WHERE clause
To: "David G. Johnston" <david.g.johnston@gmail.com>

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 - now fixed and check
constraints added...). D'oh. My apologies for any wasted time from this bug
report.

On a related note: is there a bug-tracker somewhere I can update directly
to close the issue?

On Tue, Mar 31, 2015 at 9:01 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

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@sss.pgh.pa.us> wrote: