Re: [HACKERS] MERGE SQL Statement for PG11

ISTM that a MERGE isn't really a thing that replaces 2 or 3 other DML
statements, at least in most cases. It's more like a replacement for
procedural code with an outer join, with an INSERT, UPDATE or DELETE
that affects zero or one rows inside the procedural loop that
processes matching/non-matching rows. The equivalent procedural code
could ultimately perform *thousands* of snapshot acquisitions for
thousands of RC DML statements. MERGE is sometimes explained in terms
of "here is the kind of procedural code that you don't have to write
anymore, thanks to MERGE" -- that's what the code looks like.

I attach a rough example of this, that uses plpgsql.

>> Some novel new behavior -- "EPQ with a twist"-- is clearly necessary.
>> I feel a bit uneasy about it because anything that anybody suggests is
>> likely to be at least a bit arbitrary (EPQ itself is kind of
>> arbitrary). We only get to make a decision on how "EPQ with a twist"
>> will work once, and that should be a decision that is made following
>> careful deliberation. Ambiguity is much more likely to kill a patch
>> than a specific technical defect, at least in my experience. Somebody
>> can usually just fix a technical defect.

>
>
> While I agree, I think we need to make these decisions in a time bound
> fashion. If there is too much ambiguity, then it's not a bad idea to settle
> for throwing appropriate errors instead of providing semantically wrong
> answers, even in some remote corner case.

Everything is still on the table, I think.

> Ok. I am now back from holidays and I will too start thinking about this.
> I've also requested a colleague to help us with comparing it against
> Oracle's behaviour. N That's not a gold standard for us, but knowing how
> other major databases handle RC conflicts, is not a bad idea.

The fact that Oracle doesn't allow WHEN MATCHED ... AND quals did seem
like it might be significant to me.