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

On Thursday, March 26, 2015, <rward(at)uberlogik(dot)com> wrote:

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