From: | Jeevan Ladhe <jeevan(dot)ladhe(at)enterprisedb(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>, Rahila Syed <rahilasyed90(at)gmail(dot)com>, amul sul <sulamul(at)gmail(dot)com>, Keith Fiske <keith(at)omniti(dot)com>, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>, David Steele <david(at)pgmasters(dot)net>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Adding support for Default partition in partitioning |
Date: | 2017-05-08 18:47:39 |
Message-ID: | CAOgcT0PzaUngbYSXKk2zF9ZSMt_x8qR2tGzUKzsbLyBXiAOsww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Robert,
Thanks for your explnation.
On Mon, May 8, 2017 at 9:56 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, May 4, 2017 at 4:28 PM, Jeevan Ladhe
> <jeevan(dot)ladhe(at)enterprisedb(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Fabien COELHO | 2017-05-08 18:51:36 | Re: [Pkg-postgresql-public] Debian "postgresql-common" config check issue with pg10 |
Previous Message | Peter Eisentraut | 2017-05-08 18:04:54 | Re: Not getting error if ALTER SUBSCRIPTION syntax is wrong. |