Re: Adding support for Default partition in partitioning

On Tue, May 9, 2017 at 9:26 AM, Rahila Syed <rahilasyed90(at)gmail(dot)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 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company