On Sun, Dec 15, 2013 at 2:27 PM, Josh Berkus <j...@agliodbs.com> wrote:

> On 12/14/2013 05:00 PM, Tom Lane wrote: > > This consideration also makes me question whether we should apply the > > method for NUMERIC. Although in principle numeric addition/subtraction > > is exact, such a sequence could leave us with a different dscale than > > is returned by the existing code. I'm not sure if changing the number of > > trailing zeroes is a big enough behavior change to draw complaints. > > If we're going to disqualify NUMERIC too, we might as well bounce the > feature. Without a fast FLOAT or NUMERIC, you've lost most of the > target audience. > > I think even the FLOAT case deserves some consideration. What's the > worst-case drift? In general, folks who do aggregate operations on > FLOATs aren't expecting an exact answer, or one which is consistent > beyond a certain number of significant digits. > > And Dave is right: how many bug reports would we get about "NUMERIC is > fast, but FLOAT is slow"? > > >From what I can tell adding an inverse transition function to support AVG for numeric does not affect the number of trailing zeros in the results, so I've attached a patch which now has inverse transition functions to support numeric types in AVG and all of the STDDEV* aggregates. The function numeric_avg_accum_inv is the inverse transition function for AVG. In the attached patch I've set this to be the inverse transition function for SUM too. I know it was mentioned that having extra trailing zeros here is a change of results and could be unwanted, but I've left it in for now and I've included a failing regression test to demonstrate exactly what has changed in the hope that it may seed a discussion on what the best solution is. Here's a quick example of what I'm talking about: With this query I'll include a call to MAX to force the executor to not use inverse transitions. SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), MAX(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1.00000000000001),(2,2),(3,3)) v(i,n); sum | max ------------------+----- 6.00000000000001 | 3 5 | 3 3 | 3 (3 rows) Notice lack of trailing zeros for the sum results in rows 2 and 3. Here an inverse transition will be performed... Notice the extra trailing zeros on rows 2 and 3. SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1.00000000000001),(2,2),(3,3)) v(i,n); sum ------------------ 6.00000000000001 5.00000000000000 3.00000000000000 (3 rows) My ideas so far on what to do about this: 1. Don't allow inverse transition for SUM with numeric, but since numeric_avg_accum_inv is already written for AVG numeric support we could include a note in the docs to tell users how to create a SUM aggregate for numeric that supports inverse transitions. 2. Don't worry about the trailing zeros... After all SELECT AVG(n) FROM (VALUES(2::numeric)) v(n); adds a whole bunch of trailing zeros. Only the above 2 queries shows that this behaviour can be a bit weird. 3. Invent some way to remove trailing zeros, perhaps in numeric_out. Is there a reason they are there? Note that this would affect numeric globally. What I don't currently know is: Are there any rules about trailing zeros on numeric? I see that AVG with a single numeric produces many trailing zeros after the decimal point. Are these meant to be there or is it just a side effect of dividing on numerics? Please note that I'm not trying to push for any of the above points. I just want to get the information I currently have out there as perhaps someone can think of something better or show a good reason for or against any of the 3 points. Regards David Rowley -- > Josh Berkus > PostgreSQL Experts Inc. > http://pgexperts.com >

**
inverse_transition_functions_v1.7.patch.gz**

*Description:* GNU Zip compressed data

-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers