partition table and stddev() /variance() behaviour
Hi,
I am getting different output for stddev/variance functions with partition
tables.
CREATE TABLE part (c1 INT,c2 INT) PARTITION BY RANGE (c1);
CREATE TABLE part_p1 PARTITION OF part FOR VALUES FROM (1) TO (3);
CREATE TABLE part_p2 PARTITION OF part FOR VALUES FROM (3) TO (5);
INSERT INTO part VALUES (1,5),(2,15),(3,3),(4,17);
postgres=# SET parallel_setup_cost=0;
SET
postgres=# EXPLAIN SELECT COUNT(c1),STDDEV(c2),VARIANCE(c2) FROM part;
QUERY
PLAN
--------------------------------------------------------------------------------------------
Finalize Aggregate (cost=70.36..70.37 rows=1 width=72)
-> Gather (cost=70.12..70.33 rows=2 width=72)
Workers Planned: 2
-> Partial Aggregate (cost=70.12..70.13 rows=1 width=72)
-> Parallel Append (cost=0.00..56.00 rows=1882 width=8)
-> Parallel Seq Scan on part_p1 (cost=0.00..23.29
rows=1329 width=8)
-> Parallel Seq Scan on part_p2 (cost=0.00..23.29
rows=1329 width=8)
(7 rows)
postgres=# SELECT COUNT(c1),STDDEV(c2),VARIANCE(c2) FROM part;
* count | stddev | variance -------+--------+---------- 4 | 0
| 0(1 row)*postgres=#
postgres=# RESET parallel_setup_cost;
RESET
postgres=# EXPLAIN SELECT COUNT(c1),STDDEV(c2),VARIANCE(c2) FROM part;
QUERY PLAN
-----------------------------------------------------------------------
Aggregate (cost=121.71..121.72 rows=1 width=72)
-> Append (cost=0.00..87.80 rows=4520 width=8)
-> Seq Scan on part_p1 (cost=0.00..32.60 rows=2260 width=8)
-> Seq Scan on part_p2 (cost=0.00..32.60 rows=2260 width=8)
(4 rows)
postgres=# SELECT COUNT(c1),STDDEV(c2),VARIANCE(c2) FROM part;
* count | stddev | variance
-------+--------------------+--------------------- 4 |
7.0237691685684926 | 49.3333333333333333(1 row)*
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
On 22 June 2018 at 00:18, Rajkumar Raghuwanshi
<rajkumar.raghuwanshi@enterprisedb.com> wrote:
CREATE TABLE part (c1 INT,c2 INT) PARTITION BY RANGE (c1);
CREATE TABLE part_p1 PARTITION OF part FOR VALUES FROM (1) TO (3);
CREATE TABLE part_p2 PARTITION OF part FOR VALUES FROM (3) TO (5);INSERT INTO part VALUES (1,5),(2,15),(3,3),(4,17);
postgres=# SET parallel_setup_cost=0;
postgres=# SELECT COUNT(c1),STDDEV(c2),VARIANCE(c2) FROM part;
count | stddev | variance
-------+--------+----------
4 | 0 | 0
(1 row)
Well, that's quite surprising. It appears to be a bug in
numeric_poly_combine for machines without a working int128 type. The
parameters in accum_sum_copy are in the incorrect order.
The very minimal fix is attached, but I'll need to go look at where
the tests for this have gone.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
fix_numeric_poly_combine.patchapplication/octet-stream; name=fix_numeric_poly_combine.patchDownload
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 074294cbcc..82a14295ee 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -4218,8 +4218,8 @@ numeric_poly_combine(PG_FUNCTION_ARGS)
state1->sumX = state2->sumX;
state1->sumX2 = state2->sumX2;
#else
- accum_sum_copy(&state2->sumX, &state1->sumX);
- accum_sum_copy(&state2->sumX2, &state1->sumX2);
+ accum_sum_copy(&state1->sumX, &state2->sumX);
+ accum_sum_copy(&state1->sumX2, &state2->sumX2);
#endif
MemoryContextSwitchTo(old_context);
David Rowley <david.rowley@2ndquadrant.com> writes:
Well, that's quite surprising. It appears to be a bug in
numeric_poly_combine for machines without a working int128 type. The
parameters in accum_sum_copy are in the incorrect order.
Ouch.
The very minimal fix is attached, but I'll need to go look at where
the tests for this have gone.
coverage.postgresql.org shows that numeric_poly_serialize/combine()
aren't exercised at all by the regression tests. Which is embarrassing
for this case, but I'm a bit leery of trying to insist on 100% coverage.
It might be a plan to insist on buildfarm coverage for anything with
platform-varying code in it, in which case there's at least one
other undertested bit of HAVE_INT128 code in numeric.c.
regards, tom lane
On 22 June 2018 at 02:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Rowley <david.rowley@2ndquadrant.com> writes:
Well, that's quite surprising. It appears to be a bug in
numeric_poly_combine for machines without a working int128 type. The
parameters in accum_sum_copy are in the incorrect order.Ouch.
Yeah. Looks like this function was correct when it went in. It was
9cca11c915e (v10) that caused the issue.
The very minimal fix is attached, but I'll need to go look at where
the tests for this have gone.coverage.postgresql.org shows that numeric_poly_serialize/combine()
aren't exercised at all by the regression tests. Which is embarrassing
for this case, but I'm a bit leery of trying to insist on 100% coverage.It might be a plan to insist on buildfarm coverage for anything with
platform-varying code in it, in which case there's at least one
other undertested bit of HAVE_INT128 code in numeric.c.
I'm not familiar with what the coverage tool can do, so maybe there's
an easier way than running a script to check for 0 coverage between
#ifdef / #if and #endif inside all .c files.
This sounds like a longer-term project though, let's get this one fixed first.
I think some coverage of the numerical aggregates is a good idea, so
I've added some in the attached. I managed to get a parallel plan
going with a query to onek, which is pretty cheap to execute. I didn't
touch the bool aggregates. Maybe I should have done that too..?
I also did a quick validation of the other accum_sum_copy usages. All
others look correct.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
fix_numeric_poly_combine_v2.patchapplication/octet-stream; name=fix_numeric_poly_combine_v2.patchDownload
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 074294cbcc..82a14295ee 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -4218,8 +4218,8 @@ numeric_poly_combine(PG_FUNCTION_ARGS)
state1->sumX = state2->sumX;
state1->sumX2 = state2->sumX2;
#else
- accum_sum_copy(&state2->sumX, &state1->sumX);
- accum_sum_copy(&state2->sumX2, &state1->sumX2);
+ accum_sum_copy(&state1->sumX, &state2->sumX);
+ accum_sum_copy(&state1->sumX2, &state2->sumX2);
#endif
MemoryContextSwitchTo(old_context);
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index f85e913850..7bd5fdaf6a 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -2065,3 +2065,235 @@ SELECT balk(hundred) FROM tenk1;
(1 row)
ROLLBACK;
+-- Coverage for aggregate combine/serial/deserial functions
+SET parallel_setup_cost = 0;
+SET parallel_tuple_cost = 0;
+SET min_parallel_table_scan_size = 1;
+SET max_parallel_workers_per_gather = 2;
+SET enable_indexonlyscan = off;
+-- verify we get a parallel plan for each of these.
+EXPLAIN (COSTS OFF) SELECT sum(unique1::smallint),sum(unique1::bigint),sum(unique1::numeric),sum(unique1::float4),sum(unique1::float8) FROM onek;
+ QUERY PLAN
+---------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on onek
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT avg(unique1::smallint),avg(unique1::bigint),avg(unique1::numeric),avg(unique1::float4),avg(unique1::float8) FROM onek;
+ QUERY PLAN
+---------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on onek
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT min(unique1::smallint),min(unique1::bigint),min(unique1::numeric),min(unique1::float4),min(unique1::float8) FROM onek WHERE unique1 > 10;
+ QUERY PLAN
+---------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on onek
+ Filter: (unique1 > 10)
+(6 rows)
+
+EXPLAIN (COSTS OFF) SELECT max(unique1::smallint),max(unique1::bigint),max(unique1::numeric),max(unique1::float4),max(unique1::float8) FROM onek;
+ QUERY PLAN
+---------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on onek
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT stddev(unique1::smallint),stddev(unique1::bigint),stddev(unique1::numeric),stddev(unique1::float4),stddev(unique1::float8) FROM onek;
+ QUERY PLAN
+---------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on onek
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT stddev_pop(unique1::smallint),stddev_pop(unique1::bigint),stddev_pop(unique1::numeric),stddev_pop(unique1::float4),stddev_pop(unique1::float8) FROM onek;
+ QUERY PLAN
+---------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on onek
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT stddev_samp(unique1::smallint),stddev_samp(unique1::bigint),stddev_samp(unique1::numeric),stddev_samp(unique1::float4),stddev_samp(unique1::float8) FROM onek;
+ QUERY PLAN
+---------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on onek
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT var_pop(unique1::smallint),var_pop(unique1::bigint),var_pop(unique1::numeric),var_pop(unique1::float4),var_pop(unique1::float8) FROM onek;
+ QUERY PLAN
+---------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on onek
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT var_samp(unique1::smallint),var_samp(unique1::bigint),var_samp(unique1::numeric),var_samp(unique1::float4),var_samp(unique1::float8) FROM onek;
+ QUERY PLAN
+---------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on onek
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT variance(unique1::smallint),variance(unique1::bigint),variance(unique1::numeric),variance(unique1::float4),variance(unique1::float8) FROM onek;
+ QUERY PLAN
+---------------------------------------------
+ Finalize Aggregate
+ -> Gather
+ Workers Planned: 2
+ -> Partial Aggregate
+ -> Parallel Seq Scan on onek
+(5 rows)
+
+-- bulk check various aggregate functions.
+SELECT sum(unique1::smallint),sum(unique1::bigint),sum(unique1::numeric),sum(unique1::float4),sum(unique1::float8) FROM onek;
+ sum | sum | sum | sum | sum
+--------+--------+--------+--------+--------
+ 499500 | 499500 | 499500 | 499500 | 499500
+(1 row)
+
+SELECT avg(unique1::smallint),avg(unique1::bigint),avg(unique1::numeric),avg(unique1::float4),avg(unique1::float8) FROM onek;
+ avg | avg | avg | avg | avg
+----------------------+----------------------+----------------------+-------+-------
+ 499.5000000000000000 | 499.5000000000000000 | 499.5000000000000000 | 499.5 | 499.5
+(1 row)
+
+SELECT min(unique1::smallint),min(unique1::bigint),min(unique1::numeric),min(unique1::float4),min(unique1::float8) FROM onek WHERE unique1 > 10;
+ min | min | min | min | min
+-----+-----+-----+-----+-----
+ 11 | 11 | 11 | 11 | 11
+(1 row)
+
+SELECT max(unique1::smallint),max(unique1::bigint),max(unique1::numeric),max(unique1::float4),max(unique1::float8) FROM onek;
+ max | max | max | max | max
+-----+-----+-----+-----+-----
+ 999 | 999 | 999 | 999 | 999
+(1 row)
+
+SELECT stddev(unique1::smallint),stddev(unique1::bigint),stddev(unique1::numeric),stddev(unique1::float4),stddev(unique1::float8) FROM onek;
+ stddev | stddev | stddev | stddev | stddev
+------------------+------------------+------------------+------------------+------------------
+ 288.819436095749 | 288.819436095749 | 288.819436095749 | 288.819436095749 | 288.819436095749
+(1 row)
+
+SELECT stddev_pop(unique1::smallint),stddev_pop(unique1::bigint),stddev_pop(unique1::numeric),stddev_pop(unique1::float4),stddev_pop(unique1::float8) FROM onek;
+ stddev_pop | stddev_pop | stddev_pop | stddev_pop | stddev_pop
+------------------+------------------+------------------+-----------------+-----------------
+ 288.674990257210 | 288.674990257210 | 288.674990257210 | 288.67499025721 | 288.67499025721
+(1 row)
+
+SELECT stddev_samp(unique1::smallint),stddev_samp(unique1::bigint),stddev_samp(unique1::numeric),stddev_samp(unique1::float4),stddev_samp(unique1::float8) FROM onek;
+ stddev_samp | stddev_samp | stddev_samp | stddev_samp | stddev_samp
+------------------+------------------+------------------+------------------+------------------
+ 288.819436095749 | 288.819436095749 | 288.819436095749 | 288.819436095749 | 288.819436095749
+(1 row)
+
+SELECT var_pop(unique1::smallint),var_pop(unique1::bigint),var_pop(unique1::numeric),var_pop(unique1::float4),var_pop(unique1::float8) FROM onek;
+ var_pop | var_pop | var_pop | var_pop | var_pop
+--------------------+--------------------+--------------------+----------+----------
+ 83333.250000000000 | 83333.250000000000 | 83333.250000000000 | 83333.25 | 83333.25
+(1 row)
+
+SELECT var_samp(unique1::smallint),var_samp(unique1::bigint),var_samp(unique1::numeric),var_samp(unique1::float4),var_samp(unique1::float8) FROM onek;
+ var_samp | var_samp | var_samp | var_samp | var_samp
+--------------------+--------------------+--------------------+------------------+------------------
+ 83416.666666666667 | 83416.666666666667 | 83416.666666666667 | 83416.6666666667 | 83416.6666666667
+(1 row)
+
+SELECT variance(unique1::smallint),variance(unique1::bigint),variance(unique1::numeric),variance(unique1::float4),variance(unique1::float8) FROM onek;
+ variance | variance | variance | variance | variance
+--------------------+--------------------+--------------------+------------------+------------------
+ 83416.666666666667 | 83416.666666666667 | 83416.666666666667 | 83416.6666666667 | 83416.6666666667
+(1 row)
+
+-- disable parallel query
+set max_parallel_workers_per_gather = 0;
+-- Results should match above.
+SELECT sum(unique1::smallint),sum(unique1::bigint),sum(unique1::numeric),sum(unique1::float4),sum(unique1::float8) FROM onek;
+ sum | sum | sum | sum | sum
+--------+--------+--------+--------+--------
+ 499500 | 499500 | 499500 | 499500 | 499500
+(1 row)
+
+SELECT avg(unique1::smallint),avg(unique1::bigint),avg(unique1::numeric),avg(unique1::float4),avg(unique1::float8) FROM onek;
+ avg | avg | avg | avg | avg
+----------------------+----------------------+----------------------+-------+-------
+ 499.5000000000000000 | 499.5000000000000000 | 499.5000000000000000 | 499.5 | 499.5
+(1 row)
+
+SELECT min(unique1::smallint),min(unique1::bigint),min(unique1::numeric),min(unique1::float4),min(unique1::float8) FROM onek WHERE unique1 > 10;
+ min | min | min | min | min
+-----+-----+-----+-----+-----
+ 11 | 11 | 11 | 11 | 11
+(1 row)
+
+SELECT max(unique1::smallint),max(unique1::bigint),max(unique1::numeric),max(unique1::float4),max(unique1::float8) FROM onek;
+ max | max | max | max | max
+-----+-----+-----+-----+-----
+ 999 | 999 | 999 | 999 | 999
+(1 row)
+
+SELECT stddev(unique1::smallint),stddev(unique1::bigint),stddev(unique1::numeric),stddev(unique1::float4),stddev(unique1::float8) FROM onek;
+ stddev | stddev | stddev | stddev | stddev
+------------------+------------------+------------------+------------------+------------------
+ 288.819436095749 | 288.819436095749 | 288.819436095749 | 288.819436095749 | 288.819436095749
+(1 row)
+
+SELECT stddev_pop(unique1::smallint),stddev_pop(unique1::bigint),stddev_pop(unique1::numeric),stddev_pop(unique1::float4),stddev_pop(unique1::float8) FROM onek;
+ stddev_pop | stddev_pop | stddev_pop | stddev_pop | stddev_pop
+------------------+------------------+------------------+-----------------+-----------------
+ 288.674990257210 | 288.674990257210 | 288.674990257210 | 288.67499025721 | 288.67499025721
+(1 row)
+
+SELECT stddev_samp(unique1::smallint),stddev_samp(unique1::bigint),stddev_samp(unique1::numeric),stddev_samp(unique1::float4),stddev_samp(unique1::float8) FROM onek;
+ stddev_samp | stddev_samp | stddev_samp | stddev_samp | stddev_samp
+------------------+------------------+------------------+------------------+------------------
+ 288.819436095749 | 288.819436095749 | 288.819436095749 | 288.819436095749 | 288.819436095749
+(1 row)
+
+SELECT var_pop(unique1::smallint),var_pop(unique1::bigint),var_pop(unique1::numeric),var_pop(unique1::float4),var_pop(unique1::float8) FROM onek;
+ var_pop | var_pop | var_pop | var_pop | var_pop
+--------------------+--------------------+--------------------+----------+----------
+ 83333.250000000000 | 83333.250000000000 | 83333.250000000000 | 83333.25 | 83333.25
+(1 row)
+
+SELECT var_samp(unique1::smallint),var_samp(unique1::bigint),var_samp(unique1::numeric),var_samp(unique1::float4),var_samp(unique1::float8) FROM onek;
+ var_samp | var_samp | var_samp | var_samp | var_samp
+--------------------+--------------------+--------------------+------------------+------------------
+ 83416.666666666667 | 83416.666666666667 | 83416.666666666667 | 83416.6666666667 | 83416.6666666667
+(1 row)
+
+SELECT variance(unique1::smallint),variance(unique1::bigint),variance(unique1::numeric),variance(unique1::float4),variance(unique1::float8) FROM onek;
+ variance | variance | variance | variance | variance
+--------------------+--------------------+--------------------+------------------+------------------
+ 83416.666666666667 | 83416.666666666667 | 83416.666666666667 | 83416.6666666667 | 83416.6666666667
+(1 row)
+
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 506d0442d7..54f7358ffe 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -907,3 +907,49 @@ EXPLAIN (COSTS OFF) SELECT balk(hundred) FROM tenk1;
SELECT balk(hundred) FROM tenk1;
ROLLBACK;
+
+-- Coverage for aggregate combine/serial/deserial functions
+SET parallel_setup_cost = 0;
+SET parallel_tuple_cost = 0;
+SET min_parallel_table_scan_size = 1;
+SET max_parallel_workers_per_gather = 2;
+SET enable_indexonlyscan = off;
+
+-- verify we get a parallel plan for each of these.
+EXPLAIN (COSTS OFF) SELECT sum(unique1::smallint),sum(unique1::bigint),sum(unique1::numeric),sum(unique1::float4),sum(unique1::float8) FROM onek;
+EXPLAIN (COSTS OFF) SELECT avg(unique1::smallint),avg(unique1::bigint),avg(unique1::numeric),avg(unique1::float4),avg(unique1::float8) FROM onek;
+EXPLAIN (COSTS OFF) SELECT min(unique1::smallint),min(unique1::bigint),min(unique1::numeric),min(unique1::float4),min(unique1::float8) FROM onek WHERE unique1 > 10;
+EXPLAIN (COSTS OFF) SELECT max(unique1::smallint),max(unique1::bigint),max(unique1::numeric),max(unique1::float4),max(unique1::float8) FROM onek;
+EXPLAIN (COSTS OFF) SELECT stddev(unique1::smallint),stddev(unique1::bigint),stddev(unique1::numeric),stddev(unique1::float4),stddev(unique1::float8) FROM onek;
+EXPLAIN (COSTS OFF) SELECT stddev_pop(unique1::smallint),stddev_pop(unique1::bigint),stddev_pop(unique1::numeric),stddev_pop(unique1::float4),stddev_pop(unique1::float8) FROM onek;
+EXPLAIN (COSTS OFF) SELECT stddev_samp(unique1::smallint),stddev_samp(unique1::bigint),stddev_samp(unique1::numeric),stddev_samp(unique1::float4),stddev_samp(unique1::float8) FROM onek;
+EXPLAIN (COSTS OFF) SELECT var_pop(unique1::smallint),var_pop(unique1::bigint),var_pop(unique1::numeric),var_pop(unique1::float4),var_pop(unique1::float8) FROM onek;
+EXPLAIN (COSTS OFF) SELECT var_samp(unique1::smallint),var_samp(unique1::bigint),var_samp(unique1::numeric),var_samp(unique1::float4),var_samp(unique1::float8) FROM onek;
+EXPLAIN (COSTS OFF) SELECT variance(unique1::smallint),variance(unique1::bigint),variance(unique1::numeric),variance(unique1::float4),variance(unique1::float8) FROM onek;
+
+-- bulk check various aggregate functions.
+SELECT sum(unique1::smallint),sum(unique1::bigint),sum(unique1::numeric),sum(unique1::float4),sum(unique1::float8) FROM onek;
+SELECT avg(unique1::smallint),avg(unique1::bigint),avg(unique1::numeric),avg(unique1::float4),avg(unique1::float8) FROM onek;
+SELECT min(unique1::smallint),min(unique1::bigint),min(unique1::numeric),min(unique1::float4),min(unique1::float8) FROM onek WHERE unique1 > 10;
+SELECT max(unique1::smallint),max(unique1::bigint),max(unique1::numeric),max(unique1::float4),max(unique1::float8) FROM onek;
+SELECT stddev(unique1::smallint),stddev(unique1::bigint),stddev(unique1::numeric),stddev(unique1::float4),stddev(unique1::float8) FROM onek;
+SELECT stddev_pop(unique1::smallint),stddev_pop(unique1::bigint),stddev_pop(unique1::numeric),stddev_pop(unique1::float4),stddev_pop(unique1::float8) FROM onek;
+SELECT stddev_samp(unique1::smallint),stddev_samp(unique1::bigint),stddev_samp(unique1::numeric),stddev_samp(unique1::float4),stddev_samp(unique1::float8) FROM onek;
+SELECT var_pop(unique1::smallint),var_pop(unique1::bigint),var_pop(unique1::numeric),var_pop(unique1::float4),var_pop(unique1::float8) FROM onek;
+SELECT var_samp(unique1::smallint),var_samp(unique1::bigint),var_samp(unique1::numeric),var_samp(unique1::float4),var_samp(unique1::float8) FROM onek;
+SELECT variance(unique1::smallint),variance(unique1::bigint),variance(unique1::numeric),variance(unique1::float4),variance(unique1::float8) FROM onek;
+
+-- disable parallel query
+set max_parallel_workers_per_gather = 0;
+
+-- Results should match above.
+SELECT sum(unique1::smallint),sum(unique1::bigint),sum(unique1::numeric),sum(unique1::float4),sum(unique1::float8) FROM onek;
+SELECT avg(unique1::smallint),avg(unique1::bigint),avg(unique1::numeric),avg(unique1::float4),avg(unique1::float8) FROM onek;
+SELECT min(unique1::smallint),min(unique1::bigint),min(unique1::numeric),min(unique1::float4),min(unique1::float8) FROM onek WHERE unique1 > 10;
+SELECT max(unique1::smallint),max(unique1::bigint),max(unique1::numeric),max(unique1::float4),max(unique1::float8) FROM onek;
+SELECT stddev(unique1::smallint),stddev(unique1::bigint),stddev(unique1::numeric),stddev(unique1::float4),stddev(unique1::float8) FROM onek;
+SELECT stddev_pop(unique1::smallint),stddev_pop(unique1::bigint),stddev_pop(unique1::numeric),stddev_pop(unique1::float4),stddev_pop(unique1::float8) FROM onek;
+SELECT stddev_samp(unique1::smallint),stddev_samp(unique1::bigint),stddev_samp(unique1::numeric),stddev_samp(unique1::float4),stddev_samp(unique1::float8) FROM onek;
+SELECT var_pop(unique1::smallint),var_pop(unique1::bigint),var_pop(unique1::numeric),var_pop(unique1::float4),var_pop(unique1::float8) FROM onek;
+SELECT var_samp(unique1::smallint),var_samp(unique1::bigint),var_samp(unique1::numeric),var_samp(unique1::float4),var_samp(unique1::float8) FROM onek;
+SELECT variance(unique1::smallint),variance(unique1::bigint),variance(unique1::numeric),variance(unique1::float4),variance(unique1::float8) FROM onek;
On 22 June 2018 at 03:11, David Rowley <david.rowley@2ndquadrant.com> wrote:
I think some coverage of the numerical aggregates is a good idea, so
I've added some in the attached. I managed to get a parallel plan
going with a query to onek, which is pretty cheap to execute. I didn't
touch the bool aggregates. Maybe I should have done that too..?
I should have mentioned; I tested this on a Windows VS2012 machine and
a Linux x86-64 machine and those results were fine with each. I think
the float4 and float8 aggregate results should be fairly stable since
the numbers being aggregated are all very far from the extreme range
of those types. If we do get some problems with some buildfarm members
producing slightly different results then we can probably cast the
result of the aggregate to numeric and round() them a bit.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
David Rowley <david.rowley@2ndquadrant.com> writes:
On 22 June 2018 at 02:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
coverage.postgresql.org shows that numeric_poly_serialize/combine()
aren't exercised at all by the regression tests. Which is embarrassing
for this case, but I'm a bit leery of trying to insist on 100% coverage.
It might be a plan to insist on buildfarm coverage for anything with
platform-varying code in it, in which case there's at least one
other undertested bit of HAVE_INT128 code in numeric.c.
I think some coverage of the numerical aggregates is a good idea, so
I've added some in the attached. I managed to get a parallel plan
going with a query to onek, which is pretty cheap to execute. I didn't
touch the bool aggregates. Maybe I should have done that too..?
This sort of blunderbuss testing was exactly what I *didn't* want to do.
Not only is this adding about 20x as many cycles as we need (at least for
this specific numeric_poly_combine issue), but I'm quite afraid that the
float4 and/or float8 cases will show low-order-digit irreproducibility
in the buildfarm. I think we should look at the coverage report for the
files in question and add targeted tests to cover gaps where there's
platform-varying code, such that the buildfarm might expose problems
that were missed by the code's author.
regards, tom lane
On 22 June 2018 at 03:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think some coverage of the numerical aggregates is a good idea, so
I've added some in the attached. I managed to get a parallel plan
going with a query to onek, which is pretty cheap to execute. I didn't
touch the bool aggregates. Maybe I should have done that too..?This sort of blunderbuss testing was exactly what I *didn't* want to do.
Not only is this adding about 20x as many cycles as we need (at least for
this specific numeric_poly_combine issue), but I'm quite afraid that the
float4 and/or float8 cases will show low-order-digit irreproducibility
in the buildfarm.
okay. My sniper rifle was locked away for the evening. I decided it
was best to sleep before any careful aiming was required.
I see you've done the deed already. Thanks.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Thanks for commit. I have verified reported case. it is fixed now.
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
On Fri, Jun 22, 2018 at 8:38 AM, David Rowley <david.rowley@2ndquadrant.com>
wrote:
Show quoted text
On 22 June 2018 at 03:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think some coverage of the numerical aggregates is a good idea, so
I've added some in the attached. I managed to get a parallel plan
going with a query to onek, which is pretty cheap to execute. I didn't
touch the bool aggregates. Maybe I should have done that too..?This sort of blunderbuss testing was exactly what I *didn't* want to do.
Not only is this adding about 20x as many cycles as we need (at least for
this specific numeric_poly_combine issue), but I'm quite afraid that the
float4 and/or float8 cases will show low-order-digit irreproducibility
in the buildfarm.okay. My sniper rifle was locked away for the evening. I decided it
was best to sleep before any careful aiming was required.I see you've done the deed already. Thanks.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services