Re: [HACKERS] MERGE SQL Statement for PG11

On Tue, Feb 6, 2018 at 9:19 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sun, Feb 4, 2018 at 3:41 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> 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.
>
> I am unable to understand this.
>

Neither do I. There is nothing in above statement which changes 'a'.

> What are you presuming the tuple was
> originally?
>

I have tried to think of one example which can result in what Simon is
saying. Consider original tuple has a = 0 and b = 1

Session -1
WHEN MATCHED AND a=0 THEN UPDATE SET b=0
WHEN MATCHED AND a=1 THEN UPDATE SET b=1

Session-2
WHEN MATCHED AND b=0 THEN UPDATE SET a=0
WHEN MATCHED AND b=1 THEN UPDATE SET a=1

Now assume both the session got the tuple to Update, Session-1 locks
to Update b = 0 and Session-2 will wait for Session-1 to complete.
After Session-1 commits, Session-2 will wake up and performs
EvalPlanQual because it will find the tuple as Updated. Now, I think
EvalPlanQual mechanism will succeed if we don't match WHEN clauses as
part of EvalPlanQual mechanism and it will update a = 1. So, now we
will have a=1, b=0. I think if this is going to happen with
approach-2 (#2), then one can argue that Session-2's update shouldn't
have succeeded.

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