On 21 May 2015 at 15:44, Peter Geoghegan <pg@heroku.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/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
