Re: [HACKERS] advanced partition matching algorithm for partition-wise join

On Thu, Mar 7, 2019 at 8:20 PM amul sul <sulamul(at)gmail(dot)com> wrote:

>
>
> On Thu, Mar 7, 2019 at 1:02 PM amul sul <sulamul(at)gmail(dot)com> wrote:
>
>> Thanks Rajkumar,
>>
>> I am looking into this.
>>
>>
> The crash happens when none of the if-else branch of
> handle_missing_partition()
> evaluates and returns merged_index unassigned.
>
> Let me explain, in Rajkumar 's test case, the join type is JOIN_INNER.
> When
> only outer rel has null partition, merge_null_partitions() function calls
> handle_missing_partition() with missing_side_inner = false and
> missing_side_outer = false argument value which fails to set merged_index.
>
> In the attached patch, I tried to fix this case by setting merged_index
> explicitly which fixes the reported crash.
>
Thanks Amul, with v20 patches, crash is fixed.

>
> Regards,
> Amul
>
>
>
>> On Thu, Mar 7, 2019 at 11:54 AM Rajkumar Raghuwanshi <
>> rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
>>
>>>
>>>
>>> On Tue, Mar 5, 2019 at 3:45 PM amul sul <sulamul(at)gmail(dot)com> wrote:
>>>
>>>> Attached is the rebased atop of the latest master head(35bc0ec7c8).
>>>>
>>> thanks Amul, patches applied cleanly on PG head.
>>>
>>> While testing this I got a server crash with below test case.
>>>
>>> CREATE TABLE plt1 (a int, b int, c varchar) PARTITION BY LIST(c);
>>> CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN
>>> ('0001','0002','0003');
>>> CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN
>>> ('0004','0005','0006');
>>> CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN
>>> (NULL,'0008','0009');
>>> CREATE TABLE plt1_p4 PARTITION OF plt1 FOR VALUES IN ('0000','0010');
>>> INSERT INTO plt1 SELECT i, i % 47, to_char(i % 17, 'FM0000') FROM
>>> generate_series(0, 500) i WHERE i % 17 NOT IN (7, 11, 12, 13, 14, 15,16);
>>> INSERT INTO plt1 SELECT i, i % 47, case when i % 17 = 7 then NULL else
>>> to_char(i % 17, 'FM0000') end FROM generate_series(0, 500) i WHERE i % 17
>>> IN (7,8,9);
>>> ANALYSE plt1;
>>>
>>> CREATE TABLE plt2 (a int, b int, c varchar) PARTITION BY LIST(c);
>>> CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0002','0003');
>>> CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN
>>> ('0004','0005','0006');
>>> CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN
>>> ('0007','0008','0009');
>>> CREATE TABLE plt2_p4 PARTITION OF plt2 FOR VALUES IN
>>> ('0000',NULL,'0012');
>>> INSERT INTO plt2 SELECT i, i % 47, to_char(i % 17, 'FM0000') FROM
>>> generate_series(0, 500) i WHERE i % 17 NOT IN (1, 10, 11, 13, 14, 15, 16);
>>> INSERT INTO plt2 SELECT i, i % 47, case when i % 17 = 11 then NULL else
>>> to_char(i % 17, 'FM0000') end FROM generate_series(0, 500) i WHERE i % 17
>>> IN (0,11,12);
>>> ANALYZE plt2;
>>>
>>> CREATE TABLE plt1_e (a int, b int, c text) PARTITION BY LIST(ltrim(c,
>>> 'A'));
>>> CREATE TABLE plt1_e_p1 PARTITION OF plt1_e FOR VALUES IN ('0002',
>>> '0003');
>>> CREATE TABLE plt1_e_p2 PARTITION OF plt1_e FOR VALUES IN ('0004',
>>> '0005', '0006');
>>> CREATE TABLE plt1_e_p3 PARTITION OF plt1_e FOR VALUES IN ('0008',
>>> '0009');
>>> CREATE TABLE plt1_e_p4 PARTITION OF plt1_e FOR VALUES IN ('0000');
>>> INSERT INTO plt1_e SELECT i, i % 47, to_char(i % 17, 'FM0000') FROM
>>> generate_series(0, 500) i WHERE i % 17 NOT IN (1, 7, 10, 11, 12, 13, 14,
>>> 15, 16);
>>> ANALYZE plt1_e;
>>>
>>> EXPLAIN (COSTS OFF)
>>> SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM
>>> plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') =
>>> t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
>>> 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.
>>>
>>> below is stack trace, looks like some indexes got corrupted, please
>>> take a look.
>>>
>>> Core was generated by `postgres: edb postgres [local]
>>> EXPLAIN '.
>>> Program terminated with signal 11, Segmentation fault.
>>> #0 0x0000000000821aee in map_and_merge_partitions (partmaps1=0x2c1c8a8,
>>> partmaps2=0x2c1c8e0, index1=45540240, index2=0, next_index=0x7ffeebd43d3c)
>>> at partbounds.c:4162
>>> 4162 if (partmap1->from < 0 && partmap2->from < 0)
>>> 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-24.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 0x0000000000821aee in map_and_merge_partitions (partmaps1=0x2c1c8a8,
>>> partmaps2=0x2c1c8e0, *index1=45540240*, index2=0,
>>> next_index=0x7ffeebd43d3c) at partbounds.c:4162
>>> #1 0x00000000008226c3 in merge_null_partitions (outer_bi=0x2b6e338,
>>> inner_bi=0x2bf90b0, outer_maps=0x2c1c8a8, inner_maps=0x2c1c8e0,
>>> jointype=JOIN_INNER,
>>> next_index=0x7ffeebd43d3c, null_index=0x7ffeebd43d38,
>>> default_index=0x7ffeebd43d34) at partbounds.c:4610
>>> #2 0x0000000000821726 in partition_list_bounds_merge
>>> (partsupfunc=0x2ba3548, partcollation=0x2ba34e8, outer_rel=0x2b6ce40,
>>> inner_rel=0x2bf8d28, outer_parts=0x7ffeebd43ed8,
>>> inner_parts=0x7ffeebd43ed0, jointype=JOIN_INNER) at partbounds.c:4031
>>> #3 0x000000000081ff5d in partition_bounds_merge (partnatts=1,
>>> partsupfunc=0x2ba3548, partcollation=0x2ba34e8, outer_rel=0x2b6ce40,
>>> inner_rel=0x2bf8d28, jointype=JOIN_INNER,
>>> outer_parts=0x7ffeebd43ed8, inner_parts=0x7ffeebd43ed0) at
>>> partbounds.c:3053
>>> #4 0x00000000007c610f in try_partitionwise_join (root=0x2be2a28,
>>> rel1=0x2b6ce40, rel2=0x2bf8d28, joinrel=0x2c1b0f0,
>>> parent_sjinfo=0x7ffeebd44010,
>>> parent_restrictlist=0x2c1c070) at joinrels.c:1370
>>> #5 0x00000000007c5521 in populate_joinrel_with_paths (root=0x2be2a28,
>>> rel1=0x2b6ce40, rel2=0x2bf8d28, joinrel=0x2c1b0f0, sjinfo=0x7ffeebd44010,
>>> restrictlist=0x2c1c070)
>>> at joinrels.c:914
>>> #6 0x00000000007c4f48 in make_join_rel (root=0x2be2a28, rel1=0x2b6ce40,
>>> rel2=0x2bf8d28) at joinrels.c:748
>>> #7 0x00000000007c4514 in make_rels_by_clause_joins (root=0x2be2a28,
>>> old_rel=0x2b6ce40, other_rels=0x2bae4d8) at joinrels.c:294
>>> #8 0x00000000007c41c8 in join_search_one_level (root=0x2be2a28,
>>> level=3) at joinrels.c:116
>>> #9 0x00000000007abe59 in standard_join_search (root=0x2be2a28,
>>> levels_needed=3, initial_rels=0x2bae500) at allpaths.c:2716
>>> #10 0x00000000007abdca in make_rel_from_joinlist (root=0x2be2a28,
>>> joinlist=0x2bfbae8) at allpaths.c:2647
>>> #11 0x00000000007a86b0 in make_one_rel (root=0x2be2a28,
>>> joinlist=0x2bfbae8) at allpaths.c:227
>>> #12 0x00000000007dada1 in query_planner (root=0x2be2a28,
>>> tlist=0x2ba01c8, qp_callback=0x7e0b25 <standard_qp_callback>,
>>> qp_extra=0x7ffeebd44390) at planmain.c:265
>>> #13 0x00000000007ddf83 in grouping_planner (root=0x2be2a28,
>>> inheritance_update=false, tuple_fraction=0) at planner.c:1929
>>> #14 0x00000000007dc5f5 in subquery_planner (glob=0x2be2990,
>>> parse=0x2c0e8c8, parent_root=0x0, hasRecursion=false, tuple_fraction=0) at
>>> planner.c:997
>>> #15 0x00000000007db1b6 in standard_planner (parse=0x2c0e8c8,
>>> cursorOptions=256, boundParams=0x0) at planner.c:416
>>> #16 0x00000000007daef7 in planner (parse=0x2c0e8c8, cursorOptions=256,
>>> boundParams=0x0) at planner.c:276
>>> #17 0x00000000008e15c5 in pg_plan_query (querytree=0x2c0e8c8,
>>> cursorOptions=256, boundParams=0x0) at postgres.c:878
>>> #18 0x00000000006562cc in ExplainOneQuery (query=0x2c0e8c8,
>>> cursorOptions=256, into=0x0, es=0x2c0e0a0,
>>> queryString=0x2aa24d8 "EXPLAIN (COSTS OFF)\nSELECT avg(t1.a),
>>> avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM\nplt1 t1, plt2 t2,
>>> plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c,
>>> t2.c, t3.c ORDER BY t1"..., params=0x0, queryEnv=0x0) at explain.c:364
>>>
>>> Thanks & Regards,
>>> Rajkumar Raghuwanshi
>>> QMG, EnterpriseDB.
>>>
>>>
>>>> Regards,
>>>> Amul Sul
>>>>
>>>> On Mon, Feb 4, 2019 at 11:05 AM amul sul <sulamul(at)gmail(dot)com> wrote:
>>>>
>>>>> There are few whitespaces in 0002 patch that I have fixed in the
>>>>> attached version.
>>>>> Rest of the patches are untouched.
>>>>>
>>>>> Ill continue my review and testing.
>>>>>
>>>>> Regards,
>>>>> Amul
>>>>>
>>>>> On Thu, Jan 31, 2019 at 5:26 PM Etsuro Fujita <
>>>>> fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>>>>
>>>>>> (2019/01/22 21:38), Etsuro Fujita wrote:
>>>>>> > Will continue to review.
>>>>>>
>>>>>> I rebased the patch set against the latest HEAD. Attached is a new
>>>>>> version. I'll move this to the next CF, and continue to review it.
>>>>>>
>>>>>> Best regards,
>>>>>> Etsuro Fujita
>>>>>>
>>>>>