Re: unique indexes on partitioned tables

Hi Alvaro.

On 2018/01/23 7:55, Alvaro Herrera wrote:
> Alvaro Herrera wrote:
>> Version 4 of this patch, rebased on today's master.

With the latest patch, I noticed what I think is an unintended behavior.

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.

Please find attached a fix, a delta patch which applies on top of your v4
patch.  With it:

create unique index on p (a);
insert into p values (1, 1);
insert into p values (1, 1);
ERROR:  duplicate key value violates unique constraint "p11_a_idx"
DETAIL:  Key (a)=(1) already exists.

insert into p values (2, 1);
insert into p values (2, 1);
ERROR:  duplicate key value violates unique constraint "p2_a_idx"
DETAIL:  Key (a)=(2) already exists.

drop index p_a_idx;
create unique index on p (a, b);
insert into p values (1, 1);
insert into p values (1, 1);
ERROR:  duplicate key value violates unique constraint "p11_a_b_idx"
DETAIL:  Key (a, b)=(1, 1) already exists.

insert into p values (2, 1);
insert into p values (2, 1);
ERROR:  duplicate key value violates unique constraint "p2_a_b_idx"
DETAIL:  Key (a, b)=(2, 1) already exists.

Am I missing something?

Thanks,
Amit