Re: unique indexes on partitioned tables

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
Date: 2018-01-29 07:28:54
Message-ID: 92ad77b0-2a6f-a96c-4a3c-67ef6ae65742@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v4-delta.patch text/plain 3.9 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2018-01-29 07:45:15 A Generic Question about Generic type subscripting
Previous Message Ashutosh Bapat 2018-01-29 07:25:49 Re: Query related to alter table ... attach partition