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:59:25 -0500
Message-ID: <20171102225924.GW4496@localhost> (raw)
In-Reply-To: <20171102222548.GA3581@marmot>
References: <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>
<20171102222548.GA3581@marmot>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On Thu, Nov 02, 2017 at 03:25:48PM -0700, Peter Geoghegan wrote:
> Nico Williams <nico@cryptonector.com> wrote:
> >A MERGE mapped to a DML like this:
>
> This is a bad idea. An implementation like this is not at all
> maintainable.
Assuming the DELETE issue can be addressed, why would this not be
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?
Ah, yes, we'd have to make sure the DELETE does not delete rows that
could not be inserted. There's... no way to find out what those would
have been -- RETURNING won't mention them, though it'd be a nice
addition to UPSERT to have a way to do that, and it'd make this mapping
feasible.
> 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.
Wouldn't those fire anyways in a statement like the one I mentioned?
> FWIW, your example doesn't actually have a source (just a target), so it
> isn't actually like MERGE.
That can be added. I was trying to keep it pithy.
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: <20171102225924.GW4496@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