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

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...
>
> If so then there's 36 aggregate functions ticked off my "create an inverse
> transition function for" list! I personally think that's a pretty good win.
> I'd rather do this than battle and miss deadlines for 9.4. I'd find that
> pretty annoying.
>
>
Here's a patch which removes sum(numeric) and changes the documents a
little to remove a reference to using sum(numeric) to workaround the fact
that there's no inverse transitions for sum(float). I also made a small
change in the aggregates.sql tests to check that the aggfnoid <= 9999.

I've also pulled the regression tests that I had added for sum(numeric) as
they no longer test anything new. All tests are now passing.

With the attached patch I feel like I've left users a bit high and dry for
their sum(numeric) needs. I guess there is no true workaround as even if
they created their functions in SQL using simple + and - arithmetic, they
would likely suffer from NaN recovery problems. I'm starting to come around
to Tom's FAST_SUM idea as I simply can't see any fool proof workaround that
could be created without writing things in C.

The only problems I see with the FAST_SUM idea are that the number of
trailing zeros may appear a little random based on if inverse transitions
are used or are not used... Keep in mind that inverse transitions are not
performed if any aggregate in the window does not support them OR if any
aggregate in the frame contains a volatile function in the aggregate's
parameters or the FILTER (WHERE clause). Does this matter or can we just
document to warn about that?

If there's a few more +1s for FAST_SUM(numeric) then let me know and I'll
add it.
If anyone feels strongly against adding FAST_SUM then please let the
reasons for that known too.
Or failing that, if anyone has any other ideas that have not yet been
written on this thread, please post them so we can discuss.

Regards

David Rowley

> Regards
>
> David Rowley
>
>