Date: Thu, 2 Nov 2017 15:25:48 -0700
From: Peter Geoghegan <pg@bowt.ie>
To: Nico Williams <nico@cryptonector.com>
Cc: Simon Riggs <simon@2ndquadrant.com>,
	Robert Haas <robertmhaas@gmail.com>,
	Michael Paquier <michael.paquier@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: MERGE SQL Statement for PG11
Message-ID: <20171102222548.GA3581@marmot>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: <20171102220034.GV4496@localhost>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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