Hi,
Below test case crashed, when set enable_partitionwise_aggregate to true.
CREATE TABLE part (c1 INTEGER,c2 INTEGER,c3 CHAR(10)) PARTITION BY
RANGE(c1);
CREATE TABLE part_p1 PARTITION OF part FOR VALUES FROM (MINVALUE) TO (500);
CREATE TABLE part_p2 PARTITION OF part FOR VALUES FROM (500) TO (1000);
CREATE TABLE part_p3 PARTITION OF part FOR VALUES FROM (1000) TO (MAXVALUE);
INSERT INTO part SELECT i,i % 250, to_char(i % 4, 'FM0000') FROM
GENERATE_SERIES(1,1500,2)i;
ANALYSE part;
ALTER TABLE part_p1 SET (parallel_workers = 0);
ALTER TABLE part_p2 SET (parallel_workers = 0);
ALTER TABLE part_p3 SET (parallel_workers = 0);
SET enable_partitionwise_join to on;
set enable_partitionwise_aggregate to off;
EXPLAIN (COSTS OFF)
SELECT AVG(t2.c1),SUM(t1.c1) FROM part t1 INNER JOIN part t2 ON (t1.c1 =
t2.c1) GROUP BY t1.c1, t2.c1 HAVING SUM(t1.c1) % 125 = 0 ORDER BY 1,2;
set enable_partitionwise_aggregate to on;
EXPLAIN (COSTS OFF)
SELECT AVG(t2.c1),SUM(t1.c1) FROM part t1 INNER JOIN part t2 ON (t1.c1 =
t2.c1) GROUP BY t1.c1, t2.c1 HAVING SUM(t1.c1) % 125 = 0 ORDER BY 1,2;
/*
postgres=# set enable_partitionwise_aggregate to off;
SET
postgres=# EXPLAIN (COSTS OFF)
postgres-# SELECT AVG(t2.c1),SUM(t1.c1) FROM part t1 INNER JOIN part t2 ON
(t1.c1 = t2.c1) GROUP BY t1.c1, t2.c1 HAVING SUM(t1.c1) % 125 = 0 ORDER BY
1,2;
QUERY PLAN
--------------------------------------------------------
Sort
Sort Key: (avg(t2.c1)), (sum(t1.c1))
-> HashAggregate
Group Key: t1.c1, t2.c1
Filter: ((sum(t1.c1) % '125'::bigint) = 0)
-> Append
-> Hash Join
Hash Cond: (t1.c1 = t2.c1)
-> Seq Scan on part_p1 t1
-> Hash
-> Seq Scan on part_p1 t2
-> Hash Join
Hash Cond: (t1_1.c1 = t2_1.c1)
-> Seq Scan on part_p2 t1_1
-> Hash
-> Seq Scan on part_p2 t2_1
-> Hash Join
Hash Cond: (t1_2.c1 = t2_2.c1)
-> Seq Scan on part_p3 t1_2
-> Hash
-> Seq Scan on part_p3 t2_2
(21 rows)
postgres=#
postgres=# set enable_partitionwise_aggregate to on;
SET
postgres=# EXPLAIN (COSTS OFF)
postgres-# SELECT AVG(t2.c1),SUM(t1.c1) FROM part t1 INNER JOIN part t2 ON
(t1.c1 = t2.c1) GROUP BY t1.c1, t2.c1 HAVING SUM(t1.c1) % 125 = 0 ORDER BY
1,2;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q
*/
--logfile
TRAP: FailedAssertion("!(parallel_workers > 0)", File: "allpaths.c", Line:
1630)
2018-06-14 23:24:58.375 IST [69650] LOG: server process (PID 69660) was
terminated by signal 6: Aborted
2018-06-14 23:24:58.375 IST [69650] DETAIL: Failed process was running:
EXPLAIN (COSTS OFF)
SELECT AVG(t2.c1),SUM(t1.c1) FROM part t1 INNER JOIN part t2 ON
(t1.c1 = t2.c1) GROUP BY t1.c1, t2.c1 HAVING SUM(t1.c1) % 125 = 0 ORDER BY
1,2;
--core.file
Loaded symbols for /lib64/libnss_files.so.2
Core was generated by `postgres: edb postgres [local]
EXPLAIN '.
Program terminated with signal 6, Aborted.
#0 0x0000003dd2632495 in raise (sig=6) at ../nptl/sysdeps/unix/sysv/linu
x/raise.c:64
64 return INLINE_SYSCALL (tgkill, 3, pid, selftid, sig);
Missing separate debuginfos, use: debuginfo-install
keyutils-libs-1.4-5.el6.x86_64 krb5-libs-1.10.3-65.el6.x86_64
libcom_err-1.41.12-23.el6.x86_64 libselinux-2.0.94-7.el6.x86_64
openssl-1.0.1e-57.el6.x86_64 zlib-1.2.3-29.el6.x86_64
(gdb) bt
#0 0x0000003dd2632495 in raise (sig=6) at ../nptl/sysdeps/unix/sysv/linu
x/raise.c:64
#1 0x0000003dd2633c75 in abort () at abort.c:92
#2 0x0000000000a326da in ExceptionalCondition (conditionName=0xc1a970
"!(parallel_workers > 0)", errorType=0xc1a426 "FailedAssertion",
fileName=0xc1a476 "allpaths.c",
lineNumber=1630) at assert.c:54
#3 0x0000000000797bda in add_paths_to_append_rel (root=0x1d6ff08,
rel=0x1d45d80, live_childrels=0x0) at allpaths.c:1630
#4 0x00000000007d37e1 in create_partitionwise_grouping_paths
(root=0x1d6ff08, input_rel=0x1da5380, grouped_rel=0x1d43520,
partially_grouped_rel=0x1d45d80,
agg_costs=0x7ffceb18dd20, gd=0x0, patype=PARTITIONWISE_AGGREGATE_FULL,
extra=0x7ffceb18dbe0) at planner.c:7120
#5 0x00000000007ce58d in create_ordinary_grouping_paths (root=0x1d6ff08,
input_rel=0x1da5380, grouped_rel=0x1d43520, agg_costs=0x7ffceb18dd20,
gd=0x0, extra=0x7ffceb18dbe0,
partially_grouped_rel_p=0x7ffceb18dc70) at planner.c:4011
#6 0x00000000007ce14b in create_grouping_paths (root=0x1d6ff08,
input_rel=0x1da5380, target=0x1d446d0, target_parallel_safe=true,
agg_costs=0x7ffceb18dd20, gd=0x0)
at planner.c:3783
#7 0x00000000007cb344 in grouping_planner (root=0x1d6ff08,
inheritance_update=false, tuple_fraction=0) at planner.c:2037
#8 0x00000000007c94e6 in subquery_planner (glob=0x1d6fe70,
parse=0x1d2a658, parent_root=0x0, hasRecursion=false, tuple_fraction=0) at
planner.c:966
#9 0x00000000007c80a3 in standard_planner (parse=0x1d2a658,
cursorOptions=256, boundParams=0x0) at planner.c:405
#10 0x00000000007c7dcb in planner (parse=0x1d2a658, cursorOptions=256,
boundParams=0x0) at planner.c:263
#11 0x00000000008c4576 in pg_plan_query (querytree=0x1d2a658,
cursorOptions=256, boundParams=0x0) at postgres.c:809
#12 0x000000000064a1d0 in ExplainOneQuery (query=0x1d2a658,
cursorOptions=256, into=0x0, es=0x1d24460,
queryString=0x1c68798 "EXPLAIN (COSTS OFF)\nSELECT
AVG(t2.c1),SUM(t1.c1) FROM part t1 INNER JOIN part t2 ON (t1.c1 = t2.c1)
GROUP BY t1.c1, t2.c1 HAVING SUM(t1.c1) % 125 = 0 ORDER BY 1,2;",
params=0x0, queryEnv=0x0) at explain.c:365
#13 0x0000000000649ed2 in ExplainQuery (pstate=0x1c8be28, stmt=0x1d34b08,
queryString=0x1c68798 "EXPLAIN (COSTS OFF)\nSELECT
AVG(t2.c1),SUM(t1.c1) FROM part t1 INNER JOIN part t2 ON (t1.c1 = t2.c1)
GROUP BY t1.c1, t2.c1 HAVING SUM(t1.c1) % 125 = 0 ORDER BY 1,2;",
params=0x0, queryEnv=0x0, dest=0x1c8bd90) at explain.c:254
#14 0x00000000008ccd99 in standard_ProcessUtility (pstmt=0x1d34bd8,
queryString=0x1c68798 "EXPLAIN (COSTS OFF)\nSELECT
AVG(t2.c1),SUM(t1.c1) FROM part t1 INNER JOIN part t2 ON (t1.c1 = t2.c1)
GROUP BY t1.c1, t2.c1 HAVING SUM(t1.c1) % 125 = 0 ORDER BY 1,2;",
context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x1c8bd90,
completionTag=0x7ffceb18e450 "") at utility.c:672
#15 0x00000000008cc520 in ProcessUtility (pstmt=0x1d34bd8,
queryString=0x1c68798 "EXPLAIN (COSTS OFF)\nSELECT
AVG(t2.c1),SUM(t1.c1) FROM part t1 INNER JOIN part t2 ON (t1.c1 = t2.c1)
GROUP BY t1.c1, t2.c1 HAVING SUM(t1.c1) % 125 = 0 ORDER BY 1,2;",
context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x1c8bd90,
completionTag=0x7ffceb18e450 "") at utility.c:360
#16 0x00000000008cb4ce in PortalRunUtility (portal=0x1ccdc28,
pstmt=0x1d34bd8, isTopLevel=true, setHoldSnapshot=true, dest=0x1c8bd90,
completionTag=0x7ffceb18e450 "")
at pquery.c:1178
#17 0x00000000008cb1c5 in FillPortalStore (portal=0x1ccdc28,
isTopLevel=true) at pquery.c:1038
#18 0x00000000008caaf6 in PortalRun (portal=0x1ccdc28,
count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x1d6d9e8,
altdest=0x1d6d9e8,
completionTag=0x7ffceb18e650 "") at pquery.c:768
#19 0x00000000008c4aef in exec_simple_query (
query_string=0x1c68798 "EXPLAIN (COSTS OFF)\nSELECT
AVG(t2.c1),SUM(t1.c1) FROM part t1 INNER JOIN part t2 ON (t1.c1 = t2.c1)
GROUP BY t1.c1, t2.c1 HAVING SUM(t1.c1) % 125 = 0 ORDER BY 1,2;") at
postgres.c:1122
#20 0x00000000008c8dbf in PostgresMain (argc=1, argv=0x1c922a0,
dbname=0x1c92100 "postgres", username=0x1c65298 "edb") at postgres.c:4153
#21 0x0000000000826703 in BackendRun (port=0x1c8a060) at postmaster.c:4361
#22 0x0000000000825e71 in BackendStartup (port=0x1c8a060) at
postmaster.c:4033
#23 0x0000000000822253 in ServerLoop () at postmaster.c:1706
#24 0x0000000000821b85 in PostmasterMain (argc=3, argv=0x1c631f0) at
postmaster.c:1379
#25 0x0000000000748d64 in main (argc=3, argv=0x1c631f0) at main.c:228
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
