On Thu, May 21, 2015 at 1:15 PM, Simon Riggs <si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
