RE: unique indexes on partitioned tables - Mailing list pgsql-hackers

Hi.

I tried this feature with the latest snapshot. When I executed the following SQL statement, multiple primary keys were
createdon the partition. 
 
Is this the intended behavior?

-- test
postgres=> CREATE TABLE part1(c1 INT PRIMARY KEY, c2 INT, c3 VARCHAR(10)) PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 (LIKE part1) ;
CREATE TABLE
postgres=> ALTER TABLE part1v1 ADD CONSTRAINT pk_part1v1 PRIMARY KEY (c1, c2) ;
ALTER TABLE
postgres=> ALTER TABLE part1 ATTACH PARTITION part1v1 FOR VALUES FROM (100) TO (200) ;
ALTER TABLE
postgres=> \d part1v1
                     Table "public.part1v1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | integer               |           | not null |
 c3     | character varying(10) |           |          |
Partition of: part1 FOR VALUES FROM (100) TO (200)
Indexes:
    "part1v1_pkey" PRIMARY KEY, btree (c1)
    "pk_part1v1" PRIMARY KEY, btree (c1, c2)

Regards,

Noriyoshi Shinoda

-----Original Message-----
From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp] 
Sent: Tuesday, February 20, 2018 6:24 PM
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; Jaime Casanova
<jaime.casanova@2ndquadrant.com>
Cc: Jesper Pedersen <jesper.pedersen@redhat.com>; Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: unique indexes on partitioned tables

Hi.

On 2018/02/20 5:45, Alvaro Herrera wrote:
> I pushed this now, with fixes for the last few comments there were.

I noticed with the commit that, while ON CONFLICT (conflict_target) DO UPDATE gives a less surprising error message by
catchingit in the parser, ON CONFLICT (conflict_target) DO NOTHING will go into the executor without the necessary code
tohandle the case.  Example:
 

create table p (a int primary key, b text) partition by list (a); create table p12 partition of p for values in (1, 2);
createtable p3 partition of p (a unique) for values in (3);
 

insert into p values (1, 'a') on conflict (a) do nothing;
ERROR:  unexpected failure to find arbiter index

Attached is a patch to fix that.  Actually, there are two -- one that adjusts the partitioned table tests in
insert_conflict.sqlto have a partitioned unique index and another that fixes the code.
 

I suppose we'd need to apply this temporarily until we fix the ON CONFLICT
(conflict_target) case to be able to use partitioned indexes.

Thanks,
Amit