Re: Partitioning vs ON CONFLICT

On 2017/02/17 13:25, Peter Geoghegan wrote:
> On Thu, Feb 16, 2017 at 8:21 PM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> would be working on a leaf partition chosen by tuple-routing after an
>> insert on a partitioned table. The leaf partitions can very well have a
>> unique index, which can be used for inference. The problem however is
>> that infer_arbiter_indexes() in the optimizer would be looking at the root
>> partitioned, which cannot yet have any indexes defined on them, let alone
>> unique indexes. When we develop a feature where defining an index on the
>> root partitioned table would create the same index on all the leaf
>> partitions and then extend it to support unique indexes, then we can
>> perhaps talk about supporting ON CONFLICT handing. Does that make sense?
>
> Yes, that makes sense, but I wasn't arguing that that should be
> possible today. I was arguing that when you don't spell out an
> arbiter, which ON CONFLICT DO NOTHING permits, then it should be
> possible for it to just work today -- infer_arbiter_indexes() will
> return immediately.

I see. It now seems that I should have realized the DO NOTHING action is
indeed supportable when I initially wrote the code that causes the current
error.

> This should be just like the old approach involving inheritance, in
> that that should be possible. No?

So we should error out only when the DO UPDATE conflict action is
requested. Because it will require specifying conflict_target, which it's
not possible to do in case of partitioned tables.

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.

Thanks,
Amit