Re: Infinite Interval - Mailing list pgsql-hackers

From Dean Rasheed
Subject Re: Infinite Interval
Date
Msg-id CAEZATCVs8HpjvufOep+XK+kpau0SbCaj5U6oq=-S2KfSmV6Xeg@mail.gmail.com
Whole thread Raw
In response to Re: Infinite Interval  (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>)
Responses Re: Infinite Interval
List pgsql-hackers
On Thu, 24 Aug 2023 at 14:51, Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> The patches still apply. But here's a rebased version with one white
> space error fixed. Also ran pgindent.
>

This needs another rebase, and it looks like the infinite interval
input code is broken.

I took a quick look, and had a couple of other review comments:

1). In interval_mul(), I think "result_sign" would be a more accurate
name than "result_is_inf" for the local variable.

2). interval_accum() and interval_accum_inv() don't work correctly
with infinite intervals. To make them work, they need to count the
number of infinities seen, to allow them to be subtracted off by the
inverse function (similar to the code in numeric.c, except for the
NaN-handling, which will need to be different). Consider, for example:

SELECT x, avg(x) OVER(ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
  FROM (VALUES ('1 day'::interval),
               ('3 days'::interval),
               ('infinity'::timestamptz - now()),
               ('4 days'::interval),
               ('6 days'::interval)) v(x);
ERROR:  interval out of range

as compared to:

SELECT x, avg(x) OVER(ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
  FROM (VALUES (1::numeric),
               (3::numeric),
               ('infinity'::numeric),
               (4::numeric),
               (6::numeric)) v(x);

    x     |        avg
----------+--------------------
        1 | 2.0000000000000000
        3 |           Infinity
 Infinity |           Infinity
        4 | 5.0000000000000000
        6 | 6.0000000000000000
(5 rows)

Regards,
Dean



pgsql-hackers by date:

Previous
From: jian he
Date:
Subject: Re: SQL:2011 application time
Next
From: vignesh C
Date:
Subject: Re: CHECK Constraint Deferrable