The following bug has been logged on the website:
Bug reference: 12908
Logged by: Rob Ward
Email address: rward@uberlogik.com
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,
Rob
