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

On Sat, Dec 2, 2017 at 2:13 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Dec 1, 2017 at 1:36 AM, Rajkumar Raghuwanshi
> <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
>> On Tue, Oct 31, 2017 at 2:45 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>>> OK, committed. This is a good example of how having good code
>>> coverage doesn't necessarily mean you've found all the bugs. :-)
>>>
>> As of now partition_join.sql is not having test cases covering cases
>> where partition table have default partition, attaching a small test
>> case patch to cover those.
>
> That's not that small, and to me it looks like overkill.
>

I agree, the patch looks longer than expected. I think, it's important
to have some testcases to test partition-wise join with default
partitions. I think we need at least one test for range default
partitions, one test for list partitioning, one for multi-level
partitioning and one negative testcase with default partition missing
from one side of join.

May be we could reduce the number of SQL commands and queries in the
patch by adding default partition to every table that participates in
partition-wise join (leave the tables participating in negative tests
aside.). But that's going to increase the size of EXPLAIN outputs and
query results. The negative test may simply drop the default partition
from one of the tables.

For every table being tested, the patch adds two ALTER TABLE commands,
one for detaching an existing partition and then attach the same as
default partition. Alternative to that is just add a new default
partition without detaching and existing partition. But then the
default partition needs to populated with some data, which requires 1
INSERT statement at least. That doesn't reduce the size of patch, but
increases the output of query and EXPLAIN plan.

May be in case of multi-level partitioning test, we don't need to add
DEFAULT in every partitioned relation; adding to one of them would be
enough. May be add it to the parent, but that too can be avoided. That
would reduce the size of patch a bit.

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