Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options - Mailing list pgsql-hackers

From Oliver Ford
Subject Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
Date
Msg-id CAGMVOdt3CoqizLw8yx=y_4vD_nF+T2CubpAn5TTm5OkD5J80Wg@mail.gmail.com
Whole thread Raw
In response to Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options  (Tatsuo Ishii <ishii@sraoss.co.jp>)
Responses Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
List pgsql-hackers


On Sun, Apr 23, 2023 at 4:29 AM Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
> Vik Fearing <vik@postgresfriends.org> 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 applications that might be using these
> words as column or function names.

Agreed.
 
Attached is a new version of the code and tests to implement this. There's now no modification to windowfuncs.c or the catalog,
it's only a bool added to FuncCall which if set to true, ignores nulls. It adds IGNORE/RESPECT at the Unreserved, As Label level.

The implementation also aims at better performance over previous versions by not disabling set_mark, and using an array to
track previous non-null positions in SEEK_HEAD or SEEK_CURRENT with Forward (lead, but not lag). The mark is set if a row
is out of frame and further rows can't be in frame (to ensure it works with an exclusion clause).

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 EXCLUDE CURRENT ROW and for two contiguous null rows.

I've not yet tested custom window functions with the patch, but I'm happy to add them to the test patch in v2 if we want to go this way
in implementing this feature.
Attachment

pgsql-hackers by date:

Previous
From: Masahiko Sawada
Date:
Subject: Re: Perform streaming logical transactions by background workers and parallel apply
Next
From: John Naylor
Date:
Subject: Re: [PATCH] Clarify the behavior of the system when approaching XID wraparound