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

On 21 May 2015 at 15:44, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> > Please look at the $SUBJECT of this thread. We're here now.
>
> What do you want me to do about it? I've said that I think that what
> you say about not mandating the inference clause in the parser could
> be okay. If you want to change it, obviously you're going to need to
> get some buy in, and this thread could easily be missed. I'm not
> willing to defend mandating it, and I'm not willing to argue for
> removing it (to be clear, I think being able to infer a unique index
> is very important, but that doesn't mean that I'm attached to
> mandating it for UPDATE). That's all.
>

OK, let me summarise. First, thanks for putting time into this feature; we
all wish to see it work and work well.

The current ON CONFLICT syntax requires us to specify one-and-only-one
conflict_target/conflict_action pair. I would like to be able to specify 0,
1 or more conflict_targets, as the developer desires.

It is very desirable to be able to specify DO UPDATE without any
conflict_target, relying instead on our ability to infer a conflict_target
deterministically. That is the way other systems work and we should be
aiming to provide similar ease of use. Having said that, we all recognize
that MySQL is broken for multiple constraints and we have done well to come
up with a design that allows us to specify finer grained control when we
have multiple constraints. (Ideally, we would use the identical syntax to
MySQL, but that is secondary to simply avoiding specifying a
conflict_target).

If we do have multiple constraints then we should be allowed to specify
multiple conflict_target/conflict_action pairs (or similar), since few
people believe that one conflict_action would cover the various
permutations that occur with multiple potential constraint failures.

In summary, the current design seeks to overcome the problems of having
multiple constraints, but doesn't yet do so in a flexible (0) or complete
(>1) way.

As the patch author I hope and expect that you will listen to this and
consider how you will resolve these problems, just as any of us has done
when they are the patch author, even after commit. I would like to see this
happen now before we get hit with usage questions similar to OP's. If both
requests cannot happen now, if we can at least agree a path for future
enhancement we can refer people to what will happen in later releases when
they ask.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services