Re: Partitioning vs ON CONFLICT

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:

<para>
<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.
</para>

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
ON CONFLICT.

Thanks,
Amit