Hi Robert, Thanks for your explnation.
On Mon, May 8, 2017 at 9:56 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Thu, May 4, 2017 at 4:28 PM, Jeevan Ladhe > <jeevan.la...@enterprisedb.com> wrote: > > While reviewing the code I was trying to explore more cases, and I here > > comes an > > open question to my mind: > > should we allow the default partition table to be partitioned further? > > I think yes. In general, you are allowed to partition a partition, > and I can't see any justification for restricting that for default > partitions when we allow it everywhere else. > > > If we allow it(as in the current case) then observe following case, > where I > > have defined a default partitioned which is further partitioned on a > > different > > column. > > > > 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=# INSERT INTO test VALUES (20, 24, 12); > > ERROR: no partition of relation "test_pd" found for row > > DETAIL: Partition key of the failing row contains (b) = (24). > > > > Note, that it does not allow inserting the tuple(20, 24, 12) because > though > > a=20 > > would fall in default partition i.e. test_pd, table test_pd itself is > > further > > partitioned and does not have any partition satisfying b=24. > > Right, that looks like correct behavior. You would have gotten the > same result if you had tried to insert into test_pd directly. > > > Further if I define a default partition for table test_pd, the the tuple > > gets inserted. > > That also sounds correct. > > > Doesn't this sound like the whole purpose of having DEFAULT partition on > > test > > table is defeated? > > Not to me. It's possible to do lots of silly things with partitioned > tables. For example, one case that we talked about before is that you > can define a range partition for, say, VALUES (0) TO (100), and then > subpartition it and give the subpartitions bounds which are outside > the range 0-100. That's obviously silly and will lead to failures > inserting tuples, but we chose not to try to prohibit it because it's > not really broken, just useless. There are lots of similar cases > involving other features. For example, you can apply an inherited > CHECK (false) constraint to a table, which makes it impossible for > that table or any of its children to ever contain any rows; that is > probably a dumb configuration. You can create two unique indexes with > exactly the same definition; unless you're creating a new one with the > intent of dropping the old one, that doesn't make sense. You can > define a trigger that always throws an ERROR and then another trigger > which runs later that modifies the tuple; the second will never be run > because the first one will always kill the transaction before we get > there. Those things are all legal, but often unuseful. Similarly > here. Defining a default list partition and then subpartitioning it > by list is not likely to be a good schema design, but it doesn't mean > we should try to disallow it. It is important to distinguish between > things that are actually *broken* (like a partitioning configuration > where the tuples that can be inserted into a partition manually differ > from the ones that are routed to it automatically) and things that are > merely *lame* (like creating a multi-level partitioning hierarchy when > a single level would have done the job just as well). The former > should be prevented by the code, while the latter is at most a > documentation issue. I agree with you that it is a user perspective on how he decides to do partitions of already partitioned table, and also we should have a demarcation between things to be handled by code and things to be left as common-sense or ability to define a good schema. I am ok with current behavior, provided we have atleast one-lineer in documentation alerting the user that partitioning the default partition will limit the ability of routing the tuples that do not fit in any other partitions. Regards, Jeevan Ladhe
