Re: [HACKERS] Adding support for Default partition in partitioning - Mailing list pgsql-hackers

On 2017/05/10 2:09, Robert Haas wrote:
> On Tue, May 9, 2017 at 9:26 AM, Rahila Syed <rahilasyed90@gmail.com> wrote:
>>> Hi Rahila,
>>
>>> I am not able add a new partition if default partition is further
>>> partitioned
>>> with default partition.
>>
>>> Consider example below:
>>
>>> postgres=# CREATE TABLE test ( a int, b int, c int) PARTITION BY LIST (a);
>>> CREATE TABLE
>>> postgres=# CREATE TABLE test_p1 PARTITION OF test FOR VALUES IN(4, 5, 6, 7,
>>> 8);
>>> CREATE TABLE
>>> postgres=# CREATE TABLE test_pd PARTITION OF test DEFAULT PARTITION BY
>>> LIST(b);
>>> CREATE TABLE
>>> postgres=# CREATE TABLE test_pd_pd PARTITION OF test_pd DEFAULT;
>>> CREATE TABLE
>>> postgres=# INSERT INTO test VALUES (20, 24, 12);
>>> INSERT 0 1
>>> postgres=# CREATE TABLE test_p2 PARTITION OF test FOR VALUES IN(15);
>> ERROR:  could not open file "base/12335/16420": No such file or directory
>>
>> Regarding fix for this I think we need to prohibit this case. That is
>> prohibit creation
>> of new partition after a default partition which is further partitioned.
>> Currently before adding a new partition after default partition all the rows
>> of default
>> partition are scanned and if a row which matches the new partitions
>> constraint exists
>> the new partition is not added.
>>
>> If we allow this for default partition which is partitioned further, we will
>> have to scan
>> all the partitions of default partition for matching rows which can slow
>> down execution.
>
> I think this case should be allowed

+1

> and I don't think it should
> require scanning all the partitions of the default partition.  This is
> no different than any other case where multiple levels of partitioning
> are used.  First, you route the tuple at the root level; then, you
> route it at the next level; and so on.  It shouldn't matter whether
> the routing at the top level is to that level's default partition or
> not.

It seems that adding a new partition at the same level as the default
partition will require scanning it or its (leaf) partitions if
partitioned.  Consider that p1, pd are partitions of a list-partitioned
table p accepting 1 and everything else, respectively, and pd is further
partitioned.  When adding p2 of p for 2, we need to scan the partitions of
pd to check if there are any (2, ...) rows.

As for fixing the reported issue whereby the partitioned default
partition's non-existent file is being accessed, it would help to take a
look at the code in ATExecAttachPartition() starting at the following:

    /*
     * Set up to have the table be scanned to validate the partition
     * constraint (see partConstraint above).  If it's a partitioned table, we
     * instead schedule its leaf partitions to be scanned.
     */
    if (!skip_validate)
    {

Thanks,
Amit