Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On 25 September 2014 20:38, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Thu, Sep 25, 2014 at 7:12 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> The way forwards, in my view, is to define precisely the behaviour we
>> wish to have. That definition will include the best current mechanism
>> for running an UPSERT using INSERT/UPDATE/loops and comparing that
>> against what is being provided here. We will then have a functional
>> test of equivalence of the approaches, and a basis for making a
>> performance test that shows that performance is increased without any
>> loss of concurrency.
>
> That sounds very reasonable.

So I promise not to discuss locking until we get the first things done.

My suggested approach to get this committed is...

A. UPDATE/INSERT privilege infrastructure.
Add tests to it, make it separately committable, so we can get that done.
Submit to Oct CF; get that done early.

B. Agree command semantics by producing these things
* Explanatory documentation (Ch6.4 Data Manipulation - Upsert)
* SQL Reference Documentation (INSERT)
* Test cases for feature
* Test cases for concurrency
* Test cases for pgbench

All of the above, as a separate committable patch. I hate the fact
that you have written no user facing documentation for this feature.
How can anyone tell whether the tests you've written are correct or
even consistent to a particular definition of correctness?
Submit as patch for review only to Oct 15 CF
We then agree what is required for further work
At this stage, poll the Django and Rails communities for acceptance
and early warning of these features. Listen.

C. Internal weirdness
Submit C based upon earlier agreed B, submit to Dec 15 CF, major patch
deadline, so we can fine tune for last CF.
Then Heikki rewrites half your patch in a better way, you thank him
and then we commit. All done.

> While I'm sure that what I have here can
> decisively beat the xact looping pattern in terms of performance as
> measured by pgbench, the real performance advantage is that this
> approach doesn't burn through XIDs. That was a concern that Andres
> highlighted in relation to using the subxact looping pattern with
> BDR's multi-master replication conflict resolution.

But we're still discussing SQL semantics. So first things first, then
loop back around, hoping our design has not been concurrently
deleted...

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services