Re: [HACKERS] MERGE SQL Statement for PG11

On 4 February 2018 at 06:32, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Wed, Jan 31, 2018 at 11:37 PM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>> On Wed, Jan 31, 2018 at 7:17 AM, Robert Haas <robertmhaas(at)gmail(dot)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