> Hi,
> Patch applies and compiles, all included tests passed and after the latest
> fixes for non-nulls array, performance is near to lead/lag without support
> of "ignore nulls".
> I have been using the last version for more than one month in a production
> environment with real data and didn't f
Hi,
Patch applies and compiles, all included tests passed and after the latest
fixes for non-nulls array, performance is near to lead/lag without support
of "ignore nulls".
I have been using the last version for more than one month in a production
environment with real data and didn't find any bugs
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,
>> > similar when using lead/lag.
>>
>>
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
> 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:
https://cirrus-ci.com/task/6364194477441024
Best reagards,
--
Tatsuo I
>> 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.
EXPLAIN ANALYZE
SELECT
x,
nth_value(x,2) IGNORE NULLS OVER w
FROM g
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
> 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 your tests above to the tests.
I applied the v7 patch and ra
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 |
> 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 | | 2
> 4 | 4 |
> 5 | | 4
> 6 | 6 | 7
> 7
> I've looked at it again and I think the code is correct,
Good news! I will look into your explanation.
> 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 | | 2
> 4 |
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
>> +/*
>> + * 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
>> +ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
>>
>> Do you
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 */
>
> The attached patch should fix both of these. I've added extra tests
> with a PARTITION BY in the window clause to test for multiple
> partitions.
I have looked through the v5 patch. Here are review comments.
>From 5268754b33103fefc511b57ec546103899f70dbe Mon Sep 17 00:00:00 2001
From: Oliver Fo
Hi,
I was able to reproduce exactly the problem, with clean compile
and --enable-cassert:
test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
(VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
x | y | lead
---+---+--
1 | |2
2 | 2 |2
3 | |2
(3 rows)
test=#
Also, make check
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
> Hi,
> I was able to reproduce exactly the problem, with clean compile
> and --enable-cassert:
> test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
> (VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
> x | y | lead
> ---+---+--
> 1 | |2
> 2 | 2 |2
> 3 | |2
> (3 rows)
>
> Hello,
> I also played with the v4 patch and it produces correct result:
> test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
> (VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
> x | y | lead
> ---+---+--
> 1 | |2
> 2 | 2 |
> 3 | |
> (3 rows)
>
> test=#
> It is from today's
> Attached version moves the setting of IGNORE_NULLS to the window
> function itself, with the functions that don't allow it erroring out.
> This is done with a new api: WinCheckAndInitializeNullTreatment.
>
> Custom functions that don't call this will simply not have the
> IGNORE_NULLS option set
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
Tatsuo Ishii writes:
> 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 IGNORE/RESPECT NULLS option, throw
>> No, there needs to be a way for the individual window function to
>> throw error if that's specified for a function that can't handle it.
>> I'm just saying I don't want that to be hard-wired in some centralized
>> spot.
>
> Would it be acceptable to add a bool column to pg_proc, say
> "pronull
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
>> So you want to allow to use IGNORE NULLS to other built-in window
>> functions?
>
> No, there needs to be a way for the individual window function to
> throw error if that's specified for a function that can't handle it.
> I'm just saying I don't want that to be hard-wired in some centralized
>
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 allow to use IGNORE NULLS to other built-in window
> functions?
No,
> Tatsuo Ishii writes:
>>> The latest version restricts it to lag, lead, first_value, last_value,
>>> and nth_value. We can extend it in a subsequent patch if there's
>>> demand?
>
>> The restriction is required by the SQL standard. So I don't think we
>> need to extend to other window functions.
Tatsuo Ishii writes:
>> The latest version restricts it to lag, lead, first_value, last_value,
>> and nth_value. We can extend it in a subsequent patch if there's
>> demand?
> The restriction is required by the SQL standard. So I don't think we
> need to extend to other window functions.
The SQL
Thanks for updating the patch.
>> 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,
> and nth_value.
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 Wednesday, September 11, 2024, Tatsuo Ishii wrote:
>
>>
>> test=# SELECT row_number() IGNORE NULLS OVER w FROM t1 WINDOW w AS (ORDER
>> BY i);
>> row_number
>>
>> 1
>> 2
>> (2 rows)
>>
>> The t1 table only contains NULL rows. By using IGNORE NULLS, I think
>
On Wednesday, September 11, 2024, Tatsuo Ishii wrote:
>
> test=# SELECT row_number() IGNORE NULLS OVER w FROM t1 WINDOW w AS (ORDER
> BY i);
> row_number
>
> 1
> 2
> (2 rows)
>
> The t1 table only contains NULL rows. By using IGNORE NULLS, I think
> it's no wonde
>> >> 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
>>
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:
> >>>
> >>> Attached is the patch to implement this (on top of your patch).
> >>>
> >
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:
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 funct
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;
>> ERROR: window function row_number cannot
> The last time this was discussed (
> https://www.postgresql.org/message-id/1037735.1610402426%40sss.pgh.pa.us)
> it was suggested to make the feature generalizable, beyond what the
> standard says it should be limited to.
I have read the mail. In my understanding nobody said that standard
window
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
>> The attached test patch is mostly the same as in the previous patch
>> set, but it doesn't fail on row_number anymore as the main patch
>> only rejects aggregate functions. The test patch also adds a test for
>
>> +SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- succeeds
>
> I think t
> The attached test patch is mostly the same as in the previous patch
> set, but it doesn't fail on row_number anymore as the main patch
> only rejects aggregate functions. The test patch also adds a test for
> +SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- succeeds
I think the standar
On Sun, Apr 23, 2023 at 4:29 AM Tatsuo Ishii wrote:
> > Vik Fearing writes:
> >
> >> For me, this is perfectly okay. Keep them at the lowest level of
> >> reservation as possible.
> >
> > Yeah, keep them unreserved if at all possible. Any higher reservation
> > level risks breaking existing ap
> Vik Fearing writes:
>> On 4/22/23 14:14, Tatsuo Ishii wrote:
>>> Note that RESPECT/IGNORE are not registered as reserved keywords in
>>> this patch (but registered as unreserved keywords). I am not sure if
>>> this is acceptable or not.
>
>> For me, this is perfectly okay. Keep them at the low
> Excellent. I was thinking about picking my version of this patch up
> again, but I think this might be better than mine.
Thanks.
> I am curious why set_mark is false in the IGNORE version instead of
> also being const_offset. Surely the nth non-null in the frame will
> never go backwards.
In
Vik Fearing writes:
> On 4/22/23 14:14, Tatsuo Ishii wrote:
>> Note that RESPECT/IGNORE are not registered as reserved keywords in
>> this patch (but registered as unreserved keywords). I am not sure if
>> this is acceptable or not.
> For me, this is perfectly okay. Keep them at the lowest level
On 4/22/23 14: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 patch). I really lov
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
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 patch). I really love to have
RESPECT/IGNORE NULLS because I
Thank you very much for feedback and yes, that is very useful SQL syntax.
Maybe you miss my previous answer, but you are right, that patch is
currently dead,
because some important design questions must be discussed here, before
patch rewriting.
I have dropped support of from first/last for nth_va
Greetings,
This seems to have died out, and that's pretty unfortunate because this
is awfully useful SQL standard syntax that people look for and wish we
had.
* Andrew Gierth (and...@tao11.riddles.org.uk) wrote:
> So I've tried to rough out a decision tree for the various options on
> how this mi
> "Krasiyan" == Krasiyan Andreev writes:
Krasiyan> I am using last version from more than two months ago in
Krasiyan> production environment with real data and I didn't find any
Krasiyan> bugs, so I'm marking this patch as ready for committer in the
Krasiyan> commitfest app.
Oliver (or a
> "Tom" == Tom Lane writes:
>> So I've tried to rough out a decision tree for the various options
>> on how this might be implemented (discarding the "use precedence
>> hacks" option). Opinions? Additions?
Tom> I think it'd be worth at least drafting an implementation for the
Tom> lexic
Andrew Gierth writes:
> So I've tried to rough out a decision tree for the various options on
> how this might be implemented (discarding the "use precedence hacks"
> option). Opinions? Additions?
I think it'd be worth at least drafting an implementation for the
lexical-lookahead fix. I think it
So I've tried to rough out a decision tree for the various options on
how this might be implemented (discarding the "use precedence hacks"
option). Opinions? Additions?
(formatted for emacs outline-mode)
* 1. use lexical lookahead
+: relatively straightforward parser changes
+: no new reserv
> "Tom" == Tom Lane writes:
>> select nth_value(x) from first ignore;
Tom> No, because once IGNORE is a keyword, even unreserved, it's not
Tom> legal as an AS-less alias.
That rule only applies in the select-list, not in the FROM clause; table
aliases in FROM are just ColId, so they can
Andrew Gierth writes:
> "Tom" == Tom Lane writes:
> Tom> If you just think of recognizing FROM FIRST/LAST, you get nowhere
> Tom> because that's still legal in other contexts. But if you were to
> Tom> look for FROM followed by FIRST/LAST followed by
> Tom> IGNORE/RESPECT/OVER, I think that c
> "Tom" == Tom Lane writes:
Tom> If you just think of recognizing FROM FIRST/LAST, you get nowhere
Tom> because that's still legal in other contexts. But if you were to
Tom> look for FROM followed by FIRST/LAST followed by
Tom> IGNORE/RESPECT/OVER, I think that could only validly happen i
Andrew Gierth writes:
> "Tom" == Tom Lane writes:
> Tom> The FROM FIRST/LAST bit seems particularly badly thought through,
> Tom> because AFAICS it is flat out ambiguous with a normal FROM clause
> Tom> immediately following the window function call. The only way to
> Tom> make it not so woul
> "Tom" == Tom Lane writes:
Tom> The FROM FIRST/LAST bit seems particularly badly thought through,
Tom> because AFAICS it is flat out ambiguous with a normal FROM clause
Tom> immediately following the window function call. The only way to
Tom> make it not so would be to make FIRST and LAS
Andrew Gierth writes:
> Normally I'd push hard to try and get some solution that's sufficiently
> generic to allow user-defined functions to make use of the feature. But
> I think the SQL spec people have managed to make that literally
> impossible in this case, what with the FROM keyword appearin
> "Krasiyan" == Krasiyan Andreev writes:
Krasiyan> Hi,
Krasiyan> Patch applies and compiles, all included tests and building
Krasiyan> of the docs pass. I am using last version from more than two
Krasiyan> months ago in production environment with real data and I
Krasiyan> didn't find a
Hi,
Patch applies and compiles, all included tests and building of the docs
pass.
I am using last version from more than two months ago in production
environment with real data and I didn't find any bugs,
so I'm marking this patch as ready for committer in the commitfest app.
На сб, 28.07.2018 г.
On Fri, Jul 13, 2018 at 01:52:00PM +0100, Oliver Ford wrote:
> Adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM
> FIRST/LAST to the non-aggregate window functions.
Please find attached an updated version for OID drift.
Best,
David.
--
David Fetter http://fetter.org/
Phone:
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
64 matches
Mail list logo
