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

2008-08-29 Thread Hitoshi Harada
of the specification. 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. Oh, git hosting repository is updated as well. Regards, -- Hitoshi Harada window_functions.patch.20080830.gz

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

2008-08-31 Thread Hitoshi Harada
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

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

2008-09-01 Thread Hitoshi Harada
a glance but not deeply, since I found I would be lost in design if deeply diving into it. :) -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

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

2008-09-01 Thread Hitoshi Harada
sort nodes as the patch does. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

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

2008-09-02 Thread Hitoshi Harada
than one window function. If it is thought to be the same using equalFuncs comparing targetlists, some functions are put into one node. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

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

2008-09-02 Thread Hitoshi Harada
in a week or so. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

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

2008-09-02 Thread Hitoshi Harada
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 PARTITION BY clause

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

2008-09-03 Thread Hitoshi Harada
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 the API. 2

Re: [HACKERS] Window functions doc patch

2008-09-03 Thread Hitoshi Harada
Ah, thanks. It's my mistake. Both of SQL spec and my implementation in pg_proc say it is row_number, not rownumber. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [HACKERS] NDirectFileRead and Write

2008-09-06 Thread Hitoshi Harada
of these variables should be defined together but actually you put the two in buffile.h while the others in buf_iinnternals.h. Is there clear reason for that? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

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

2008-09-06 Thread Hitoshi Harada
the same ordering window. /---quote--- But it doesn't say anything about volatile functions. Do you have example that is bad with the current design? The other issuses are OK. I missed those cases. will fix them. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers

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

2008-09-09 Thread Hitoshi Harada
bench1 INNER JOIN(SELECT sector, avg(amount) FROM bench1 GROUP BY sector)t USING(sector) I'll include this test in my docs later. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

2008-09-30 Thread Hitoshi Harada
). Instead, I decided it'd better that we can indicate the row number by integer. Regards, -- Hitoshi Harada *** a/src/backend/utils/sort/tuplestore.c --- b/src/backend/utils/sort/tuplestore.c *** *** 141,148 struct Tuplestorestate --- 141,159 int markpos_current; /* saved

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

2008-10-01 Thread Hitoshi Harada
window function's sliding frame. But it isn't really critical, just performance matter. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

2008-10-01 Thread Hitoshi Harada
2008/10/2 Tom Lane [EMAIL PROTECTED]: Hitoshi Harada [EMAIL PROTECTED] writes: I hadn't realized that this would be relevant to window functions. Now that I know that, I propose fixing tuplestore for multiple positions and committing it separately, before I go back to the CTE patch

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

2008-10-02 Thread Hitoshi Harada
2008/10/2 Greg Stark [EMAIL PROTECTED]: On 2 Oct 2008, at 05:44 AM, Tom Lane [EMAIL PROTECTED] wrote: Hitoshi Harada [EMAIL PROTECTED] writes: Hmm, I've looked over the patch. Logically window functions can access arbitrary rows that have been stored in a frame. Thus I had thought

Re: [HACKERS] Window Functions patch v06

2008-10-11 Thread Hitoshi Harada
(this is a Japanese traditional business sense -:). I don't know how much I can work for the next commit fest but I can't believe it if this feature would be missed in 8.4 *only* for my own tiny business clients! Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org

Re: [HACKERS] Window Functions patch v06

2008-10-11 Thread Hitoshi Harada
I am drunk. I forgot cc to -hackers. The talk between me and Ian was like that. 2008/10/12 Hitoshi Harada [EMAIL PROTECTED]: 2008/10/12 Ian Caulfield [EMAIL PROTECTED]: 2008/10/11 Hitoshi Harada [EMAIL PROTECTED]: 2008/10/12 Ian Caulfield [EMAIL PROTECTED]: 2008/10/11 Hitoshi Harada [EMAIL

Re: [HACKERS] Window Functions patch v06

2008-10-12 Thread Hitoshi Harada
find how to do it with the new (window execution model) design, (and the design is suitable to fix it above,) but at first before going into trivial specs, I would like core hackers to review the model is better than before or not. Thank you for your cooperation. Regards, -- Hitoshi Harada

Re: [HACKERS] SELECT TOP %d PERCENT, or SELECT ... LIMIT %d PERCENT ?

2008-10-12 Thread Hitoshi Harada
out. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SELECT TOP %d PERCENT, or SELECT ... LIMIT %d PERCENT ?

2008-10-12 Thread Hitoshi Harada
determine the percent. Better than count(*) of course, but I'm not sure it meets his needs. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SELECT TOP %d PERCENT, or SELECT ... LIMIT %d PERCENT ?

2008-10-12 Thread Hitoshi Harada
2008/10/13 Grzegorz Jaskiewicz [EMAIL PROTECTED]: On 2008-10-12, at 16:22, Hitoshi Harada wrote: 2008/10/12 Robert Haas [EMAIL PROTECTED]: I wonder if this could be implemented using the window-function infrastructure... Yeah, actually window functions I am working now has percent_rank

Re: [HACKERS] Window Functions patch v06

2008-10-13 Thread Hitoshi Harada
2008/10/14 David Rowley [EMAIL PROTECTED]: Hitoshi Harada wrote: I made up my mind to scratch former window functions and redesigned completely new execution model, based on the discussion with Heikki. Attached is the v06 against HEAD today. http://umitanuki.net/pgsql/wfv06/design.html First

Re: [HACKERS] Window Functions

2008-10-14 Thread Hitoshi Harada
see all the rows to determine the maximum of row_number() so it's not so efficiently as you feel but I beleive (and hope) it is elegant enough and perform well considerablely. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

[HACKERS] Window Functions: buffering strategy

2008-10-19 Thread Hitoshi Harada
guess I know what to do but I don't know how to do it :) But I am going to send another patch until next commit fest. I appreciate for you comments. Regeards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] Window Functions: buffering strategy

2008-10-20 Thread Hitoshi Harada
Hi, 2008/10/20 Simon Riggs [EMAIL PROTECTED]: On Mon, 2008-10-20 at 10:32 +0900, Hitoshi Harada wrote: So I propose three Window node buffering strategies, row/frame/partition buffering so as to avoid unnecessary row buffering. Sounds good from here. Can I suggest you release the code

Re: [HACKERS] Window Functions: buffering strategy

2008-10-20 Thread Hitoshi Harada
2008/10/20 Heikki Linnakangas [EMAIL PROTECTED]: Hitoshi Harada wrote: Hi, 2008/10/20 Simon Riggs [EMAIL PROTECTED]: On Mon, 2008-10-20 at 10:32 +0900, Hitoshi Harada wrote: So I propose three Window node buffering strategies, row/frame/partition buffering so as to avoid unnecessary row

Re: [HACKERS] Window Functions: buffering strategy

2008-10-20 Thread Hitoshi Harada
2008/10/21 Heikki Linnakangas [EMAIL PROTECTED]: Hitoshi Harada wrote: The real problem is not how to cut off preceding rows, but how to read ahead after the current row. I intend to avoid reading ahead until end of the partition for only row_number() that doesn't need any following rows

Re: [HACKERS] SE-PostgreSQL wiki updates, but ...

2008-10-21 Thread Hitoshi Harada
://www.postgresql.org/community/signup and with that account the wiki is editable. I guess it might be better if there is more public announcement to help developers' activities. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

[HACKERS] Window Functions: v07 APIs and buffering strateties

2008-10-27 Thread Hitoshi Harada
the window functions cannot be defined as user function now. Comments, feedbacks? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Window Functions: v07 APIs and buffering strateties

2008-10-27 Thread Hitoshi Harada
2008/10/28 David Rowley [EMAIL PROTECTED]: Hitoshi Harada Wrote: 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

Re: [HACKERS] Window Functions: v07 APIs and buffering strateties

2008-10-27 Thread Hitoshi Harada
Thanks for your testing all! 2008/10/28 ITAGAKI Takahiro [EMAIL PROTECTED]: Hitoshi Harada [EMAIL PROTECTED] wrote: As I promised, version 7 of the window functions is now released. patch: http://umitanuki.net/pgsql/window_functions.patch.20081028.gz I tested the patch on mingw (Windows

Re: [HACKERS] Window Functions: v07 APIs and buffering strateties

2008-10-28 Thread Hitoshi Harada
2008/10/28 Hitoshi Harada [EMAIL PROTECTED]: Thanks for your testing all! 2008/10/28 ITAGAKI Takahiro [EMAIL PROTECTED]: Hitoshi Harada [EMAIL PROTECTED] wrote: select relid,AVG(seq_Scan) OVER (ORDER BY relid) FROM pg_stat_user_tables GROUP BY relid,seq_scan; crashes with segfault. I

Re: [HACKERS] Window Functions: v07 APIs and buffering strateties

2008-10-28 Thread Hitoshi Harada
2008/10/28 ITAGAKI Takahiro [EMAIL PROTECTED]: Hitoshi Harada [EMAIL PROTECTED] wrote: And I fixed this problem, confirming with/without debug/cassert/gcc -O and push it to git. If you want delta patch, please see http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=commitdiff

Re: [HACKERS] Window Functions: v07 APIs and buffering strateties

2008-10-28 Thread Hitoshi Harada
2008/10/28 Tom Lane [EMAIL PROTECTED]: Hitoshi Harada [EMAIL PROTECTED] writes: 2008/10/28 ITAGAKI Takahiro [EMAIL PROTECTED]: I tested the patch on mingw (Windows) and got the following warning and error: A. gram.y: conflicts: 3 shift/reduce B. include/nodes/plannodes.h:650: error: syntax

Re: [HACKERS] Window Functions: v07 APIs and buffering strateties

2008-10-28 Thread Hitoshi Harada
2008/10/28 Tom Lane [EMAIL PROTECTED]: Hitoshi Harada [EMAIL PROTECTED] writes: OK, I'll try to remove it. I'm not used to bison so my first task is to find where the conflict is... Use bison -v to get details of where the conflict is. I find that the most common way to fix things

Re: [HACKERS] Window Functions: v07 APIs and buffering strateties

2008-10-28 Thread Hitoshi Harada
2008/10/29 Tom Lane [EMAIL PROTECTED]: Hitoshi Harada [EMAIL PROTECTED] writes: Can ROWS be reserved_keyword? In window specifications, we have OVER (ORDER BY expr_list [(ROWS|RANGE) ... ]) and currently ROWS is not reserved so bison is confused with cases of ROWS included in expr_list

Re: [HACKERS] Window Functions: v07 APIs and buffering strateties

2008-10-28 Thread Hitoshi Harada
CURRENT and unsigned constant though. Still the phrasing approach doesn't seem less hope? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Window Functions: patch for CommitFest:Nov.

2008-10-31 Thread Hitoshi Harada
it will come to 8.4. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Window Functions: patch for CommitFest:Nov.

2008-10-31 Thread Hitoshi Harada
2008/11/1 David Fetter [EMAIL PROTECTED]: On Fri, Oct 31, 2008 at 01:00:38PM -0300, Alvaro Herrera wrote: Hitoshi Harada escribió: [Patch itself] http://umitanuki.net/pgsql/wfv08/window_functions.patch.20081031.gz Please send the patch to the pgsql-hackers list too. That way we

Re: [HACKERS] Window Functions: patch for CommitFest:Nov.

2008-10-31 Thread Hitoshi Harada
2008/11/1 Tom Lane [EMAIL PROTECTED]: Gregory Stark [EMAIL PROTECTED] writes: Hitoshi Harada [EMAIL PROTECTED] writes: 2008/11/1 David Fetter [EMAIL PROTECTED]: I've ever sent a patch over 100k and failed. Actually how much is the limitation of the patch size? And if the patch is too huge

Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.

2008-11-01 Thread Hitoshi Harada
; -- windowing SELECT id, grp, row_number() OVER (PARTITION grp ORDER BY id) FROM t; Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.

2008-11-03 Thread Hitoshi Harada
2008/11/2 David Rowley [EMAIL PROTECTED]: Hitoshi Harada Wrote: 2008/11/2 David Rowley [EMAIL PROTECTED]: Obervations: Test 3 and 5 did not seem to make use of an index to get a sorted list of results. I disabled enable_seqscan but the planner still failed to choose index_scan

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-04 Thread Hitoshi Harada
, as Oracle and Sybase say. Even though I understand the definition, your suggestion of COUNT(*) OVER (ORDER BY salary) doesn't make sense. In the patch, it simply returns the same value as row_number() but is it wrong, too? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-04 Thread Hitoshi Harada
it means RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW as you pointed. And the result of count(*) OVER (ORDER BY salary) doesn't equal to row_number(). Now my assumption is broken. Let me take time to think about how to solve it... Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-08 Thread Hitoshi Harada
2008/11/9 David Rowley [EMAIL PROTECTED]: Hitoshi Harada Wrote: although attached is the whole (split) patch. I'm having some trouble getting these patches to patch cleanly. I think it's because of this that I can't get postgres to compile after applying the patch. It errors out

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-09 Thread Hitoshi Harada
2008/11/9 David Rowley [EMAIL PROTECTED]: Hitoshi Harada wrote: I recreate the patch against current HEAD, in the git it's here: http://git.postgresql.org/?p=postgresql.git;a=commit;h=f88970d3c6fb9f99543 d873bb7228f4c057c23e0 I tested `patch -p1` with the attached and succeeded to make

Re: [HACKERS] Windowing Function Patch Review - ROW_NUMBER without ORDER BY

2008-11-09 Thread Hitoshi Harada
. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.

2008-11-10 Thread Hitoshi Harada
2008/11/10 David Rowley [EMAIL PROTECTED]: Hitoshi Harada wrote: I found how to do it, though it's only on the case you gave. Thinking about the planner optimization of the Window nodes (and its attached Sort nodes), we must consider the execution order of more than one node. In the test case

Re: [HACKERS] Windowing Function Patch Review - NTILE function

2008-11-10 Thread Hitoshi Harada
null case to return null. will be fixed soon. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Windowing Function Patch Review - NTH_VALUE

2008-11-10 Thread Hitoshi Harada
) returns the second. If there're no objections I will change it as you told. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Windowing Function Patch Review - NTH_VALUE

2008-11-11 Thread Hitoshi Harada
2008/11/10 Hitoshi Harada [EMAIL PROTECTED]: 2008/11/9 David Rowley [EMAIL PROTECTED]: I'm having a little trouble understanding the standard for NTH_VALUE(). I would have assumed that NTH_VALUE(name,1) would return the first name in the window. The current patch is using 0 for the first

Re: [HACKERS] Window functions review

2008-11-12 Thread Hitoshi Harada
require frame buffering but RANGE BETEWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (that is OVER() ) needs it. You're telling me to switch the strategy depending on cases? Hmmm, let me see. -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.

2008-11-16 Thread Hitoshi Harada
2008/11/15 David Rowley [EMAIL PROTECTED]: Hitoshi Harada wrote: david=# explain select date,lag(date,1) over (order by date) from meter_Readings order by date; QUERY PLAN

Re: [HACKERS] Window functions review

2008-11-21 Thread Hitoshi Harada
such like what David reported. Feel free to ask me about any cut of the patch. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-24 Thread Hitoshi Harada
2008/11/24 Heikki Linnakangas [EMAIL PROTECTED]: Hitoshi Harada wrote: 2008/11/20 David Rowley [EMAIL PROTECTED]: I won't be around until Monday evening (Tuesday morning JST). I'll pickup the review again there. It's really time for me to push on with this review. The patch is getting

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-24 Thread Hitoshi Harada
2008/11/25 Heikki Linnakangas [EMAIL PROTECTED]: Hitoshi Harada wrote: If you keep this in your mind, please don't be annoyed but the current frame concept is wrong. ... Note that on empno=4 then last_value=4(yours)/3(mine), which means the frame is applied to last_value() as well

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-25 Thread Hitoshi Harada
2008/11/26 David Rowley [EMAIL PROTECTED]: Hitoshi Harada wrote: 2008/11/20 David Rowley [EMAIL PROTECTED]: -- The following query gives incorrect results on the -- maxhighbid column SELECT auctionid, category, description, highestbid, reserve, MAX

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-25 Thread Hitoshi Harada
2008/11/25 Hitoshi Harada [EMAIL PROTECTED]: 2008/11/25 Heikki Linnakangas [EMAIL PROTECTED]: Here's an updated patch, where the rows are fetched on-demand. Good! And I like the fetching args by number better. Let me take more time to look in detail... I read more, and your spooling approach

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-26 Thread Hitoshi Harada
2008/11/26 Heikki Linnakangas [EMAIL PROTECTED]: Hitoshi Harada wrote: 2008/11/26 David Rowley [EMAIL PROTECTED]: I'm at a bit of a loss to what to do now. Should I wait for your work Heikki? Or continue validating this patch? The best thing I can think to do right now is continue and any

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-26 Thread Hitoshi Harada
2008/11/26 Heikki Linnakangas [EMAIL PROTECTED]: Hitoshi Harada wrote: I read more, and your spooling approach seems flexible for both now and the furture. Looking at only current release, the frame with ORDER BY is done by detecting peers in WinFrameGetArg() and add row number of peers

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-11-26 Thread Hitoshi Harada
no doubt. Particularly some of the planner part probably makes wrong. Thank you for your check. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-12-02 Thread Hitoshi Harada
2008/12/2 Hitoshi Harada [EMAIL PROTECTED]: sample=# EXPLAIN ANALYZE SELECT LEAD(timestamp) OVER (ORDER BY id) FROM bigtable LIMIT 1; QUERY PLAN

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-12-02 Thread Hitoshi Harada
2008/11/26 Heikki Linnakangas [EMAIL PROTECTED]: Hitoshi Harada wrote: I read more, and your spooling approach seems flexible for both now and the furture. Looking at only current release, the frame with ORDER BY is done by detecting peers in WinFrameGetArg() and add row number of peers

[HACKERS] tuplestore potential performance problem

2008-12-03 Thread Hitoshi Harada
similar logic. Doesn't CTE have any problem for large data set? Regards, *1:http://archives.postgresql.org/pgsql-hackers/2008-12/msg00077.php -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [HACKERS] tuplestore potential performance problem

2008-12-03 Thread Hitoshi Harada
I don't have real evidence but reasoned it. No strace was done. So it may not be cased by flushing out but this commit gets performance quite better, to earlier patch performance, around 44sec from around 76sec. Oh, I mean, 116sec to 44sec. -- Hitoshi Harada -- Sent via pgsql-hackers

Re: [HACKERS] tuplestore potential performance problem

2008-12-03 Thread Hitoshi Harada
was buffering all the table and the newer Heikki's approach was buffer on row by row while reading. The newest is buffering row by row while reading during in memory, and holding all the remaining tuples before reading after out to file, something like hybrid method. Regards, -- Hitoshi Harada -- Sent via

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-12-07 Thread Hitoshi Harada
, thanks, applied to my patch. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Windowing Function Patch Review - Standard Conformance

2008-12-07 Thread Hitoshi Harada
2008/12/6 David Rowley [EMAIL PROTECTED]: Hitoshi Harada Wrote: 2008/12/3 Hitoshi Harada [EMAIL PROTECTED]: I am randomly trying some issues instead of agg common code (which I now doubt if it's worth sharing the code), so tell me if you're restarting your hack again. I'll send the whole

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-11-14 Thread Hitoshi Harada
, was assuming a constant for unsigned value specification which actually includes any expression. But it's a fixed value during execution, right? Otherwise, we cannot predicate frame boundary. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-11-14 Thread Hitoshi Harada
in the executor. Thanks for this comment. I hadn't determined which node should be used as a value node passed to executor. Including Tom's comment, I must consider which should be again. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

[HACKERS] NULL input for array_agg()?

2009-11-14 Thread Hitoshi Harada
. == /snip == ... and array_agg() is among the functions that description above refers to. So I wonder if array_agg doesn't accept NULL input (with STRICT trans function). Did we discussed about this issue when implementing it for 8.4? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-11-14 Thread Hitoshi Harada
or a more complex expression. Yeah, so that we allow something like ROWS BETWEEN 1 + $1 PRECEDING AND ... And to support RANGE BETWEEN n PRECEDING ..., we should make datum to add or to subtract from current value on initial call anyway. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing

Re: [HACKERS] NULL input for array_agg()?

2009-11-15 Thread Hitoshi Harada
2009/11/15 Andrew Gierth and...@tao11.riddles.org.uk: Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes:  Hitoshi Hi, During reviewing aggregates ORDER BY, I was reading spec  Hitoshi and found description like:  Hitoshi == snip ==  Hitoshi Of the rows in the aggregation, the following

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-15 Thread Hitoshi Harada
pstate to transform clauses and I felt a bit fear. - SortGroupClause.implicit implicit member was added in SortGroupClause. I didn't find clear reason to add this. Could you show a case to clarify this? That's it for now. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-15 Thread Hitoshi Harada
2009/11/16 Andrew Gierth and...@tao11.riddles.org.uk: Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes:  Hitoshi Questions here:  Hitoshi - agglevelsup? What case exactly would you consider an error? When an order by expression references a lower (more deeply nested) query level than

Re: [HACKERS] Aggregate ORDER BY patch

2009-11-15 Thread Hitoshi Harada
2009/11/16 Andrew Gierth and...@tao11.riddles.org.uk: Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes:   What case exactly would you consider an error? When an order by   expression references a lower (more deeply nested) query level   than any of the actual arguments?  Hitoshi It's

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-11-19 Thread Hitoshi Harada
have tmpcontext for that purpose), memory leakage probably seems to happen. I'll check it out. Thanks for your elaborate review anyway. All I was worried about is now clear. It will be lucky if I can update my patch until next week. So please keep it Waiting on Author. Regards, -- Hitoshi Harada

[HACKERS] Syntax conflicts in frame clause

2009-11-24 Thread Hitoshi Harada
, it was solved as expected. In my poor mind there's no way to avoid this situation as long as you keep BETWEEN as type_func_name_keyword, but could anyone have a solution for this? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] Syntax conflicts in frame clause

2009-11-24 Thread Hitoshi Harada
2009/11/25 Tom Lane t...@sss.pgh.pa.us: Hitoshi Harada umi.tan...@gmail.com writes: Rewriting my frame support types patch to allow any expression in PRECEDING/FOLLOWING clause, I found the syntax below in PG conflicts: Yeah, we ran into that in the original WINDOW patch IIRC, and found some

[HACKERS] Frame level memory in WindowAgg?

2009-11-28 Thread Hitoshi Harada
aggregate which I guess doesn't cause serious things anyway. Not crash, nor return wrong answer. For 1. way, the ideal situation is compiler error on building such custom aggregates for 8.5, which tells the developer that the API had changed. Comments? Regards, -- Hitoshi Harada -- Sent via

Re: [HACKERS] Aggregate ORDER BY patch

2009-12-02 Thread Hitoshi Harada
to look more on this patch, I mark this item as Ready for Committer for now. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Cost of sort/order by not estimated by the query planner

2009-12-02 Thread Hitoshi Harada
. It's because the result of IndexScan is already sorted by demanded key, whereas the one of BitmapIndexScan isn't. But I'm not sure why the query lasts more than 30 minutes... Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-12-05 Thread Hitoshi Harada
. In literalROWS/ mode this value means physical row (this error appears twice) Oops, thanks. I'm now reworking as reviewed. The last issue is whether we accept extension of frame types without RANGE support. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-12-05 Thread Hitoshi Harada
2009/12/5 Hitoshi Harada umi.tan...@gmail.com: I'm now reworking as reviewed. The last issue is whether we accept extension of frame types without RANGE support. Attached is updated version. I added AggGetMemoryContext() in executor/nodeAgg.h (though I'm not sure where to go...) and its second

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-12-06 Thread Hitoshi Harada
2009/12/7 Andrew Gierth and...@tao11.riddles.org.uk: Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes:  Hitoshi Attached is updated version. I added AggGetMemoryContext()  Hitoshi in executor/nodeAgg.h (though I'm not sure where to go...)  Hitoshi and its second argument iswindowagg

Re: [HACKERS] add more frame types in window functions (ROWS)

2009-12-06 Thread Hitoshi Harada
are welcome yet. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] YAML Was: CommitFest status/management

2009-12-07 Thread Hitoshi Harada
no response is silent approval or not. +1. Sometimes a reviewer waits for the consensus in the community when someone else waits for review (, because it is marked as Needs Review). Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] LATERAL

2009-12-19 Thread Hitoshi Harada
aggregate on large since the planner doesn't push down outer qual to aggregate node. The output will discard almost all of agged's output. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] LATERAL

2009-12-19 Thread Hitoshi Harada
2009/12/20 Robert Haas robertmh...@gmail.com: On Sat, Dec 19, 2009 at 12:49 PM, Hitoshi Harada umi.tan...@gmail.com wrote: 2009/10/20 Andrew Gierth and...@tao11.riddles.org.uk: Right now, the only way pg can plan this is to do a hashjoin or mergejoin of the _entire content of big1 and big2_

[HACKERS] IntArray in c.h

2009-12-29 Thread Hitoshi Harada
lIndex in ExecEvalArrayRef because the usage of the variable doesn't seem good to me. Regression passed and various manual tests like UPDATE t SET a[1:2][1] = 1 didn't fail. Regards, -- Hitoshi Harada IntArray.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql

Re: [HACKERS] IntArray in c.h

2009-12-29 Thread Hitoshi Harada
2009/12/30 Tom Lane t...@sss.pgh.pa.us: Hitoshi Harada umi.tan...@gmail.com writes: I found the struct IntArray defined in c.h is actually used only in execQual.c. ISTM the definition should be at least moved to the right place. It's a general-purpose datatype that might be used anywhere

[HACKERS] More frame options in window functions

2009-12-31 Thread Hitoshi Harada
in RANGE offset are only numeric and temporal ones but we don't have such limitation. - add +/- operator search code in parsing, which is used to calculate frame bound, but I'm not sure if this is right approach. - add more regression tests Regards, -- Hitoshi Harada more_frame_options.20091231

Re: [HACKERS] IntArray in c.h

2010-01-02 Thread Hitoshi Harada
doesn't need, plus or minus a few things maybe. Looking closer in c.h, there are several things to move or remove (and it gets slightly more efficient if we do), but it seems we don't have such motivation... Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers

Re: [HACKERS] PATCH: Add hstore_to_json()

2010-01-02 Thread Hitoshi Harada
have our own implementation to handle JSON (i.e. no use of external libraries)? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PATCH: Add hstore_to_json()

2010-01-02 Thread Hitoshi Harada
2010/1/3 Andrew Dunstan and...@dunslane.net: Hitoshi Harada wrote: A question: Isn't there no possibility that we have our own implementation to handle JSON (i.e. no use of external libraries)? Why should we reinvent a wheel someone else has already invented? This is what shared

Re: [HACKERS] PATCH: Add hstore_to_json()

2010-01-03 Thread Hitoshi Harada
, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PATCH: Add hstore_to_json()

2010-01-04 Thread Hitoshi Harada
2010/1/4 David E. Wheeler da...@kineticode.com: On Jan 3, 2010, at 4:18 PM, Hitoshi Harada wrote: That sounds good and seems possible, as far as operator returns JSON always. Perhaps every JSON fetching returns JSON even if the result would be a number. You can cast it.   % SELECT ('{foo

Re: [HACKERS] More frame options in window functions

2010-01-04 Thread Hitoshi Harada
2009/12/31 Hitoshi Harada umi.tan...@gmail.com: Attached is the fix pointed out in the previous CommitFest plus RANGE offset support. Improved version attached. In this revision I fixed type mismatch case like ORDER BY int4_data RANGE BETWEEN int8_data PRECEDING Update of comments and fix

Re: [HACKERS] More frame options in window functions

2010-01-13 Thread Hitoshi Harada
2010/1/5 Hitoshi Harada umi.tan...@gmail.com: 2009/12/31 Hitoshi Harada umi.tan...@gmail.com: Attached is the fix pointed out in the previous CommitFest plus RANGE offset support. Improved version attached. In this revision I fixed type mismatch case like ORDER BY int4_data RANGE BETWEEN

  1   2   3   4   5   >