Horton

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

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-10-19T11:21:51Z
Lists: pgsql-hackers 
On 10/19/23 11:22, Ashutosh Bapat wrote:
> On Thu, Oct 19, 2023 at 2:31 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
> 
>>
>> Does that explain the algorithm? I'm not against clarifying the comment,
>> of course.
> 
> Thanks a lot for this explanation. It's clear now.
> 
>> I tried to do that, but I ran into troubles with the "date" tests. I
>> needed to build values that close to the min/max values, so I did
>> something like
>>
>> SELECT '4713-01-01 BC'::date + (i || ' days')::interval FROM
>> generate_series(1,10) s(i);
>>
>> And then the same for the max date, but that fails because of the
>> date/timestamp conversion in date plus operator.
>>
>> However, maybe two simple generate_series() would work ...
>>
> 
> Something like this? select i::date from  generate_series('4713-02-01
> BC'::date,  '4713-01-01 BC'::date, '-1 day'::interval) i;

That works, but if you try the same thing with the largest date, that'll
fail

  select i::date from  generate_series('5874896-12-01'::date,
                                       '5874897-01-01'::date,
                                       '1 day'::interval) i;

  ERROR:  date out of range for timestamp


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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