From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: adrian.klaver@aklaver.com
Cc: frazer@frazermclean.co.uk
Cc: pgsql-general@postgresql.org
Subject: Re: Unexpected interval comparison
Date: Thu, 30 Mar 2017 10:57:19 -0400
Message-ID: <2087.1490885839@sss.pgh.pa.us> (raw)
In-Reply-To: <20170330.164651.249668658.horiguchi.kyotaro@lab.ntt.co.jp>
References: <1490104629.422698.918452336.26FA96B7@webmail.messagingengine.com>
<30765.1490107336@sss.pgh.pa.us>
<375c9e5a-960f-942c-913f-55632a1f0a90@aklaver.com>
<20170330.164651.249668658.horiguchi.kyotaro@lab.ntt.co.jp>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> writes:
> At Tue, 21 Mar 2017 07:52:25 -0700, Adrian Klaver <adrian.klaver@aklaver.com> wrote in <375c9e5a-960f-942c-913f-55632a1f0a90@aklaver.com>
>> On 03/21/2017 07:42 AM, Tom Lane wrote:
>>> It looks like the problem is overflow of the result of interval_cmp_value,
>>> because it's trying to compute
>>> =# select '32618665'::int8 * 30 * 86400 * 1000000;
>>> ERROR: bigint out of range
>>> It's not immediately obvious how to avoid that while preserving the
>>> same comparison semantics :-(
> Detecting the overflow during the conversion can fix it and
> preserving the semantics (except value range). The current code
> tells a lie anyway for the cases but I'm not sure limting the
> range of value is acceptable or not.
I don't think it is. It'd cause failures in attempting to enter
very large interval values into btree indexes, for instance.
A possible solution is to manually work in wider-than-64-bit
arithmetic, that is compute the comparison values div and mod
some pretty-large number and then compare the two halves.
I seem to recall that we did something similar in a few cases
years ago, before we were willing to assume that every machine
had 64-bit integer support.
Of course, for machines having int128, you could just use that
type directly. I'm not sure how widespread that support is
nowadays. Maybe a 95%-good-enough solution is to use int128
if available and otherwise throw errors for intervals exceeding
64 bits.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, horiguchi.kyotaro@lab.ntt.co.jp, adrian.klaver@aklaver.com, frazer@frazermclean.co.uk
Subject: Re: Unexpected interval comparison
In-Reply-To: <2087.1490885839@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox