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

On Thu, May 21, 2015 at 1:15 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> OK, let me summarise. First, thanks for putting time into this feature; we
> all wish to see it work and work well.

You're welcome.

> 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.

Well, multiple unique indexes (that represent essentially the same
business rule) can be inferred at the same time, for edge-cases around
migrations and so on.

> 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).

Okay. No real argument here so far.

> 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.

My difficulty with this (which seems distinct to the concern about not
mandating an inference specification, a concern which seems to only be
about laziness and/or MySQL compatibility) is that I think you'll have
a very hard time finding a case where the update naturally applies to
the path when either constraint is taken, and applies indifferently.
After all, and as I said, why should you not fail when updating the
*other* constrained column in the update? Also, why should you not
have to worry about *both* constraints failing at once (from the
insert)?

I think that if we try and address these cases, we'll end up with
something unusable, complicated, and no better than simply writing two
statements.

> 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.

That's reasonable. I only ask that you describe a plausible use case.
Let's start with that. Try and convince me.

--
Peter Geoghegan