Re: Add RANGE with values and exclusions clauses to the Window Functions

On Wed, Jan 31, 2018 at 5:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> We could imagine reimplementing WinGetFuncArgInFrame to fix this, but
> aside from the sheer inefficiency of simple fixes, I'm not very clear
> what seeking relative to WINDOW_SEEK_CURRENT should mean when the current
> row is excluded. (Of course, the current row could have been out of frame
> before too. Maybe we should just get rid of WINDOW_SEEK_CURRENT?)
>
>
​The exclusion clause is frame-specific and none of the three frame callers
use WINDOW_SEEK_CURRENT (only the single partition caller does). So unless
there is an external code concern removing WINDOW_SEEK_CURRENT from being
valid for WinGetFuncArgInFrame seems straight forward and probably could be
done to remove dead code whether frame exclusion is implemented or not.
And it should remain dead since, as you say, in a frame context the current
row may not be represented even today.

The three ​callers of WinGetFuncArgInFrame don't use the isout argument;
they probably need to read that and a new isexcluded argument. Start at
the head, loop until isout = true || isexcluded = false.

You could create a partition/frame that retains its contiguous property but
you then need to map multiple original row positions onto the single frame
rows that denote the head and tail positions for each. This seems
considerably more bug-prone; but I don't really have a feel for how
sheer-ly inefficient the iteration would be (assuming it is even plausible).

I do think moving that decision and code to a separate patch would be a
good separation of work.

The obvious use case for me (I haven't tried hard here) would be something
like the question: compare my value to the average value of the 4 previous
and 4 subsequent records.

Implementing the standard is a plus - though agreed that the implementation
itself makes a difference. With the iterative approach the complexity
seems manageable and performance paid for only by the user of the feature.

David J.