Fwd: BUG #12908: tstzrange constructor fails when used in WHERE clause - Mailing list 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:

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