On Mon, Jun 18, 2018 at 5:02 PM, Rajkumar Raghuwanshi <
rajkumar.raghuwan...@enterprisedb.com> wrote:
> Hi,
>
> Below test case crashed, when set enable_partitionwise_aggregate to true.
>
I will have a look over this.
Thanks for reporting.
>
> 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
>
--
Jeevan Chalke
Technical Architect, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
