Re: MERGE SQL Statement for PG11

On Thu, Nov 02, 2017 at 02:05:19PM -0700, Peter Geoghegan wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >So in your view we should make no attempt to avoid concurrent errors,
> >even when we have the capability to do so (in some cases) and doing so
> >would be perfectly compliant with the SQLStandard.
>
> Yes. That's what I believe. I believe this because I can't see a way to
> do this that isn't a mess, and because ON CONFLICT DO UPDATE exists and
> works well for the cases where we can do better in READ COMMITTED mode.

A MERGE mapped to a DML like this:

WITH
updated AS (
UPDATE <target>
SET ...
WHERE <condition>
RETURNING <target>
)
, inserted AS (
INSERT INTO <target>
SELECT ...
WHERE <key> NOT IN (SELECT <key> FROM updated) AND ..
ON CONFLICT DO NOTHING -- see below!
RETURNING <target>
)
DELETE FROM <target>
WHERE <key> NOT IN (SELECT <key> FROM updated) AND
<key> NOT IN (SELECT <key> FROM inserted) AND ...;

can handle concurrency via ON CONFLICT DO NOTHING in the INSERT CTE.

Now, one could write a MERGE that produces conflicts even without
concurrency, so adding ON CONFLICT DO NOTHING by default as above...
seems not-quite-correct. But presumably one wouldn't write MERGE
statements that produce conflicts in the absence of concurrency, so this
seems close enough to me.

Another thing is that MERGE itself could get an ON CONFLICT clause for
the INSERT portion of the MERGE, allowing one to ignore some conflicts
and not others, though there would be no need for DO UPDATE, only DO
NOTHING for conflict resolution :) This seems better.

I do believe this mapping is correct, and could be implemented entirely
in src/backend/parser/gram.y! Am I wrong about this?

Nico
--