On 2018/01/29 16:28, Amit Langote wrote: > create table p (a int, b int) partition by list (a); > create table p1 partition of p for values in (1) partition by range (b); > create table p11 partition of p1 for values from (1) to (10); > create table p2 partition of p for values in (2); > > create unique index on p (a); > ERROR: insufficient columns in UNIQUE constraint definition > DETAIL: UNIQUE constraint on table "p1" lacks column "b" which is part of > the partition key. > > It seems that after recursing to p1 which is itself partitioned, > DefineIndex() mistakenly looks for column b (which is in the p1's > partition key) in the unique key. I think that's unnecessary. > DefineIndex() should check that only once, that is, before recursing.
Hmm, scratch that... > Am I missing something? Yes, I am. create table p (a int, b int) partition by list (a); create table p1 partition of p for values in (1) partition by range (b); create table p11 partition of p1 for values from (1) to (10); create table p12 partition of p1 for values from (10) to (20); create table p2 partition of p for values in (2); -- after applying my delta patch create unique index on p (a); insert into p values (1, 1); -- unique index p11 (a) says all fine insert into p values (1, 10); -- unique index p12 (a) says all fine That can't be right, because p (a) is no longer unique. So, a unique key on a partitioned table must include the partition key columns of *all* downstream partitioned tables, as your patch correctly enforces. Sorry about the noise. That said, I think that it might be a good idea to include the above detail in the documentation of CREATE INDEX and ALTER TABLE ADD UNIQUE. Thanks, Amit
