From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
cc: adrian.klaver@aklaver.com, frazer@frazermclean.co.uk,
        pgsql-general@postgresql.org
Subject: Re: Unexpected interval comparison
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>
Comments: In-reply-to Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
	message dated "Thu, 30 Mar 2017 16:46:51 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2086.1490885839.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 30 Mar 2017 10:57:19 -0400
Message-ID: <2087.1490885839@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

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_val=
ue,
>>> because it's trying to compute
>>> =3D# 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


--=20
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general