I have used your notes below to rewrite the Window function SQL manual
section. As you said, it was very hard to read. I now understand it
better, having restructured it, and I hope others do too.
After waiting 30 minutes for our developer doc build to refresh, I am
giving up and posting my own URL for the doc changes:
http://momjian.us/tmp/pgsql/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
Perhaps I need to go back to having my own doc build.
---------------------------------------------------------------------------
On Mon, Oct 17, 2011 at 11:48:38AM +0200, Florian Pflug wrote:
> On Oct17, 2011, at 01:09 , Tom Lane wrote:
> > Florian Pflug <[email protected]> writes:
> >> ... reading those parts again, I realize the it says "When ORDER BY is
> >> omitted
> >> the *default* frame consists ... ", and that the second quote is followed
> >> by a footnote which says
> >
> >> There are options to define the window frame in other ways, but this
> >> tutorial
> >> does not cover them. See Section 4.2.8 for details. [3.5, Window
> >> Functions]
> >
> >> So it was just me being thick. Sorry for the noise.
> >
> > Hmm. Maybe the use of a <footnote> there is too subtle, and we should
> > instead have that text in-line (probably in parentheses)? Or we could
> > use a <note>, but that's probably too much emphasis.
>
> Inline and in parentheses sounds fine.
>
> In addition, I think we should reword the explanation in 4.2.8 (The SQL
> Language
> / SQL Syntax / Value Expressions / Window Functions). Instead of that rather
> long (and IMHO hard to read) paragraph about possible frame clauses and their
> behaviour in the presence or absence of an ORDER BY clause, we should go with
> a more algorithmic explanation I think.
>
> Something along these lines maybe:
>
> ----------
> .) PARTITION BY splits the rows into disjoint partitions. All further
> processing
> happens only inside a single partition
>
> .) In RANGE mode, ORDER BY then splits each partition into an ordered list of
> sub-partitions, each containing rows which the ORDER BY considers to be
> equivalent.
>
> .) In ROWS mode, OTOH, each sub-partition contains only a single row. Thus, if
> there are rows which are considered to be equivalent by the ORDER BY, the
> ordering of the sub-partition isn't fully determined.
>
> .) Each row's frame then consists of some consecutive range of sub-partitions.
>
> .) In RANGE mode, that consecutive range can only start at either the first
> sub-partition or the current row's sub-partition, and can only end at
> either
> the current row's sub-partition or the last sub-partitions.
>
> .) In ROWS mode, the consecutive range may additional start <n> sub-partitions
> (or rows, it's the same thing here) before the current row, and may
> additionally
> end <m> sub-partitions/rows after the current row.
>
> >From that, it follows that even with an underspecified sort order, the
> >contents of
> each frame are still fully determined in RANGE mode. The ordering of rows
> within
> a frame is not determined, though. So overall, in RANGE mode, a query's
> result is
> only non-deterministic if the window function is sensitive to the ordering of
> rows
> within a frame.
>
> In ROWS mode, OTOH, the contents each frame themselves are not fully
> determined,
> so even an ordering agnostic window function may produce non-deterministic
> results.
> ----------
>
> If you think that something along these lines would be an improvement, I can
> try
> to come up with a patch.
>
> best regards,
> Florian Pflug
>
>
> --
> Sent via pgsql-hackers mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
