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

On Tue, May 19, 2015 at 1:57 PM, Simon Riggs <simon(at)2ndquadrant(dot)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 have a hard time imagining why you'd ever not want to be explicit
>>> about what to take the alternative path on for the DO UPDATE variant.
>>>
>>> What do you have in mind?
>>
>>
>> If I'm being honest, my main driver is laziness :) I don't mind specifying
>> the constraint if I can understand why it's required, but otherwise it just
>> seems like I need to do more typing for no reason. Especially when there's
>> only one unique constraint on a table.
>
>
> 1) Ease of use - Unique constraints don't change very often. This saves time
> for the common case where they stay the same. It also saves time if they do
> change, because you avoid having to completely recode your app AND make that
> happen at exactly the same time you apply the change of unique constraint.

I don't see how it's possible to change unique constraints in a way
that breaks the inference specification without that actually being
desirable -- naming the constraint by name is an escape hatch that is
generally discouraged. That's the whole point of inference. I put an
awful lot of work into making unique index inference as forgiving as
possible. For example, it doesn't care what order attributes appear
in, or if they appear redundantly, or if an ON CONFLICT unique index
predicate is more selective than any available index that is otherwise
satisfied (there is a call to predicate_implied_by()).

> 2) Compatibility with MySQL

But what you describe isn't compatible with MySQL. It's totally novel.

--
Peter Geoghegan