Re: [HACKERS] Window functions patch v04 for the September commit fest

>> Also, current implementation has only a type of plan which uses sort >> operation. It should be optimized by re-position the windows and/or >> using hashtable. > > I would like to see some performance test results also. It would be good > to know whether they are fast/slow etc.. It will definite

Re: [HACKERS] Window functions patch v04 for the September commit fest

2008/9/5 Heikki Linnakangas <[EMAIL PROTECTED]>: > Heikki Linnakangas wrote: >> >> I'll review the parser/planner changes from the current patch. > > Looks pretty sane to me. Few issues: > > Is it always OK to share a window between two separate window function > invocations, if they both happen to

Re: [HACKERS] Window functions patch v04 for the September commit fest

Heikki Linnakangas wrote: I'll review the parser/planner changes from the current patch. Looks pretty sane to me. Few issues: Is it always OK to share a window between two separate window function invocations, if they both happen to have identical OVER clause? It seems OK for stable function

Re: [HACKERS] Window functions patch v04 for the September commit fest

Hitoshi Harada wrote: BTW, I think it is better to put together the discussion points we have done as "general roadmap to complete window functions". It is not about the features for the next release but is the complete tasks. Where to go? Wiki, or my design docs? That's up to you, really. I li

Re: [HACKERS] Window functions patch v04 for the September commit fest

2008/9/3 Heikki Linnakangas <[EMAIL PROTECTED]>: > Hitoshi Harada wrote: >> >>> I'd suggest: >>> >>> 1. Implement Window node, with the capability to invoke an aggregate >>> function, using the above API. Implement required parser/planner changes. >>> Implement a few simple ranking aggregates using

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Wed, 2008-09-03 at 09:51 +0300, Heikki Linnakangas wrote: > Simon Riggs wrote: > > On Tue, 2008-09-02 at 15:51 +0300, Heikki Linnakangas wrote: > > > >> The needs of access to the rows are so different that it seems best to > >> me to delegate the buffering to the window function. > > > > Tha

Re: [HACKERS] Window functions patch v04 for the September commit fest

Hitoshi Harada wrote: 2008/9/2 Heikki Linnakangas <[EMAIL PROTECTED]>: Hitoshi Harada wrote: 2008/9/2 Heikki Linnakangas <[EMAIL PROTECTED]>: In my understanding, the "Window Frame" is defined by clauses such like "ROWS BETWEEN ... ", "RANGE BETWEEN ... " or so, contrast to "Window Partition" d

Re: [HACKERS] Window functions patch v04 for the September commit fest

Simon Riggs wrote: On Tue, 2008-09-02 at 15:51 +0300, Heikki Linnakangas wrote: The needs of access to the rows are so different that it seems best to me to delegate the buffering to the window function. That seems sensible in some ways, not others. In the API I proposed later in that mail,

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Tue, 2008-09-02 at 15:51 +0300, Heikki Linnakangas wrote: > The needs of access to the rows are so different that it seems best to > me to delegate the buffering to the window function. That seems sensible in some ways, not others. Some of the window functions, like lead and lag merely speci

Re: [HACKERS] Window functions patch v04 for the September commit fest

2008/9/2 Heikki Linnakangas <[EMAIL PROTECTED]>: > Hitoshi Harada wrote: >> >> 2008/9/2 Heikki Linnakangas <[EMAIL PROTECTED]>: >> In my understanding, the "Window Frame" is defined >> by clauses such like "ROWS BETWEEN ... ", "RANGE BETWEEN ... " or so, >> contrast to "Window Partition" defined by

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Tue, Sep 2, 2008 at 9:35 AM, David Fetter <[EMAIL PROTECTED]> wrote: > Any chance we can buy a few copies of the official one for use on the > project? AFAIK there is no significant difference between the "official" standard and the draft version available online, so I don't see the point. Nei

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Tue, 2008-09-02 at 09:35 -0700, David Fetter wrote: > On Tue, Sep 02, 2008 at 12:42:45PM +0100, Simon Riggs wrote: > > > > On Tue, 2008-09-02 at 03:14 -0400, Tom Lane wrote: > > > David Fetter <[EMAIL PROTECTED]> writes: > > > > On Tue, Sep 02, 2008 at 02:42:25AM -0400, Tom Lane wrote: > > > >

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Tue, Sep 02, 2008 at 12:42:45PM +0100, Simon Riggs wrote: > > On Tue, 2008-09-02 at 03:14 -0400, Tom Lane wrote: > > David Fetter <[EMAIL PROTECTED]> writes: > > > On Tue, Sep 02, 2008 at 02:42:25AM -0400, Tom Lane wrote: > > >> It's not like we haven't seen a SQL draft go down in flames > > >>

Re: [HACKERS] Window functions patch v04 for the September commit fest

Martijn van Oosterhout wrote: On Tue, Sep 02, 2008 at 10:44:31AM +0100, Simon Riggs wrote: If we only have the combined (brain * time) to get a partial implementation in for this release then I would urge we go for that, rather than wait for perfection - as long as there are no other negative ef

Re: [HACKERS] Window functions patch v04 for the September commit fest

Hitoshi Harada wrote: 2008/9/2 Heikki Linnakangas <[EMAIL PROTECTED]>: In my understanding, the "Window Frame" is defined by clauses such like "ROWS BETWEEN ... ", "RANGE BETWEEN ... " or so, contrast to "Window Partition" defined by "PARTITION BY" clause. A frame slides within a partition or the

Re: [HACKERS] Window functions patch v04 for the September commit fest

2008/9/2 Simon Riggs <[EMAIL PROTECTED]>: > If you've done all of that, then I'm impressed. Well done. > > Few general comments > > * The docs talk about "windowing functions", yet you talk about "window > functions" here. I think the latter is correct, but whichever we choose > we should be consis

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Tue, 2008-09-02 at 03:14 -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Tue, Sep 02, 2008 at 02:42:25AM -0400, Tom Lane wrote: > >> It's not like we haven't seen a SQL draft go down in flames before. > > > Do you think that anything in the windowing functions section

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Tue, Sep 02, 2008 at 10:44:31AM +0100, Simon Riggs wrote: > If we only have the combined (brain * time) to get a partial > implementation in for this release then I would urge we go for that, > rather than wait for perfection - as long as there are no other negative > effects. "premature optimi

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Mon, 2008-09-01 at 21:00 +0300, Heikki Linnakangas wrote: > 1. It's important that what gets committed now can be extended to handle > all of the window function stuff in SQL2003 in the future, as well as > user-defined-window-functions in the spirit of PostgreSQL extensibility. > Even if w

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Sat, 2008-08-30 at 02:04 +0900, Hitoshi Harada wrote: > Here's the latest window functions patch against HEAD. It seems to be > ready for the September commit fest, as added documents, WINDOW clause > feature and misc tests. I guess this would be the window functions > feature freeze for 8.4.

Re: [HACKERS] Window functions patch v04 for the September commit fest

2008/9/2 Heikki Linnakangas <[EMAIL PROTECTED]>: > Gregory Stark wrote: >> What would the executor do for a query like >> >> SELECT lead(x,1),lead(y,2),lead(y,3) >> >> It would not only have to keep a tuplestore to buffer the output but it >> would >> have to deal with receiving data from different

Re: [HACKERS] Window functions patch v04 for the September commit fest

Tom Lane wrote: David Fetter <[EMAIL PROTECTED]> writes: On Tue, Sep 02, 2008 at 02:42:25AM -0400, Tom Lane wrote: It's not like we haven't seen a SQL draft go down in flames before. Do you think that anything in the windowing functions section will disappear? Who's to say? I have no obje

Re: [HACKERS] Window functions patch v04 for the September commit fest

David Fetter <[EMAIL PROTECTED]> writes: > On Tue, Sep 02, 2008 at 02:42:25AM -0400, Tom Lane wrote: >> It's not like we haven't seen a SQL draft go down in flames before. > Do you think that anything in the windowing functions section will > disappear? Who's to say? I have no objection to looki

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Tue, Sep 02, 2008 at 02:42:25AM -0400, Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > > David Fetter wrote: > >> On Mon, Sep 01, 2008 at 09:00:47PM +0300, Heikki Linnakangas wrote: > >>> Ok, I'm starting to read up on SQL2003 window functions, > >> > >> Maybe it would be bet

Re: [HACKERS] Window functions patch v04 for the September commit fest

Gregory Stark wrote: Heikki Linnakangas <[EMAIL PROTECTED]> writes: sfunc is called once for each input row. Unlike with normal aggregates, sfunc is passed the whole input row, so that e.g RANK can compare it against the previous row, or LEAD can buffer it. I'm not sure I follow this bit abou

Re: [HACKERS] Window functions patch v04 for the September commit fest

Heikki Linnakangas <[EMAIL PROTECTED]> writes: > David Fetter wrote: >> On Mon, Sep 01, 2008 at 09:00:47PM +0300, Heikki Linnakangas wrote: >>> Ok, I'm starting to read up on SQL2003 window functions, >> >> Maybe it would be better to read the SQL2008 standard >> :

Re: [HACKERS] Window functions patch v04 for the September commit fest

David Fetter wrote: On Mon, Sep 01, 2008 at 09:00:47PM +0300, Heikki Linnakangas wrote: Ok, I'm starting to read up on SQL2003 window functions, Maybe it would be better to read the SQL2008 standard :) Ah, thanks! -- Heikki Linnakangas EnterpriseDB htt

Re: [HACKERS] Window functions patch v04 for the September commit fest

2008/9/2 Gregory Stark <[EMAIL PROTECTED]>: > And it doesn't answer how to deal with things like > > SELECT lag(x,1) OVER (ORDER BY a), lag(x,1) OVER (ORDER BY b) > > I, uh, don't actually have any ideas of how to deal with that one :( For different windows we make different window nodes with diff

Re: [HACKERS] Window functions patch v04 for the September commit fest

Thanks for comments, 2008/9/2 Heikki Linnakangas <[EMAIL PROTECTED]>: > > Ok, I'm starting to read up on SQL2003 window functions, and to review this > patch. Here's a long brain-dump of my first thoughts on the design. > > Let's list a couple of requirements first: > > 1. It's important that what

Re: [HACKERS] Window functions patch v04 for the September commit fest

Heikki Linnakangas <[EMAIL PROTECTED]> writes: > sfunc is called once for each input row. Unlike with normal aggregates, sfunc > is passed the whole input row, so that e.g RANK can compare it against the > previous row, or LEAD can buffer it. I'm not sure I follow this bit about being passed the

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Mon, Sep 01, 2008 at 09:00:47PM +0300, Heikki Linnakangas wrote: > Hitoshi Harada wrote: >> 2008/8/30 Hitoshi Harada <[EMAIL PROTECTED]>: >>> Here's the latest window functions patch against HEAD. It seems to be >>> ready for the September commit fest, as added documents, WINDOW clause >>> featu

Re: [HACKERS] Window functions patch v04 for the September commit fest

Hitoshi Harada wrote: 2008/8/30 Hitoshi Harada <[EMAIL PROTECTED]>: Here's the latest window functions patch against HEAD. It seems to be ready for the September commit fest, as added documents, WINDOW clause feature and misc tests. I guess this would be the window functions feature freeze for 8

Re: [HACKERS] Window functions patch v04 for the September commit fest

On Mon, Sep 01, 2008 at 12:15:19PM +0900, Hitoshi Harada wrote: > README is updated. > http://umitanuki.net/pgsql/wfv04/design.html > > Please add link to commit fest wiki. Added :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Ya

Re: [HACKERS] Window functions patch v04 for the September commit fest

2008/8/30 Hitoshi Harada <[EMAIL PROTECTED]>: > Here's the latest window functions patch against HEAD. It seems to be > ready for the September commit fest, as added documents, WINDOW clause > feature and misc tests. I guess this would be the window functions > feature freeze for 8.4. The remaining