Re: Partitioning vs ON CONFLICT

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Peter Geoghegan <pg(at)bowt(dot)ie>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Thom Brown <thom(at)linux(dot)com>
Subject: Re: Partitioning vs ON CONFLICT
Date: 2017-02-17 06:47:29
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2017/02/17 14:50, Peter Geoghegan wrote:
> On Thu, Feb 16, 2017 at 9:27 PM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> Attached patch fixes that. Thom, your example query should not error out
>> with the patch. As discussed here, DO UPDATE cannot be supported at the
>> moment.
> Maybe you should just let infer_arbiter_indexes() fail, rather than
> enforcing this directly. IIRC, that's what happens with
> inheritance-based partitioning.

That would be another way. The error message emitted by
infer_arbiter_indexes() would be:

ERROR: there is no unique or exclusion constraint matching the ON
CONFLICT specification

It does read better than what proposed patch makes
transformOnConflictClause() emit:

ERROR: ON CONFLICT ON UPDATE clause is not supported with partitioned tables

I updated the patch. Now it's reduced to simply removing the check in
transformInsertStmt() that prevented using *any* ON CONFLICT on
partitioned tables at all.

I don't however see why the error would *necessarily* occur in the case of
inheritance partitioning. I mean if inserts into the root table in an
inheritance hierarchy, it's still possible to ON CONFLICT DO UPDATE using
the unique index only on that table for inference, although that's what a
user would intend to do.

create table foo (a int, b int, unique (a));
create table foo_part (like foo including indexes) inherits (foo);
insert into foo values (1, 2);

-- the following still works

insert into foo values (1, 2)
on conflict (a) do update set b = excluded.b where excluded.a = 1;
insert into foo values (1, 2)
on conflict (a) do update set b = excluded.b where excluded.a = 1;

As the documentation about inheritance partitioning notes, that may not be
the behavior expected for partitioned tables:

<command>INSERT</command> statements with <literal>ON CONFLICT</>
clauses are unlikely to work as expected, as the <literal>ON CONFLICT</>
action is only taken in case of unique violations on the specified
target relation, not its child relations.

With partitioned tables, since it's not possible to create index
constraints on them, ON CONFLICT DO UPDATE simply won't work. So the
patch also updates the note in the document about partitioned tables and


Attachment Content-Type Size
0001-ON-CONFLICT-DO-NOTHING-should-work-with-partitioned-2.patch text/x-diff 4.7 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Kouhei Kaigai 2017-02-17 07:16:47 Re: ParallelFinish-hook of FDW/CSP (Re: Steps inside ExecEndGather)
Previous Message Thomas Munro 2017-02-17 06:16:50 pg_recvlogical.c doesn't build with --disable-integer-datetimes