Re: Infinite Interval

hi.

fixed the doc special value inf/-inf reference. didn't fix the EXTRACT
function doc issue.

I refactor the avg(interval), sum(interval), so moving aggregate,
plain aggregate both work with +inf/-inf.
no performance degradation, in fact, some performance gains.

--setup for test performance.
create unlogged table interval_aggtest AS
select  g::int as a
        ,make_interval(years => g % 100, days => g % 100, hours => g %
200 , secs => random()::numeric(3,2) *100 ) as b
from    generate_series(1, 100_000) g;
--use foreign data wrapper to copy exact content to interval_aggtest_no_patch
create unlogged table interval_aggtest_no_patch AS
select * from interval_aggtest;

--queryA
explain (analyze, costs off, buffers)
SELECT a, avg(b) OVER(ROWS BETWEEN 1 preceding AND 2 FOLLOWING)
from    interval_aggtest \watch i=0.1 c=10

--queryB
explain (analyze, costs off, buffers)
SELECT a, avg(b) OVER(ROWS BETWEEN 1 preceding  AND 2 FOLLOWING)
from    interval_aggtest_no_patch \watch i=0.1 c=10

--queryC
explain (analyze, costs off, buffers)
SELECT a, sum(b) OVER(ROWS BETWEEN 1 preceding AND 2 FOLLOWING)
from    interval_aggtest \watch i=0.1 c=10

--queryD
explain (analyze, costs off, buffers)
SELECT a, sum(b) OVER(ROWS BETWEEN 1 preceding AND 2 FOLLOWING)
from    interval_aggtest_no_patch \watch i=0.1 c=10

--queryE
explain (analyze, costs off, buffers)
SELECT sum(b), avg(b)
from    interval_aggtest \watch i=0.1 c=10

--queryF
explain (analyze, costs off, buffers)
SELECT sum(b), avg(b)
from    interval_aggtest_no_patch \watch i=0.1 c=10

queryA  execute 10 time, last executed time(ms) 748.258
queryB  execute 10 time, last executed time(ms) 1059.750

queryC  execute 10 time, last executed time(ms) 697.887
queryD  execute 10 time, last executed time(ms) 708.462

queryE  execute 10 time, last executed time(ms) 156.237
queryF  execute 10 time, last executed time(ms) 405.451
---------------------------------------------------------------------
The result seems right, I am not %100 sure the code it's correct.
That's the best I can think of. You can work based on that.