more support for various frame types of window functions

From: Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: more support for various frame types of window functions
Date: 2009-11-09 09:39:54
Message-ID: e08cc0400911090139g1f0e536fj17fbcec533cc8eca@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'm not sure if it can be finished until the start of the next CF, but
I've been working on $subject. This work intends to extend current
limited frame types of window functions such like below;

- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- ORDER BY x RANGE BETWEEN CURRENT ROW AND f FOLLOWING
- ORDER BY x RANGE BETWEEN p PRECEDING AND f FOLLOWING

where "p" and "f" are values that indicate preceding/following frame
boundary offsets from current row (or peer). With this feature, you
can calculate something like "moving average" by SQL.

Frame types that won't be introduced in this work includes:

- EXCLUDE clause

The hardest point is that aggregates must be re-initialized as rows
exit from current frame, which doesn't occur in 8.4 design. One of the
solution for this is to let aggregates have "negative trans functions"
(NTF), and some comments in nodeWindowAgg.c say about it, which
current aggregate system doesn't have. But my work doesn't introduce
this mechanism because

1) "negative trans function" doesn't do anything in normal aggregate
2) forcing that to everyone who writes his/her own aggregate is quite
hard and incompatible to older releases
3) so, we must at least support aggregates that don't have NTF even if
it will be introduced in the future

That means moving average is initialized again on frame-off situation
as the frame moves down. I know that may kill it's performance but
reasons above result in my proposing design.

If you have better ideas please feel free to tell me, and any comments welcomed.

Regards,

--
Hitoshi Harada

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2009-11-09 10:09:09 Re: more support for various frame types of window functions
Previous Message Hans-Juergen Schoenig -- PostgreSQL 2009-11-09 08:46:06 Re: next CommitFest