>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...@lab.ntt.co.jp > wrote: > On 2017/05/10 2:09, Robert Haas wrote: > > On Tue, May 9, 2017 at 9:26 AM, Rahila Syed <rahilasye...@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 > >