On Thu, Mar 1, 2018 at 4:33 AM, Pavan Deolasee <pavan.deola...@gmail.com> wrote: > What if the updated tuple fails the join qual with respect to the current > tuple from the source relation but it now matches some other tuple from the > source relation? I described this case in one of the earlier emails too. In > this case, we might end up doing an INSERT (if we decide to execute WHEN NOT > MATCHED action), even though a MATCH exists. If there is no WHEN NOT MATCHED > action, the current patch will just skip the updated tuple even though a > match exists, albeit it's not the current source tuple.
If it does happen, then that will typically be because someone else concurrently updated a row, changing the primary key attributes, or some unique index attributes that our MERGE joins on, which I think is pretty rare. I'm assuming that the user does an idiomatic MERGE, like every example I can find shows, where the join quals on the target table are simple equality predicates on the primary key attribute(s). I think it's fine to simply let the insertion fail with a duplicate error. Is this any different to a concurrent INSERT that produces that same outcome? If the MERGE isn't idiomatic in the way I describe, then the INSERT may actually succeed, which also seems fine. > Oracle behaves differently and it actually finds a new matching tuple from > the source relation and executes the WHEN MATCHED action, using that source > tuple. But I am seriously doubtful that we want to go down that path and > whether it's even feasible. I think that that's just a consequence of Oracle using statement level rollback to do RC conflict handling. It's the same with an UPDATE ... FROM, or any other type of UPDATE. > Our regular UPDATE .. FROM does not do that > either. Given that, it seems better to just throw an error (even when no NOT > MATCHED action exists) and explain to the users that MERGE will work as long > as concurrent updates don't modify the columns used in the join condition. > Concurrent deletes should be fine and we may actually even invoke WHEN NOT > MATCHED action in that case. Again, I have to ask: is such an UPDATE actually meaningfully different from a concurrent DELETE + INSERT? If so, why is a special error better than a dup violation, or maybe even having the INSERT (and whole MERGE statement) succeed? -- Peter Geoghegan
