Re: [HACKERS] MERGE SQL Statement for PG11 - Mailing list pgsql-hackers

On Tue, Jan 30, 2018 at 2:28 PM, Peter Geoghegan <pg@bowt.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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company