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

From: rward(at)uberlogik(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #12908: tstzrange constructor fails when used in WHERE clause
Date: 2015-03-27 04:52:55
Message-ID: 20150327045255.2492.93419@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 12908
Logged by: Rob Ward
Email address: rward(at)uberlogik(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message dreamsxin 2015-03-27 08:51:50 BUG #12909: pg_dump error
Previous Message Jon Nelson 2015-03-26 23:02:12 Re: minor: contrib/btree_gin/btree_gin.c uses DirectFunctionCall3(inet_in, ..)