From: Peter Geoghegan <pg@bowt.ie>
To: Nico Williams <nico@cryptonector.com>
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 15:25:48 -0700
Message-ID: <20171102222548.GA3581@marmot> (raw)
In-Reply-To: <20171102220034.GV4496@localhost>
References: <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>
<20171102220034.GV4496@localhost>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Nico Williams <nico@cryptonector.com> wrote:
>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 ...;
>
This is a bad idea. An implementation like this is not at all
maintainable.
>can handle concurrency via ON CONFLICT DO NOTHING in the INSERT CTE.
That's not handling concurrency -- it's silently ignoring an error. Who
is to say that the conflict that IGNORE ignored is associated with a row
visible to the MVCC snapshot of the statement? IOW, why should the DELETE
affect any row?
There are probably a great many reasons why you need a ModifyTable
executor node that keeps around state, and explicitly indicates that a
MERGE is a MERGE. For example, we'll probably want statement level
triggers to execute in a fixed order, regardless of the MERGE, RLS will
probably require explicitly knowledge of MERGE semantics, and so on.
FWIW, your example doesn't actually have a source (just a target), so it
isn't actually like MERGE.
--
Peter Geoghegan
--
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: pg@bowt.ie, nico@cryptonector.com, simon@2ndquadrant.com, robertmhaas@gmail.com, michael.paquier@gmail.com
Subject: Re: MERGE SQL Statement for PG11
In-Reply-To: <20171102222548.GA3581@marmot>
* 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