Re: Support for RANGE ... PRECEDING windows in OVER

Thanks! The discussions have been useful, although I am currently just
reviewing the code.
I think a good starting point will be to refactor/imrpove the
WinGetFuncArgInPartition and WinGetFuncArgInFrame functions.
Tom Lane wrote this about them before comitting the patch:

*

I'm not terribly happy with the changes you made in WinGetFuncArgInPartition

and WinGetFuncArgInFrame to force the window function mark to not go

past frame start in some modes. Not only is that pretty ugly, but I

think it can mask bugs in window functions: it's an error for a window

function to fetch a row before what it has set its mark to be, but in

some cases that wouldn't be detected because of this change. I think

it would be better to revert those changes and find another method of

protecting fetches needed to determine the frame head. One idea is

to create a separate read pointer that tracks the frame head whenever

actual fetches of the frame head might be needed by update_frameheadpos.

I committed it without changing that, but I think this should be

revisited before trying to add the RANGE value PRECEDING/FOLLOWING

options, because those will substantially expand the number of cases

where that hack affects the behavior.*

I am honestly not 100% certain why these functions have issues, but this
seems a good place to start investigating.

Ian Link

> Craig Ringer <mailto:craig(at)2ndquadrant(dot)com>
> Thursday, June 20, 2013 7:37 PM
>
> Good to know, and welcome.
>
> I hope the links to the archived discussions on the matter were useful
> to you.
>
> Craig Ringer <mailto:craig(at)2ndquadrant(dot)com>
> Thursday, June 20, 2013 7:24 PM
> Hi all
>
> Since 8.4, PostgreSQL has had extremely useful window function support -
> but support for "RANGE PRECEDING / FOLLOWING" windows was dropped late
> in 8.4's development in order to get the rest of the feature in, per
> http://archives.postgresql.org/pgsql-hackers/2010-02/msg00540.php.
>
> It looks like there was discussion of requiring a new opclass to be
> declared for types or otherwise extending opclasses to provide the
> information required for RANGE ... PRECEDING / FOLLOWING (
> http://www.postgresql.org/message-id/20100211201444.GA28270@svana.org )
> . I can't find any sign that it went anywhere beyond some broad
> discussion:
> http://www.postgresql.org/message-id/13993.1265920013@sss.pgh.pa.us at
> the time.
>
> I've missed this feature more than once, and am curious about whether
> any more recent changes may have made it cleaner to tackle this, or
> whether consensus can be formed on adding the new entries to btree's
> opclass to avoid the undesirable explicit lookups of the '+' and '-'
> oprators.
>
> Some question seems to remain open about how ranges over
> timestamps/intervals should work, but this wasn't elaborated on.
>
> There's been interest in this, eg:
>
> http://pgsql.hackers.free-usenet.eu/[HACKERS]-range-intervals-in-window-function-frames_T66085695_S1
>
> http://grokbase.com/t/postgresql/pgsql-general/105a89gm2n/postgresql-9-0-support-for-range-value-preceding-window-functions
>
>
>