Re: [HACKERS] MERGE SQL Statement for PG11

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.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com