add more frame types in window functions (ROWS)

From: Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: add more frame types in window functions (ROWS)
Date: 2009-11-13 19:25:02
Message-ID: e08cc0400911131125m1a89190fqb0ddd6f69c2b2f00@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Attached is the patch against HEAD to support more frame types in
window functions, including these frame types:

- ROWS BETWEEN s PRECEDING AND e PRECEDING
- ROWS BETWEEN s PRECEDING AND CURRENT ROW
- ROWS BETWEEN s PRECEDING AND e FOLLOWING
- ROWS BETWEEN s PRECEDING AND UNBOUNDED FOLLOWING
- ROWS BETWEEN CURRENT ROW AND e FOLLOWING
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- ROWS BETWEEN s FOLLOWING AND e FOLLOWING
- ROWS BETWEEN s FOLLOWING AND UNBOUNDED FOLLOWING
- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- ROWS s PRECEDING

which means that for ROWS types we now support almost all types but
for RANGE types we don't have "value PRECEDING" / "value FOLLOWING".
I'm planning to implement them until CommitFest:2010-01 so this is
"Request for Review" phase though I've arranged the patch to be
committable.

Aggregate cache mechanism is sometimes cleared as discussed the other
day. But it should be kept that the original cache mechanism for basic
(i.e. already implemented) frame types.

Some points to be reviewed are:

- Added WindowFrameDef in parsenode.h
- Is A_Const member for startOffset / endOffset is appropriate?
- Are those data types (including gram.y) well designed?
- For basic frame types, are aggregates still optimized as before?
- Added regression tests but enough?
- All error case covered? For example: ROWS s FOLLOWING AND e PRECEDING
- Added members to WindowAggState to track more information of frame
types, but isn't there more efficient way?
- Not modified docs yet

Regards,

--
Hitoshi Harada

Attachment Content-Type Size
rows_frame_types.20091114.patch application/octet-stream 58.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-11-13 19:27:30 Re: Experimental patch: generating BKI revisited
Previous Message Greg Stark 2009-11-13 19:01:32 Re: Aggregate ORDER BY patch