Thread: window function v03 against HEAD
I happily announce that the first design of window function was finished and the patch against HEAD is released online. See http://umitanuki.net/pgsql/wfv03/design.html The window functions such like row_number(), rank(), dense_rank(), percent_rank() are experimentally defined within the patch as * It is formed as an aggregate function, seen in pg_aggregate * It does or does not have trans function. * It has a final function that is declared as VOLATILE. * Its final function is called multiple times during returning values. And ranking system is a bit ugly. The problem was how you know the ranking boundary. Inside rank_final(), you see WindowState node is pulled out from fcinfo->context and TupleTableSlot is used. Actually I am not sure if this is appropriate but have tried it, which did work. So as the first release, I guess it is better that the window functions are formulated but not open as user-defined functions. Also, since current design of the window functions is by aggregate, some of the SQL spec functions such like lag()/lead() was dropped. These functions need to reach for random rows from current row. For this needs, we might have to provide something like Window Object mechanism that allows random access to the current frame. There's no additional docs and tests, as well as lack of comments in source code. The "make check" will fail because some of the window functions do not have trans function. Reviews and comments are welcome. I'm eager to refine it. Regards, -- Hitoshi Harada
Attachment
On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote: > I happily announce that the first design of window function was > finished and the patch against HEAD is released online. See > http://umitanuki.net/pgsql/wfv03/design.html I've put up a git repository at <http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary> Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
2008/7/29 David Fetter <david@fetter.org>: > On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote: >> I happily announce that the first design of window function was >> finished and the patch against HEAD is released online. See >> http://umitanuki.net/pgsql/wfv03/design.html > > I've put up a git repository at > <http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary> > Thanks a lot. I have tried to get clone from the URL but it didn't work. $ git-clone http://git.postgresql.org/git/~davidfetter/window_functions/.git Initialized empty Git repository in /home/forcia/repo/window_functions/.git/ Getting alternates list for http://git.postgresql.org/git/~davidfetter/window_functions/.git Getting pack list for http://git.postgresql.org/git/~davidfetter/window_functions/.git Getting index for pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 Getting pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555which contains c13ba377a6e58a05b5d9e39e36674af42126d48d here it stops and tells nothing. This occurs on both Linux and Windows clients. I succeeded on getting git://git.postgresql.org/git/postgresql.git. I am quite new to git so if you know something please point me out. Regards, -- Hitoshi Harada
On Thu, Jul 31, 2008 at 02:12:26PM +0900, Hitoshi Harada wrote: > 2008/7/29 David Fetter <david@fetter.org>: > > On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote: > >> I happily announce that the first design of window function was > >> finished and the patch against HEAD is released online. See > >> http://umitanuki.net/pgsql/wfv03/design.html > > > > I've put up a git repository at > > <http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary> > > Thanks a lot. > I have tried to get clone from the URL but it didn't work. > > $ git-clone http://git.postgresql.org/git/~davidfetter/window_functions/.git > Initialized empty Git repository in /home/forcia/repo/window_functions/.git/ > Getting alternates list for > http://git.postgresql.org/git/~davidfetter/window_functions/.git > Getting pack list for > http://git.postgresql.org/git/~davidfetter/window_functions/.git > Getting index for pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 > Getting pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 > which contains c13ba377a6e58a05b5d9e39e36674af42126d48d > > here it stops and tells nothing. This occurs on both Linux and > Windows clients. How long does it hang for? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
2008/7/31 David Fetter <david@fetter.org>: > On Thu, Jul 31, 2008 at 02:12:26PM +0900, Hitoshi Harada wrote: >> 2008/7/29 David Fetter <david@fetter.org>: >> > On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote: >> >> I happily announce that the first design of window function was >> >> finished and the patch against HEAD is released online. See >> >> http://umitanuki.net/pgsql/wfv03/design.html >> > >> > I've put up a git repository at >> > <http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary> >> >> Thanks a lot. >> I have tried to get clone from the URL but it didn't work. >> >> $ git-clone http://git.postgresql.org/git/~davidfetter/window_functions/.git >> Initialized empty Git repository in /home/forcia/repo/window_functions/.git/ >> Getting alternates list for >> http://git.postgresql.org/git/~davidfetter/window_functions/.git >> Getting pack list for >> http://git.postgresql.org/git/~davidfetter/window_functions/.git >> Getting index for pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 >> Getting pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 >> which contains c13ba377a6e58a05b5d9e39e36674af42126d48d >> >> here it stops and tells nothing. This occurs on both Linux and >> Windows clients. > > How long does it hang for? > Sorry, finally I got it. It took about an hour... -- Hitoshi Harada
On Thu, Jul 31, 2008 at 02:46:29PM +0900, Hitoshi Harada wrote: > 2008/7/31 David Fetter <david@fetter.org>: > > On Thu, Jul 31, 2008 at 02:12:26PM +0900, Hitoshi Harada wrote: > >> 2008/7/29 David Fetter <david@fetter.org>: > >> > On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote: > >> >> I happily announce that the first design of window function was > >> >> finished and the patch against HEAD is released online. See > >> >> http://umitanuki.net/pgsql/wfv03/design.html > >> > > >> > I've put up a git repository at > >> > <http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary> > >> > >> Thanks a lot. > >> I have tried to get clone from the URL but it didn't work. > >> > >> $ git-clone http://git.postgresql.org/git/~davidfetter/window_functions/.git > >> Initialized empty Git repository in /home/forcia/repo/window_functions/.git/ > >> Getting alternates list for > >> http://git.postgresql.org/git/~davidfetter/window_functions/.git > >> Getting pack list for > >> http://git.postgresql.org/git/~davidfetter/window_functions/.git > >> Getting index for pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 > >> Getting pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 > >> which contains c13ba377a6e58a05b5d9e39e36674af42126d48d > >> > >> here it stops and tells nothing. This occurs on both Linux and > >> Windows clients. > > > > How long does it hang for? > > > > Sorry, finally I got it. It took about an hour... Sorry about that. Apparently, at least the way things are set up, there's a *lot* of history you can rewind. Further changes should move pretty quickly :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
2008/7/31 David Fetter <david@fetter.org>: > On Thu, Jul 31, 2008 at 02:46:29PM +0900, Hitoshi Harada wrote: >> 2008/7/31 David Fetter <david@fetter.org>: >> > On Thu, Jul 31, 2008 at 02:12:26PM +0900, Hitoshi Harada wrote: >> >> 2008/7/29 David Fetter <david@fetter.org>: >> >> > On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote: >> >> >> I happily announce that the first design of window function was >> >> >> finished and the patch against HEAD is released online. See >> >> >> http://umitanuki.net/pgsql/wfv03/design.html >> >> > >> >> > I've put up a git repository at >> >> > <http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary> >> >> >> >> Thanks a lot. >> >> I have tried to get clone from the URL but it didn't work. >> >> >> >> $ git-clone http://git.postgresql.org/git/~davidfetter/window_functions/.git >> >> Initialized empty Git repository in /home/forcia/repo/window_functions/.git/ >> >> Getting alternates list for >> >> http://git.postgresql.org/git/~davidfetter/window_functions/.git >> >> Getting pack list for >> >> http://git.postgresql.org/git/~davidfetter/window_functions/.git >> >> Getting index for pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 >> >> Getting pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 >> >> which contains c13ba377a6e58a05b5d9e39e36674af42126d48d >> >> >> >> here it stops and tells nothing. This occurs on both Linux and >> >> Windows clients. >> > >> > How long does it hang for? >> > >> >> Sorry, finally I got it. It took about an hour... > > Sorry about that. Apparently, at least the way things are set up, > there's a *lot* of history you can rewind. Further changes should > move pretty quickly :) > > Cheers, > David. > -- > David Fetter <david@fetter.org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@gmail.com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > Thankfully, I succeeded to learn about git and started to use it on my local. Then I read through the -hackers list about it but some points are still not clear. - Am I eligible to push git.postgresql.org/git/~davidfetter/window_functions? - Or to get qualified do I need to claim? - The repositories on git.postgresql.org need reviewing for new push/pull or is it free to push there? How is the developing flow? I just got some message when tried pushing: error: Cannot access URL http://git.postgresql.org/git/~davidfetter/window_functions/.git/, return code 22 error: failed to push some refs to 'http://git.postgresql.org/git/~davidfetter/window_functions/.git' If possible, I would like an account to push for me. Regards, -- Hitoshi Harada
On Thu, Jul 31, 2008 at 11:00:15PM +0900, Hitoshi Harada wrote: > 2008/7/31 David Fetter <david@fetter.org>: > > > > Sorry about that. Apparently, at least the way things are set up, > > there's a *lot* of history you can rewind. Further changes should > > move pretty quickly :) > > Thankfully, I succeeded to learn about git and started to use it on > my local. Then I read through the -hackers list about it but some > points are still not clear. I guess we'll all learn at once :) > - Am I eligible to push > git.postgresql.org/git/~davidfetter/window_functions? You will be as soon as I can arrange it. I may move or re-create that repository. Please send me a username and an RSA public key so I can give you git-shell access. > - Or to get qualified do I need to claim? > - The repositories on git.postgresql.org need reviewing for new > push/pull or is it free to push there? How is the developing flow? Working on that :) > I just got some message when tried pushing: > > error: Cannot access URL > http://git.postgresql.org/git/~davidfetter/window_functions/.git/, > return code 22 > error: failed to push some refs to > 'http://git.postgresql.org/git/~davidfetter/window_functions/.git' > > If possible, I would like an account to push for me. Right. I will start that this afternoon, PDT. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
2008/8/1 David Fetter <david@fetter.org>: > You will be as soon as I can arrange it. I may move or re-create that > repository. Please send me a username and an RSA public key so I can > give you git-shell access. Thank you for your consideration. But right now, I'm going to be off for three days or so. As soon as coming back, I'll send it to you. Regards, -- Hitoshi Harada
On Fri, Aug 01, 2008 at 02:13:52AM +0900, Hitoshi Harada wrote: > 2008/8/1 David Fetter <david@fetter.org>: > > You will be as soon as I can arrange it. I may move or re-create > > that repository. Please send me a username and an RSA public key > > so I can give you git-shell access. > > Thank you for your consideration. But right now, I'm going to be off > for three days or so. As soon as coming back, I'll send it to you. Enjoy your days off :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
2008/8/1 David Fetter <david@fetter.org>: > On Thu, Jul 31, 2008 at 11:00:15PM +0900, Hitoshi Harada wrote: >> 2008/7/31 David Fetter <david@fetter.org>: >> > >> > Sorry about that. Apparently, at least the way things are set up, >> > there's a *lot* of history you can rewind. Further changes should >> > move pretty quickly :) >> >> Thankfully, I succeeded to learn about git and started to use it on >> my local. Then I read through the -hackers list about it but some >> points are still not clear. > > I guess we'll all learn at once :) > >> - Am I eligible to push >> git.postgresql.org/git/~davidfetter/window_functions? > > You will be as soon as I can arrange it. I may move or re-create that > repository. Please send me a username and an RSA public key so I can > give you git-shell access. I've pushed my work into David's git hosting. http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary The DISTNCT fix which overlaps much of my patch were merged. And some bugs were fixed while adding regression test for window functions. Documantation is still out of order, because some points are uncertain, including window function design. Up to now, I started to think about new pg catalog for them (e.g. pg_wfunc). If anyone is interested this patch please clone source tree from git.postgresql.org and send me patch along with comments. Regards, -- Hitoshi Harada