From: Nico Williams <nico@cryptonector.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: MERGE SQL Statement for PG11
Date: Thu, 2 Nov 2017 17:00:35 -0500
Message-ID: <20171102220034.GV4496@localhost> (raw)
In-Reply-To: <20171102210519.GA31574@marmot>
References: <CANP8+j+Wij0NoewRaNA=U6pfz3CxV9AXdBrkvRmGcq+ZiBQxpQ@mail.gmail.com>
<CAH2-Wz=4Nha_cMdwTVcZwD9Gt1uzXEfpKOv72vBhCAJNEGQ0kw@mail.gmail.com>
<CANP8+jJBpB7e+sHs=gPaqL-E2Prtmv7fnLN9D34BO8_mJuy_KQ@mail.gmail.com>
<CAH2-Wzk+4013Ht1d9uNef2YBPdkTyyhdH=CDQ527K4iCfatu+Q@mail.gmail.com>
<CANP8+jKNXUjW4FT=1me5FzTpvMhwWkK4-Ea3F1_Vw9vE0rkt0A@mail.gmail.com>
<20171101182032.GA7104@marmot>
<CANP8+jJtsL3fYgiRWYB5vM9g98iFNHgBeCtPufUkgVShpEFvvg@mail.gmail.com>
<20171102191636.GA27644@marmot>
<CANP8+jKREJsXEGUvBTJTZk8WsAXyNjVEmXRwv0Z2MYtcbaQ8Ng@mail.gmail.com>
<20171102210519.GA31574@marmot>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On Thu, Nov 02, 2017 at 02:05:19PM -0700, Peter Geoghegan wrote:
> Simon Riggs <simon@2ndquadrant.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
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nico@cryptonector.com, pg@bowt.ie, simon@2ndquadrant.com, robertmhaas@gmail.com, michael.paquier@gmail.com
Subject: Re: MERGE SQL Statement for PG11
In-Reply-To: <20171102220034.GV4496@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox