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

pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Oliver Ford <ojford@gmail.com>
Cc: Erik Rijkers <er@xs4all.nl>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Add RANGE with values and exclusions clauses to the Window Functions
Date: Wed, 31 Jan 2018 19:06:00 -0500
Message-ID: <15869.1517443560@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGMVOdv5D67XaOWAys_cr8cFiq4z5ywKnQo1rovFW0tbnVyYuA@mail.gmail.com>
References: <CAGMVOdu9sivPAxbNN0X+q19Sfv9edEPv=HibOJhB14TJv_RCQg@mail.gmail.com>
	<b2374b9461c349b17b50c77f3a70e1a1@xs4all.nl>
	<CAGMVOdsopg5ZAuJh9+ceq-s0pU2qqZsZn-jbL4A1c=h1iWaZVA@mail.gmail.com>
	<CAGMVOdvrZCwJg0BngM9GMO+LK=j20vhSfdk1gTTBNNH311Vgsw@mail.gmail.com>
	<bc8a1d397fa81d6ae33c297d0224184b@xs4all.nl>
	<2c084c1c2bf599ceb2afa87874de8bc1@xs4all.nl>
	<CAGMVOdvETRCKpeT06Uoq5RsNUOdH7d1iYy7C1Pze=L5=gBzs-Q@mail.gmail.com>
	<cdbe62a6eaddae00fa770c09ed346fc0@xs4all.nl>
	<CAGMVOdttRSUU3=tjgdhr902HmUVL1GLQWG0ZTVXi7OY59Xo0Xw@mail.gmail.com>
	<13279.1515535159@sss.pgh.pa.us>
	<CAGMVOdtYSNJusckQ_9vBkMsZBQzan=Uw-Z-uYG2fD9rZ2TP7pA@mail.gmail.com>
	<4139.1515567978@sss.pgh.pa.us>
	<CAGMVOdvJMfOzm0v4psCoAaVGfCbdFwNKzaSEhfTj8rE9UCz-eQ@mail.gmail.com>
	<c30e6457a6de09d26425af997f71996e@xs4all.nl>
	<CAGMVOdtGe=30u6D2WqwvWchE+-yNb9jKnEeG8BHyw7jHiGPR7w@mail.gmail.com>
	<27788.1517069693@sss.pgh.pa.us>
	<CAGMVOduaHeA8q+kvCqX-UfuV_=jHvb-XK+1KJEiNy80Drk=y_A@mail.gmail.com>
	<10568.1517261334@sss.pgh.pa.us>
	<CAGMVOdtJZCXQRO1HQvGWTfzxrP0cTtxWXfXJ58EC2f2GgSQ+9w@mail.gmail.com>
	<14068.1517266223@sss.pgh.pa.us>
	<CAGMVOdtkUxmN8iLF5cQSF7+8kZA_fDYuBBcN0BvjDKbOjCvUdw@mail.gmail.com>
	<18875.1517272139@sss.pgh.pa.us>
	<CAGMVOdv0G=cSEFdbP6om7920cd7d_GOin8H+dRi6jMpC_9C_2g@mail.gmail.com>
	<CAGMVOdtHgHbhKxZfduUbQNX8o5b0bTiCKV5A=PjTS7w6i8QFKw@mail.gmail.com>
	<c970520d467f2d49ca45c20a3f0a8533@xs4all.nl>
	<e0b02cb1e6412400c1def920f8f24d02@xs4all.nl>
	<CAGMVOdv5D67XaOWAys_cr8cFiq4z5ywKnQo1rovFW0tbnVyYuA@mail.gmail.com>

Oliver Ford <ojford@gmail.com> writes:
> [ 0001-window-frame-v11.patch ]

I've realized that the exclusion clause aspect of this patch is rather
badly broken.  In particular, the "seek to row" logic in
WinGetFuncArgInFrame is critically dependent on the assumption that the
rows of the frame are contiguous.  Use of an EXCLUDE option makes them
not contiguous, but that doesn't mean you can just return NULL if the
seek hits one of the excluded rows.  The way the spec is written, it's
pretty clear that e.g. first_value() should be the value from the first
row that survives all exclusions.  But as this is coded, if the first
row that'd otherwise be in frame is excluded by EXCLUDE, you'll get
NULL, not the value from the first row that isn't excluded.  An example
of getting the wrong results:

regression=# select x, first_value(x) over (order by x rows between
current row and 1 following exclude current row)
from generate_series(1,10) x;
 x  | first_value 
----+-------------
  1 |            
  2 |            
  3 |            
  4 |            
  5 |            
  6 |            
  7 |            
  8 |            
  9 |            
 10 |            
(10 rows)

We could imagine reimplementing WinGetFuncArgInFrame to fix this, but
aside from the sheer inefficiency of simple fixes, I'm not very clear
what seeking relative to WINDOW_SEEK_CURRENT should mean when the current
row is excluded.  (Of course, the current row could have been out of frame
before too.  Maybe we should just get rid of WINDOW_SEEK_CURRENT?)

I'm a bit tempted to rip out the exclusion-clause support and leave the
topic to be revisited later.  It'd have been better done as a separate
patch anyhow IMO, since it seems quite orthogonal to the RANGE or GROUPS
options.  (And TBH, given the lack of field demand for it, I'm not sure
that we want to pay a complexity and performance price for it.)

			regards, tom lane

view thread (45+ messages)  latest in thread

Message-ID: <15869.1517443560@sss.pgh.pa.us>
Permalink:  ../15869.1517443560@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/15869.1517443560@sss.pgh.pa.us

 ·

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tgl@sss.pgh.pa.us, ojford@gmail.com, er@xs4all.nl
  Subject: Re: Add RANGE with values and exclusions clauses to the Window Functions
  In-Reply-To: <15869.1517443560@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox