Re: [HACKERS] MERGE SQL Statement for PG11

On Wed, Jan 31, 2018 at 7:17 AM, Robert Haas <robertmhaas@gmail.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.

> 3. Fall through to the NOT MATCHED clause and try that instead.
> Allows MERGE to work as UPSERT in some simple cases, I think.

I probably wouldn't put it that way myself, FWIW.

> 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.

You have two WHEN MATCHED cases here, which is actually quite a
complicated example. If you didn't, then IIUC there would be no
distinction between #3 and #4.

Whether or not this "pretty weird" behavior would be weirder than
equivalent procedural code consisting of multiple (conditionally
executed) DML statements is subjective. If you imagine that the
equivalent procedural code is comprised of two UPDATE statements and
an INSERT (one statement for every WHEN case), then it's not weird, I
think, or at least is no weirder. If you imagine that it's only one
UPDATE (plus an INSERT), then is does indeed seem weirder.

I'm inclined to think of it as two UPDATE statements (that can only
affect zero or one tuples) rather than one (the statements are inside
a loop that processes many input rows, equivalent to the left side of
MERGE's join). After all, it seems very likely that one of the two
WHEN MATCHED items would actually end up containing a DELETE in real
world queries, and not another UPDATE. That's why I lean towards #4
ever so slightly right now.

> 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.

I'd say the livelock risk is non-zero, but it might still be worth it.
Isn't this like rolling back and repeating the statement in most
real-world cases?

Apparently READ COMMITTED conflict handling in a system that's similar
to Postgres occurs through a statement-level rollback. A little bird
told me that it can repeat again and again, and that there is a little
known mechanism for that to eventually error out after a fixed number
of retries. It might be desirable to emulate that in a rudimentary way
-- by implementing #5. This doesn't seem all that appealing to me
right now, though.

> Maybe there are more options?

Probably.

Minor point on semantics: There is clearly a two phase nature to WHEN
quals, which is the actual structure that Simon chose. Technically,
what you described wouldn't ever require EPQ recheck -- it might
require WHEN recheck. I think we should start being careful about
which we're talking about going forward. Hopefully Simon's MERGE wiki
page can establish a standard lexicon to talk about this stuff without
everyone becoming even more confused. That seems like it would be a
big help.

-- 
Peter Geoghegan