Re: MERGE SQL Statement for PG11

From: Nico Williams <nico(at)cryptonector(dot)com>
To: Peter Geoghegan <pg(at)bowt(dot)ie>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: MERGE SQL Statement for PG11
Date: 2017-11-02 22:00:35
Message-ID: 20171102220034.GV4496@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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:

updated AS (
UPDATE <target>
SET ...
WHERE <condition>
RETURNING <target>
, inserted AS (
INSERT INTO <target>
WHERE <key> NOT IN (SELECT <key> FROM updated) AND ..
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?


In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2017-11-02 22:25:48 Re: MERGE SQL Statement for PG11
Previous Message Piotr Stefaniak 2017-11-02 21:32:51 Re: SQL/JSON in PostgreSQL