From: Tom Lane <tgl@sss.pgh.pa.us>
To: wollhofen@unisoftwareplus.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18126: Ordered set aggregate: result does not depend on sort order
Date: Thu, 21 Sep 2023 11:01:30 -0400
Message-ID: <699056.1695308490@sss.pgh.pa.us> (raw)
In-Reply-To: <18126-b4b5de7206c972e3@postgresql.org>
References: <18126-b4b5de7206c972e3@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> Probably related to bug report #18125.
> Using a custom ordered set aggregate, the sort order in the ORDER BY after
> the WITHIN GROUP does not show an influence on the results, as demonstrated
> in the script below.
I think the problem in both of these bugs is that you're expecting
the system to sort the input for you. As per [1], this is not the
case for ordered-set aggregates:
Unlike the case for normal aggregates, the sorting of input rows for
an ordered-set aggregate is not done behind the scenes, but is the
responsibility of the aggregate's support functions. The typical
implementation approach is to keep a reference to a “tuplesort” object
in the aggregate's state value, feed the incoming rows into that
object, and then complete the sorting and read out the data in the
final function. This design allows the final function to perform
special operations such as injecting additional “hypothetical” rows
into the data to be sorted. While normal aggregates can often be
implemented with support functions written in PL/pgSQL or another PL
language, ordered-set aggregates generally have to be written in C,
since their state values aren't definable as any SQL data type.
Perhaps it would be useful to have some kind of shim whereby a
"dumb" transition function could be the basis of an ordered-set
aggregate. But I'm not quite seeing why that would be an
improvement over a traditional aggregate that you use with an
ORDER BY option.
regards, tom lane
[1] https://www.postgresql.org/docs/current/xaggr.html#XAGGR-ORDERED-SET-AGGREGATES
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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, wollhofen@unisoftwareplus.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18126: Ordered set aggregate: result does not depend on sort order
In-Reply-To: <699056.1695308490@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