From: | Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> |
Cc: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: BRIN minmax multi - incorrect distance for infinite timestamp/date |
Date: | 2023-10-19 12:41:34 |
Message-ID: | CAExHW5vY+BCoRdrvLjYtCvDvNmGEKEWN+jgen_TMNwqh0=d1Wg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Oct 19, 2023 at 4:51 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> On 10/19/23 11:22, Ashutosh Bapat wrote:
> > On Thu, Oct 19, 2023 at 2:31 PM Tomas Vondra
> > <tomas(dot)vondra(at)enterprisedb(dot)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
Hmm, I see. This uses generate_series(timestamp, timestamp, interval) version.
date + integer -> date though, so the following works. It's also an
example at https://www.postgresql.org/docs/16/functions-srf.html.
#SELECT '5874896-12-01'::date + i FROM
generate_series(1,10) s(i);
I think we should provide generate_series(date, date, integer) which
will use date + integer -> date.
--
Best Wishes,
Ashutosh Bapat
From | Date | Subject | |
---|---|---|---|
Next Message | Hayato Kuroda (Fujitsu) | 2023-10-19 12:53:51 | RE: [PoC] pg_upgrade: allow to upgrade publisher node |
Previous Message | Andrei Zubkov | 2023-10-19 12:40:24 | Re: [PATCH] Tracking statements entry timestamp in pg_stat_statements |