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.