Re: [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Jan 14, 2014 at 4:16 AM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Tue, Jan 14, 2014 at 9:09 PM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>>
>> I think unless anyone has some objections I'm going to remove the inverse
>> transition for SUM(numeric) and modify the documents to tell the user how to
>> build their own FAST_SUM(numeric) using the built in functions to do it. I'm
>> starting to think that playing around with resetting numeric scale will turn
>> a possible 9.4 patch into a 9.5/10.0 patch. I see no reason why what's there
>> so far, minus sum(numeric), can't go in...
>>
>
> Of course its only now that I discover that this is not possible to do this:
>
> CREATE AGGREGATE fast_sum (numeric)
> (
> stype = numeric,
> sfunc = numeric_avg_accum,
> invfunc = numeric_avg_accum_inv,
> finalfunc = numeric_sum
> );
>
> because SUM(numeric) uses an internal type to store the transition state.
>
> hmmm, built-in fast_sum anyone?
> Is there any simple way to limit these to only be used in the context of a
> window? If so is it worth it?
> Would we want fast_sum() for float too?

Maybe these additional "fast" functions (one might also say
"inaccurate") should live in an extension, in contrib.

It strikes me that for numeric what you really need is to just tell
the sum operation, whether through a parameter or otherwise, how many
decimal places to show in the output. Obviously that's not a
practical change for sum() itself, but if we're inventing new stuff it
can be done.

For floats, things are not so good. The data type is inexact by
nature, and I think cases where you get substantially wrong answers
will be common enough that people who attempt to use whatever we
devise in this area will have sum trouble. *ducks*

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company