Date: Thu, 2 Nov 2017 14:05:19 -0700
From: Peter Geoghegan <pg@bowt.ie>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: 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: <20171102210519.GA31574@marmot>
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>
 <20171102191636.GA27644@marmot>
 <CANP8+jKREJsXEGUvBTJTZk8WsAXyNjVEmXRwv0Z2MYtcbaQ8Ng@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <CANP8+jKREJsXEGUvBTJTZk8WsAXyNjVEmXRwv0Z2MYtcbaQ8Ng@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

Simon Riggs <simon@2ndquadrant.com> wrote:
>I think people imagined you had worked out how to make MERGE run
>concurrently, I certainly did, but in fact you're just saying you
>don't believe it ever should.

I'm certain that they didn't think that at all. But I'll let them speak
for themselves.

>That is strange since the SQL Standard specifically allows the
>implementation to decide upon concurrent behaviour.

And yet nobody else decided to do what you propose with this apparent
leeway.  (See the UPSERT wiki page for many references that confirm
this.)

>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.

Did you know that Oracle doesn't have an EPQ style mechanism at all?
Instead, it rolls back the entire statement and retries it from scratch.
That is not really any further from or closer to the standard than the
EPQ stuff, because the standard doesn't say anything about what should
happen as far as READ COMMITTED conflict handling goes.

My point here is that all of the stuff I'm talking about is only
relevant in READ COMMITTED mode, in areas where the standard never
provides us with guidance. If you can rely on SSI, then there is no
difference between what you propose and what I propose anyway, except
that what I propose is more general and will have better performance,
especially for batch MERGEs. If READ COMMITTED didn't exist,
implementing ON CONFLICT would have been more or less free of
controversy.

>Yes, that certainly will make an easier patch for MERGE.

Indeed, it will.

--
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