|From:||Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>|
|To:||Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||Re: unique indexes on partitioned tables|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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.
|Next Message||Antonin Houska||2018-01-29 08:32:02||Re: [HACKERS] WIP: Aggregation push-down|
|Previous Message||Masahiko Sawada||2018-01-29 08:18:05||Re: Regarding ambulkdelete, amvacuumcleanup index methods|