Date: Thu, 2 Nov 2017 15:32:24 -0500
From: Nico Williams <nico@cryptonector.com>
To: Peter Geoghegan <pg@bowt.ie>
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: <20171102203223.GT4496@localhost>
References: 
 <CAH2-Wzm0A0PUiAeMsmj6P+P4jaP+yz3dXH5f=xPWnuyX5m8ebQ@mail.gmail.com>
 <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>
 <20171102191612.GR4496@localhost>
 <20171102195145.GB27644@marmot>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20171102195145.GB27644@marmot>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On Thu, Nov 02, 2017 at 12:51:45PM -0700, Peter Geoghegan wrote:
> Nico Williams <nico@cryptonector.com> wrote:
> >If you want to ignore conflicts arising from concurrency you could
> >always add an ON CONFLICT DO NOTHING to the INSERT DML in the mapping I
> >proposed earlier.  Thus a MERGE CONCURRENTLY could just do that.
> >
> >Is there any reason not to map MERGE as I proposed?
> 
> Performance, for one. MERGE generally has a join that can be optimized
> like an UPDATE FROM join.

Ah, right, I think my mapping was pessimal.  How about this mapping
instead then:

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 ..
      /*
       * Add ON CONFLICT DO NOTHING here to avoid conflicts in the face
       * of concurrency.
       */
      RETURNING <target>
  )
DELETE FROM <target>
WHERE <key> NOT IN (SELECT <key> FROM updated) AND
      <key> NOT IN (SELECT <key> FROM inserted) AND ...;

?

If a MERGE has no delete clause, then the mapping would be:

WITH
    updated AS (
      UPDATE <target>
      SET ...
      WHERE <condition>
      RETURNING <target>
  )
INSERT INTO <target>
SELECT ...
WHERE <key> NOT IN (SELECT <key> FROM updated) AND ..
/*
 * Add ON CONFLICT DO NOTHING here to avoid conflicts in the face
 * of concurrency.
 */
;

> I haven't studied this question in any detail, but FWIW I think that
> using CTEs for merging is morally equivalent to a traditional MERGE
> implementation. [...]

I agree.  So why not do that initially?  Optimize later.

Such a MERGE mapping could be implemented entirely within
src/backend/parser/gram.y ...

Talk about cheap to implement, review, and maintain!

Also, this would be notionally very simple.

Any optimizations to CTE query/DML execution would be generic and
applicable to MERGE and other things besides.  If mapping MERGE to
CTE-using DMLs motivates such optimizations, all the better.

>          [...]. It may actually be possible to map from CTEs to a MERGE
> statement, but I don't think that that's a good approach to implementing
> MERGE.

Surely not every DML with CTEs can map to MERGE.  Maybe I misunderstood
your comment?

> Most of the implementation time will probably be spent doing things like
> making sure MERGE behaves appropriately with triggers, RLS, updatable
> views, and so on. That will take quite a while, but isn't particularly
> technically challenging IMV.

Note that mapping to a DML with CTEs as above gets triggers, RLS, and
updateable views right from the get-go, because DMLs with CTEs, and DMLs
as CTEs, surely do as well.

Nico
-- 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers