Re: Partition-wise join for join between (declaratively) partitioned tables

On Mon, Apr 24, 2017 at 5:02 PM, Rajkumar Raghuwanshi
<rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
> On Fri, Apr 21, 2017 at 7:59 PM, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>>
>> Here's an updated patch set
>
>
> Hi,
>
> I have applied v18 patches and got a crash in m-way joins when partition
> ranges differ, below are steps to reproduce this.
>
> CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
> CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250);
> CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600);
> CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500);
> INSERT INTO prt1 SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0,
> 599, 2) i;
> ANALYZE prt1;
>
> CREATE TABLE prt4_n (a int, b int, c text) PARTITION BY RANGE(a);
> CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (300);
> CREATE TABLE prt4_n_p2 PARTITION OF prt4_n FOR VALUES FROM (300) TO (500);
> CREATE TABLE prt4_n_p3 PARTITION OF prt4_n FOR VALUES FROM (500) TO (600);
> INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0,
> 599, 2) i;
> ANALYZE prt4_n;
>
> SET enable_partition_wise_join = on ;
> EXPLAIN (COSTS OFF)
> SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2, prt1 t3 WHERE t1.a =
> t2.a AND t2.a = t3.a;
> 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.
> !>

Thanks Rajkumar for the report. When two relations with same partition
scheme but different partition bounds are joined, their join relation
has partition scheme set, but not partition bounds since we do not
have logic to merge such partition bounds. When this join relation is
joined further with other relation with same partition scheme, the
code assumed that the join relation had partition bounds set. So the
corresponding assertion failed. Instead, we should treat this
condition same as the case of joining relations with different
partition bounds and not use partition wise join for this join. This
case may be further improved in the next set of patches by trying to
merge partition bounds so that partition-wise join can be applied.
Here's set of patches which fixes the issue.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company