Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition - Mailing list pgsql-bugs

From Andres Freund
Subject Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition
Date
Msg-id 20201114002805.5s26gg3hliywrath@alap3.anarazel.de
Whole thread Raw
In response to Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition  (Andy S <gatekeeper.mail@gmail.com>)
Responses Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition
List pgsql-bugs
Hi,

On 2020-11-13 21:30:39 +0300, Andy S wrote:
> No, it's a bug

At best it is a potentially desirable feature. One that isn't trivial to
implement. We'd have to scan the entire partition tree to ensure there's
a matching constraint on all partitions. And add some more complicated
way to do plan invalidation, because somebody could add a new partition
without a matching constraint - which'd not be detected in a trivial
implementation, because the arbiter determination happens at parse
analysis time.


> Also:
> the certain partition to which data is to be inserted is computed at query
> planning stage the very stage where the planner could also find out if an
> index matching the given specification could be inferred since it's the
> only index that matters.

That is not generally the case. You can have parametrized values that
are inserted, and you can have multi-row inserts. In both cases you
cannot make this decision at plan time.

- Andres



pgsql-bugs by date:

Previous
From: Ernesto Hernández-Novich
Date:
Subject: Re: PG 9.6.20 -- query misbehaves in replica
Next
From: "David G. Johnston"
Date:
Subject: Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition