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 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 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 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 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
;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, 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
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;
>>
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 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
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
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 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
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
>
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 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
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 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 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 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
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 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
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 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 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 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 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 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, 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 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, 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 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 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 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.
>
34 matches
Mail list logo
