Re: Adding support for Default partition in partitioning

>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.

This is a better explanation. May be following sentence was confusing,
"That is prohibit creation of new partition after a default partition which
is further partitioned"
Here, what I meant was default partition is partitioned further.

>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:
OK. I get it now. If attach partition already supports scanning all the
partitions before attach,
similar support should be provided in the case of adding a partition after
default partition as well.

Thank you,
Rahila Syed

On Wed, May 10, 2017 at 6:42 AM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp
> wrote:

> On 2017/05/10 2:09, Robert Haas wrote:
> > 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
>
> +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
>
>