Re: Unique indexes & constraints on partitioned tables

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Unique indexes & constraints on partitioned tables
Date: 2017-12-25 09:49:05
Message-ID: c1651d5b-7bd6-b7e7-e1cc-16ecfe2c0da5@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0003-Teach-executor-to-handle-ON-CONFLICT-key-on-partitio.patch text/plain 6.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ildar Musin 2017-12-25 13:24:38 Re: General purpose hashing func in pgbench
Previous Message Amit Langote 2017-12-25 08:30:39 Re: [HACKERS] Proposal: Local indexes for partitioned table