Horton

Re: BRIN minmax multi - incorrect distance for infinite timestamp/date

Dean Rasheed <dean.a.rasheed@gmail.com>

View thread View on postgr.es
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: 2023-10-13T15:28:14Z
Lists: pgsql-hackers 
On Fri, 13 Oct 2023 at 13:17, Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
>
> I do plan to backpatch this, yes. I don't think there are many people
> affected by this (few people are using infinite dates/timestamps, but
> maybe the overflow could be more common).
>

OK, though I doubt that such values are common in practice.

There's also an overflow problem in
brin_minmax_multi_distance_interval() though, and I think that's worse
because overflows there throw "interval out of range" errors, which
can prevent index creation or inserts.

There's a patch (0009 in [1]) as part of the infinite interval work,
but that just uses interval_mi(), and so doesn't fix the
interval-out-of-range errors, except for infinite intervals, which are
treated as special cases, which I don't think is really necessary.

I think this should be rewritten to compute delta from ia and ib
without going via an intermediate Interval value. I.e., instead of
computing "result", just do something like

    dayfraction = (ib->time % USECS_PER_DAY) - (ia->time % USECS_PER_DAY);
    days = (ib->time / USECS_PER_DAY) - (ia->time / USECS_PER_DAY);
    days += (int64) ib->day - (int64) ia->day;
    days += ((int64) ib->month - (int64) ia->month) * INT64CONST(30);

then convert to double precision as it does now:

    delta = (double) days + dayfraction / (double) USECS_PER_DAY;

So the first part is exact 64-bit integer arithmetic, with no chance
of overflow, and it'll handle "infinite" intervals just fine, when
that feature gets added.

Regards,
Dean

[1] https://www.postgresql.org/message-id/CAExHW5u1JE7dxK=WLzqhCszNToxQzJdieRmhREpW6r8w6kcRGQ@mail.gmail.com

Commits

  1. Fix minmax-multi distance for extreme interval values

  2. Fix minmax-multi on infinite date/timestamp values

  3. Fix calculation in brin_minmax_multi_distance_date

  4. Fix overflow when calculating timestamp distance in BRIN