Re: Unique indexes & constraints on partitioned tables

Hi Alvaro,

On 2017/12/23 6:29, Alvaro Herrera wrote:
> Hello,
> 
> I'm giving this patch its own thread for mental sanity, but this is
> essentially what already posted in [1], plus some doc fixes.  This patch
> depends on the main "local partitioned indexes" in that thread, last
> version of which is at [2].

Thanks for working on this.

Have you considered what happens when ON CONFLICT code tries to depend on
such an index (a partitioned unique index)?  It seems we'll need some new
code in the executor for the same.  I tried the following after applying
your patch:

create table p (a char) partition by list (a);
create table pa partition of p for values in ('a');;
create table pb partition of p for values in ('b');
create unique index on p (a);

insert into p values ('a', 1);
INSERT 0 1

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

insert into p values ('a', 1) on conflict do nothing;
INSERT 0 0

Fine so far... but

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

or

insert into p values ('a', 1) on conflict (a) do update set b = excluded.b;
ERROR:  unexpected failure to find arbiter index

I mentioned this case at [1] and had a WIP patch to address that.  Please
find it attached here.  It is to be applied on top of both of your patches.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/a26f7823-6c7d-3f41-c5fb-7d50dd2f4848%40lab.ntt.co.jp