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>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Jaime Casanova <jaime(dot)casanova(at)2ndquadrant(dot)com>
Cc: Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: unique indexes on partitioned tables
Date: 2018-02-20 09:24:24
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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 catching it in the parser,
ON CONFLICT (conflict_target) DO NOTHING will go into the executor without
the necessary code to handle 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);
create table 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.sql to 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.


Attachment Content-Type Size
v1-0001-Adjust-partitioned-table-tests-in-insert_conflict.patch text/plain 4.3 KB
v1-0002-Fix-ON-CONFLICT-DO-NOTHING-with-partitioned-index.patch text/plain 3.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Aleksander Alekseev 2018-02-20 09:52:53 Re: CURRENT OF causes an error when IndexOnlyScan is used
Previous Message Ashutosh Bapat 2018-02-20 09:13:02 Re: Expression errors with "FOR UPDATE" and postgres_fdw with partition wise join enabled.