Re: Aggregates with non-commutative transition functions

(CC'd to the newsgroup/mailing list to work around damn Tom's spamguard,
which refuses to recognize my SMTP as a possibly valid one ...)

Dear Tom,

Tom Lane wrote:
> Emmanuel Charpentier <charpent(at)bacbuc(dot)dyndns(dot)org> writes:
>
>>In other words, may I guarantee that :
>>select recnum, glue(linenum)as authors from (select recnum, linenum, author
>>from cit_authors where <some conditions on recnum> order by recnum,
>>linenum) as foo;
>>will indeed give me the authors in the original order ?
>
>
> The query as given is illegal; you'd need to add "GROUP BY recnum" to
> the outer query to make it legal. And once you do that, I don't think
> you can rely on the ordering of the rows.

Right ! I was typing off the top of my mind ...

> This problem has been discussed before, and I think we came up with some
> workaround for it, but I don't have time to go trolling the archives for
> the solution at the moment.

I looked up the archives (not a, easy task ...), didn't found a solution
per se, but a good explanation of the problem (yours, BTW ...). and got an
idea : the problem arises, according to you) from the fact that the
aggregation itself requires a sort according to the grouping key, which
isn't stable (i.e. may muck the row ordering of the table to which the
aggregate is to be applied) "on most platforms" (does this mean that you
use the libc sort() ?).

Since the stability of the sort is an issue but for this quite specific
case, one could envision a flag to "CREATE AGGREGATE", signalling the
planner the fact that the transition function isn't commutative, hence the
need to use a stable version of sort() (I *suppose* that this exists in the
littterature ...). AFAICT, this is the only case where this need arises.

Since CREATE AGGREGATE is PostgreSQL-specific anyway, this shouldn't break
compatibility with anything.

What I do not know is if this extension has enough of "general usefulness"
to grant the work (insertion of a stable sort routine, mucling with the
planner, changging the grammar for CREATE AGGREGATE, extenging
pg_aggregate, etc ...) necessary to fit it in PostgreSQL...

In the meanwhile, I'll cope with an ad-hockery (probably involving a cursor).

Your thoughs ?

Emmanuel Charpentier

--
Emmanuel Charpentier