Window Functions: v07 APIs and buffering strateties - Mailing list pgsql-hackers

As I promised, version 7 of the window functions is now released. At
the same time, git repository branch comes back to master.

git: http://git.postgresql.org/?p=~davidfetter/window_functions/.git
patch: http://umitanuki.net/pgsql/window_functions.patch.20081028.gz

It's too huge to send it to this list :) and I don't have time enough
to update the usual document but more comments in source code were put
than before so check it out!

Thanks to those feedbacks I found the basic cumulative aggregate is
necessary even though FRAME clause is not supported. In window
specifications like:
WINDOW w AS (ORDER BY id)
the window frame is implicitly made as BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW. But still SQL spec window functions like percent_rank()
need all rows of the partition. So I introduce buffering strategy in
Window node.

So now we have:
SELECT sum(i) OVER (ORDER BY i) FROM generate_series(1, 10) i;sum
-----  1  3  6 10 15 21 28 36 45 55
(10 rows)

and all SQL spec functions.

I guess this release covers all the features I had intended when I was
started. Though FRAME clause support is not done yet, a basic
execution mechanism is prepared so if the parser supports it, the
executor doesn't worry about it. But before heading for FRAME clause,
we must support buffering strategies, and FRAME clause may be in 8.5
cycle. In this release, I wrote around the Partition buffering so no
optimization for row_number() is implemented, though you can be
inspired how to add another buffering when reading nodeWindow.c. For
the future buffering addition, I defined and classified those Window
function APIs:

extern int64 WinRowCurrentPos(WindowObject winobj);
extern Datum WinRowGetArg(WindowObject winobj, ExprState *argstate,
bool *isnull);
extern bool WinRowGetTuple(WindowObject winobj, TupleTableSlot *slot);

extern bool WinFrameShrinked(WindowObject winobj);
extern bool WinFrameExtended(WindowObject winobj);
extern int64 WinFrameGetRowNum(WindowObject winobj);
extern Datum WinFrameGetArg(WindowObject winobj, ExprState *argstate,        int relpos, int seektype, bool *isnull);
extern bool WinFrameGetTuple(WindowObject winobj, TupleTableSlot *slot,        int relpos, int seektype);
extern int WinFrameShrinkingNum(WindowObject winobj);
extern int WinFrameExtendedNum(WindowObject winobj);

extern int64 WinPartGetRowNum(WindowObject winobj);
extern Datum WinPartGetArg(WindowObject winobj, ExprState *argstate,        int relpos, int seektype, bool *isnull);
extern bool WinPartGetTuple(WindowObject winobj, TupleTableSlot *slot,        int relpos, int seektype);

extern WindowIter WinFrameStartIter(WindowObject winobj, int pos);
extern WindowIter WinPartStartIter(WindowObject winobj, int pos);
extern bool WinIterNext(WindowIter iter);
extern Datum WinIterGetArg(WindowIter iter, ExprState *argstate, bool *isnull);
extern bool WinIterGetTuple(WindowIter iter, TupleTableSlot *slot);

With these APIs, you can write advanced window aggregate subtracting
shrinking rows of the frame, and buffering supports.

I believe I can send another patch until the next commit fest, but not
sure about sgml documentation. If someone is interested in this
feature, feel free to help me in documentation! I think the lack is
around SQL spec window functions, how the window frame works (and that
we don't support FRAME clause in this release), and basic concept of
window function architecture. We don't have to touch deep inside of
window function APIs and buffering strategies since the window
functions cannot be defined as user function now.

Comments, feedbacks?

Regards,

-- 
Hitoshi Harada