Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

On Tue, May 19, 2015 at 2:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On 19 May 2015 at 17:10, Peter Geoghegan <pg@heroku.com> wrote:
>>
>> On Tue, May 19, 2015 at 1:57 PM, Simon Riggs <simon@2ndquadrant.com>
>> wrote:
>> > We should allow DO UPDATE to exclude a constraint and apply a
>> > deterministic
>> > order to the constraints. 1. PK if it exists. 2. Replica Identity, when
>> > not
>> > PK, 3. UNIQUE constraints in name order, like triggers, so users can
>> > define
>> > a default evaluation order, just like they do with triggers.
>>
>> That seems like something way worse than just allowing it for all
>> constraints.
>
>
> I'm talking about the evaluation order; it would still match all
> constraints, otherwise they wouldn't be constraints.

But it doesn't match all constraints when a would-be conflict is
detected.

With Postgres, we want to make sure that the user has
put thought into the condition they take that update path on, and so
it is mandatory (it can infer multiple unique indexes, but only when
they're basically equivalent for this purpose).

I actually do not feel strongly that it would be terrible to allow the
user to omit an inference clause for the DO UPDATE variant (on the
grounds of that being closer to MySQL). After all, we don't mandate
that the user specifies an explicit targetlist for INSERT, and that
seems like a footgun to me. If you want to make the case for doing
things that way, I probably will not oppose it. FWIW, I don't think
it's unreasonable to have a little discussion on fine points of
semantics like that post feature-freeze.