On Fri, Feb 28, 2025 at 11:49 AM Tatsuo Ishii wrote:
> >> BTW, I noticed that in the code path where
> >> ignorenulls_getfuncarginframe() is called, WinSetMarkPosition() is
> >> never called?
> >>
> >> Attached version uses the mark_pos at the end.
>
> I did simple performance test against v8.
>
On Sun, 9 Mar 2025, 20:07 Oliver Ford, wrote:
> On Sun, Mar 9, 2025 at 6:40 AM Tatsuo Ishii wrote:
>
>> > Attached version removes the non-nulls array. That seems to speed
>> > everything up. Running the above query with 1 million rows averages
>> 450ms,
&g
On Sun, Mar 9, 2025 at 6:40 AM Tatsuo Ishii wrote:
> > Attached version removes the non-nulls array. That seems to speed
> > everything up. Running the above query with 1 million rows averages
> 450ms,
> > similar when using lead/lag.
>
> Great. However, CFbot complains about the patch:
>
> http
On Tue, Feb 18, 2025 at 4:19 AM Tatsuo Ishii wrote:
> > Attached version doesn't use the nonnulls array if an Exclude is
> > specified, as I think it's not going to work with exclusions (as it's
> > only an optimization, this is ok and can be taken out entirely if you
> > prefer). I've also added
On Mon, Feb 3, 2025 at 11:46 AM Tatsuo Ishii wrote:
>
> > I've looked at it again and I think the code is correct, but I
> > miswrote that the array needs to be sorted. The above query returns:
> > x | y | nth_value
> > ---+---+---
> > 1 | 1 | 2
> > 2 | 2 | 1
> > 3 |
On Tue, Jan 28, 2025 at 9:02 AM Tatsuo Ishii wrote:
>
> >> +/*
> >> + * ignorenulls_getfuncarginframe
> >> + * For IGNORE NULLS, get the next nonnull value in the frame, moving
> >> forward or backward
> >> + * until we find a value or reach the frame's end.
> >> + */
> >> +static Datum
> >> +ign
On Mon, Jan 27, 2025 at 11:51 AM Tatsuo Ishii wrote:
> I have looked through the v5 patch. Here are review comments.
New version attached.
> @@ -69,6 +69,10 @@ typedef struct WindowObjectData
> int readptr;/* tuplestore read
> pointer for this fn */
>
On Thu, Jan 23, 2025 at 6:27 AM Tatsuo Ishii wrote:
>
> Another possible problem is, probably the code does not work well if
> there are multiple partitions. Since win_nonnulls stores currentpos in
> a partition, when the partition ends, win_nonnulls needs to be
> reset. Otherwise, it mistakenly r
On Tue, Jan 21, 2025 at 1:21 AM Tatsuo Ishii wrote:
> It needs lots of work including modifying CREATE FUNCTION
> command. Instead you could add an API to WinObject access functions to
> export ignore_nulls value. Then let each window function check it. If
> the window function should not take IGN
On Mon, Jan 20, 2025 at 12:31 AM Tom Lane wrote:
>
> Tatsuo Ishii writes:
> >> I would think that
> >> IGNORE NULLS is potentially useful for user-defined window functions,
> >> and we should not be building anything that restricts the feature to
> >> specific functions.
>
> > So you want to allo
On Thu, Sep 12, 2024 at 2:41 AM Tatsuo Ishii wrote:
>
> It seems you allow to use IGNORE NULLS for all window functions. If
> the case, you should explicitely stat that in the docs. Otherwise
> users will be confused because;
The latest version restricts it to lag, lead, first_value, last_value,
On Sun, Sep 8, 2024 at 2:22 PM Vik Fearing wrote:
>
> On 9/7/24 22:25, Oliver Ford wrote:
> > On Sat, May 6, 2023 at 9:41 AM Oliver Ford wrote:
> >>
> >>
> >>
> >> On Sat, 6 May 2023, 04:57 Tatsuo Ishii, wrote:
> >>>
> &g
On Sun, Sep 8, 2024 at 2:44 PM Laurenz Albe wrote:
>
> On Sun, 2024-09-08 at 11:43 +, Oliver Ford wrote:
> > The following review has been posted through the commitfest application:
> > make installcheck-world: tested, failed
> > Implements feature: tested, f
The following review has been posted through the commitfest application:
make installcheck-world: tested, failed
Implements feature: tested, failed
Spec compliant: tested, failed
Documentation:tested, failed
LGTM
The new status of this patch is: Ready for Committer
On Sat, May 6, 2023 at 9:41 AM Oliver Ford wrote:
>
>
>
> On Sat, 6 May 2023, 04:57 Tatsuo Ishii, wrote:
>>
>> Attached is the patch to implement this (on top of your patch).
>>
>> test=# SELECT row_number() RESPECT NULLS OVER () FROM (SELECT 1) AS s;
>>
On Sat, 6 May 2023, 04:57 Tatsuo Ishii, wrote:
> Attached is the patch to implement this (on top of your patch).
>
> test=# SELECT row_number() RESPECT NULLS OVER () FROM (SELECT 1) AS s;
> ERROR: window function row_number cannot have RESPECT NULLS or IGNORE
> NULLS
>
The last time this was di
;m happy
to add them to the test patch in v2 if we want to go this way
in implementing this feature.
From 81c48df9a08deb065379e8bccffb2f5592faa4d0 Mon Sep 17 00:00:00 2001
From: Oliver Ford
Date: Wed, 19 Apr 2023 01:07:14 +0100
Subject: [PATCH] initial window ignore
---
src/backend/executor/nodeWindowAgg.c |
On Sat, 22 Apr 2023, 13:14 Tatsuo Ishii, wrote:
> I revisited the thread:
>
> https://www.postgresql.org/message-id/flat/CAGMVOdsbtRwE_4%2Bv8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A%40mail.gmail.com
>
> and came up with attached POC patch (I used some varibale names
> appearing in the Krasiyan Andreev's
Adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM
FIRST/LAST to the non-aggregate window functions.
A previous patch
(https://www.postgresql.org/message-id/CA+=vxna5_n1q5q5okxc0aqnndbo2ru6gvw+86wk+onsunjd...@mail.gmail.com)
partially implemented this feature. However, that pat
On Tuesday, 30 January 2018, Tom Lane wrote:
> Another thing I'm a little confused by is the precise API for the in_range
> support functions (the lack of any documentation for it doesn't help).
> I wonder why you chose to provide two support functions per datatype
> combination rather than one w
On Monday, 29 January 2018, Tom Lane wrote:
> Oliver Ford writes:
> > On Monday, 29 January 2018, Tom Lane wrote:
> >> I've started to go through this in some detail, and I'm wondering why
> >> you invented a FRAMEOPTION_EXCLUDE_NO_OTHERS option bit ra
On Monday, 29 January 2018, Tom Lane wrote:
> Oliver Ford writes:
> > [ 0001-window-frame-v9.patch ]
>
> I've started to go through this in some detail, and I'm wondering why
> you invented a FRAMEOPTION_EXCLUDE_NO_OTHERS option bit rather than
> just represe
On Tuesday, 9 January 2018, Tom Lane wrote:
>
> So the approach I'm imagining now is a datatype-specific support function
> along the lines of
>
> in_range(a, b, delta) returns bool
>
> which is supposed to return true if a <= b + delta, or something along
> that line --- exact details of
On Fri, Dec 8, 2017 at 3:10 PM, David G. Johnston
wrote:
> On Fri, Dec 8, 2017 at 7:34 AM, Oliver Ford wrote:
>>
>> On Thu, Dec 7, 2017 at 11:47 PM, Everaldo Canuto
>> wrote:
>>
>> +1 from me. When I first used Postgres I struggled with how to quit
>> psql
On Thu, Dec 7, 2017 at 11:47 PM, Everaldo Canuto
wrote:
> Some of us unfortunately have to work with multiple databases like Oracle or
> MySQL. Their respective clients mysql and sqlplus uses "quit" or "exit" to
> exit sql client.
>
> Oracle's sqlplus uses "quit" or "exit" and MySQL client can be
Adds the GROUPS option to the window framing clause. This further
resolves TODO list item "Implement full support for window framing
clauses" and implements SQL:2011 T620. No other mainstream db has this
feature.
Apply this on top of my previous patch available here:
https://www.postgresql.org/mes
On Tue, Nov 28, 2017 at 4:38 AM, David Fetter wrote:
> On Mon, Nov 27, 2017 at 04:55:17PM +0000, Oliver Ford wrote:
>> On Mon, Nov 27, 2017 at 4:40 PM, Erik Rijkers wrote:
>> > On 2017-11-27 17:34, Erik Rijkers wrote:
>> >>
>> >> On 2017-11-27 16:01, Ol
On Mon, Nov 27, 2017 at 4:40 PM, Erik Rijkers wrote:
> On 2017-11-27 17:34, Erik Rijkers wrote:
>>
>> On 2017-11-27 16:01, Oliver Ford wrote:
>>>
>>> Attached is it in bare diff form.
>>
>>
>> [0001-window-frame-v3.patch]
>>
>> Th
On Mon, Nov 27, 2017 at 12:06 PM, Oliver Ford wrote:
> On Fri, Nov 24, 2017 at 3:08 PM, Erikjan Rijkers wrote:
>> SELECT pg_get_viewdef('v_window');
>> ! pg_get_viewdef
>> ! -
On Fri, Nov 24, 2017 at 3:08 PM, Erikjan Rijkers wrote:
> SELECT pg_get_viewdef('v_window');
> ! pg_get_viewdef
> ! --
> ! SELECT i.i,+
> ! sum(i.i) OVER (ORDER BY i.i) AS sum_rows+
> FROM ge
On Fri, Nov 24, 2017 at 3:08 PM, Erikjan Rijkers wrote:
> (debian 8)
>
> make check fails:
>
> foreign_data ... ok
> window ... FAILED
> xmlmap ... ok
>
> The diff is:
>
> $ ( cd /var/data1/pg_stuff/pg_sandbox/pgsql.frame_range/src/te
Adds RANGE BETWEEN with a start and end value, as well as an
exclusions clause, to the window functions. This partially resolves
TODO list item "Implement full support for window framing clauses".
== Specification ==
The window functions already allow a "ROWS BETWEEN start_value
PRECEDING/FOLLOWI
On Monday, 13 November 2017, Tom Lane wrote:
> Oliver Ford > writes:
> > On Sun, Nov 12, 2017 at 7:00 PM, Tom Lane > wrote:
> >> * Don't we need to fix the NUM_L (currency symbol) case in the
> >> same manner? (The NUM_D and NUM_S cases are handled in
>
On Sun, Nov 12, 2017 at 7:00 PM, Tom Lane wrote:
> Oliver Ford writes:
>> [ 0001-apply-number-v3.patch ]
>
> I looked at this patch briefly and have a couple of comments:
>
> * It seems entirely wrong to be matching to L_thousands_sep in the
> NUM_COMMA case; that form
34 matches
Mail list logo
