Re: Add RANGE with values and exclusions clauses to the Window Functions

From: Oliver Ford <ojford(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Erik Rijkers <er(at)xs4all(dot)nl>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Add RANGE with values and exclusions clauses to the Window Functions
Date: 2018-01-26 23:35:20
Message-ID: CAGMVOdvJMfOzm0v4psCoAaVGfCbdFwNKzaSEhfTj8rE9UCz-eQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Attached patch implements an extensible version of the RANGE with
values clause. It doesn't actually add any more type support than was
available in previous versions, but is flexible enough for them to be
added easily in further commits.

The new code adds a new family, "in_range_ops", to pg_opfamily. This
can be thought of as a polymorphic function type that is implemented
by adding rows to pg_amproc with the corresponding amprocfamily. In
pg_amproc, a row of this family has an amproclefttype corresponding to
the ORDER BY column's type, an amprocrighttype corresponding to the
offset type, and an amproc with the pg_proc.Oid of the function that
implements "in_range" for these types. The implementing function has a
name in pg_proc of the format:

in_range_<<sort type>>_<<offset type>>_[asc|desc]

For example, "in_range_int8_int4_asc" is the function for a sort
column of type int8, an offset of type int4, and a sort mode of
ascending. In pg_amproc, an amprocnum of 1 is used for an ascending
sort mode, and an amprocnum of 2 for a descending sort mode. Treating
"in_range" as its own opfamily allows the retrieval of the function's
Oid with a single cache lookup. The Oid's for start and end are sent
through the parser and planner into nodeWindowAgg, where the
PGFunction is retrieved. The PGFunction is then called to check if the
row is in range. No other code in nodeWindowAgg has changed from
previous versions of the patch.

The in_range functions have the following signature:
in_range(Datum curr, Datum slot, Datum offset, bool preceding, bool end);

I've tested that the existing regression tests in previous versions
still pass, and also added new tests for descending mode.

I would suggest that we add any further type support as separate
patches on top of this as this patch is already quite large. I'm happy
to work on adding any types that people would like to be supported.

Attachment Content-Type Size
0001-window-frame-v7.patch application/octet-stream 176.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-01-26 23:36:51 Re: [HACKERS] Refactoring identifier checks to consistently use strcmp
Previous Message Tomas Vondra 2018-01-26 23:28:07 Re: Setting BLCKSZ 4kB