Re: [HACKERS] MERGE SQL Statement for PG11

On 31 January 2018 at 15:17, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Jan 30, 2018 at 2:28 PM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>> What's at issue here specifically is the exact behavior of
>> EvalPlanQual() in the context of having *multiple* sets of WHEN quals
>> that need to be evaluated one at a time (in addition to conventional
>> EPQ join quals). This is a specific, narrow question about the exact
>> steps that are taken by EPQ when we have to switch between WHEN
>> MATCHED and WHEN NOT MATCHED cases *as we walk the UPDATE chain*.
>>
>> Right now, I suspect that we will require some minor variation of
>> EPQ's logic to account for new risks. The really interesting question
>> is what happens when we walk the UPDATE chain, while reevaluating EPQ
>> quals alongside WHEN quals, and then determine that no UPDATE/DELETE
>> should happen for the first WHEN case -- what then? I suspect that we
>> may not want to start from scratch (from the MVCC-visible tuple) as we
>> reach the second or subsequent WHEN case, but that's a very tentative
>> view, and I definitely want to hear more opinions it. (Simon wants to
>> just throw a serialization error here instead, even in READ COMMITTED
>> mode, which I see as a cop-out.)
>
> 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.
>
> 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.
>
> 3. Fall through to the NOT MATCHED clause and try that instead.
> Allows MERGE to work as UPSERT in some simple cases, I think.
>
> 4. Continue walking the chain of WHEN MATCHED items in order and test
> them against the new tuple. This is actually pretty weird because a
> 0->1 update will fall through to the second UPDATE rule, but a 1->0
> update will fall through to the NOT MATCHED clause.
>
> 5. Retry from the top of the chain with the updated tuple. Could
> theoretically livelock - not sure how much of a risk that is in
> practice.
>
> Maybe there are more options?
>
> My initial reaction is to wonder what's wrong with #2.

No, we don't throw an ERROR in that case, because it is a simple
variation of existing EvalPlanQual behavior.

#2 is possible, yes, and is how we had it coded in v11.

#4 was how I first assumed it had to work, but it gives the wrong
answer in some cases and right answer in others, depending upon order
of WHEN clauses. That was ruled out as inconsistent.

#5 is what the patch does now. There are tests covering that behavior
in specs/merge-match-recheck.spec

There are more complex cases to consider.

If a concurrent DELETE hits, then we can try #3, i.e. changing MATCHED
to NOT MATCHED. That currently throws an error, as requested. It looks
to be possible, but it would require some variation of EvalPlanQual.
My prototype of that doesn't yet work, so I can't yet confirm whether
it is even possible. If it is, I will submit as an option for PG11.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services