On 4 February 2018 at 06:32, Amit Kapila <amit.kapil...@gmail.com> wrote: > On Wed, Jan 31, 2018 at 11:37 PM, Peter Geoghegan <p...@bowt.ie> wrote: >> On Wed, Jan 31, 2018 at 7:17 AM, Robert Haas <robertmh...@gmail.com> wrote: >>> I don't fully grok merge but suppose you have: >>> >>> WHEN MATCHED AND a = 0 THEN UPDATE ... >>> WHEN MATCHED AND a = 1 THEN UPDATE ... >>> WHEN NOT MATCHED THEN INSERT ... >>> >>> Suppose you match a tuple with a = 0 but, upon trying to update it, >>> find that it's been updated to a = 1. It seems like there are a few >>> possible behaviors: >>> >>> 1. Throw an error! I guess this is what the patch does now. >> >> Right. >> >>> 2. Do absolutely nothing. I think this is what would happen with an >>> ordinary UPDATE; the tuple fails the EPQ recheck and so is not >>> updated, but that doesn't trigger anything else. >> >> I think #2 is fine if you're talking about join quals. Which, of >> course, you're not. These WHEN quals really do feel like >> tuple-at-a-time procedural code, more than set-orientated quals (if >> that wasn't true, we'd have to allow cardinality violations, which we >> at least try to avoid). Simon said something like "the SQL standard >> requires that WHEN quals be evaluated first" at one point, which makes >> sense to me. >> > > It is not clear to me what is exactly your concern if we try to follow > #2? To me, #2 seems like a natural choice.
At first, but it gives an anomaly so is not a good choice. The patch does behavior #5, it rechecks the conditions with the latest row. Otherwise WHEN MATCHED AND a=0 THEN UPDATE SET b=0 WHEN MATCHED AND a=1 THEN UPDATE SET b=1 would result in (a=1, b=0) in case of concurrent updates, which the user clearly doesn't want. The evaluation of the WHEN qual must occur prior to the update, which will still be true in #5. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
