From: | Peter Geoghegan <pg(at)heroku(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | Bruce Momjian <bruce(at)momjian(dot)us>, Josh Berkus <josh(at)agliodbs(dot)com>, Kevin Grittner <kgrittn(at)ymail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Craig Ringer <craig(at)2ndquadrant(dot)com> |
Subject: | Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} |
Date: | 2014-10-08 20:16:04 |
Message-ID: | CAM3SWZSJbtwp8gcrwbtG3XZQFkX5U1qPH7WqM8Gr1iooThS0Xw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Oct 8, 2014 at 6:25 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> I change my view on this, after some more thought. (Hope that helps)
Great.
> If we implement MERGE, I can see we may also wish to implement MERGE
> CONCURRENTLY one day. That would be different to UPSERT.
>
> So in the future I think we will need 3 commands
>
> 1. MERGE
> 2. MERGE CONCURRENTLY
> 3. UPSERT
>
> So we no longer need to have the command start with the MERGE keyword.
As I've outlined, I don't see how MERGE CONCURRENTLY could ever work,
but I'm glad that you agree that it should not block this effort (or
indeed, some later effort to implement a MERGE that is comparable to
the implementations of other database systems).
> We will one day have MERGE according to the SQL Standard.
Agreed.
> My opinion is that syntax for this should be similar to MERGE in the
> *body* of the command, rather than some completely different syntax.
> e.g.
>
>> WHEN NOT MATCHED THEN
>> INSERT
>> WHEN MATCHED THEN
>> UPDATE
>
> I'm happy that we put that to a vote on what the syntax should be, as
> long as we bear in mind that we will one day have MERGE as well.
While I am also happy with taking a vote, if we do so I vote against
even this much less MERGE-like syntax. It's verbose, and makes much
less sense when the mechanism is driven by would-be duplicate key
violations rather than an outer join. I also like that when you UPSERT
with the proposed ON CONFLICT UPDATE syntax, you get all the
flexibility of an INSERT - you can use data-modifying CTEs, and nest
the statement in a data-modifying CTE, and "INSERT ... SELECT... ON
CONFLICT UPDATE ..." . And to be honest, it's much simpler to
implement this whole feature as an adjunct to how INSERT statements
are currently processed (during parse analysis, planning and
execution); I don't want to make the syntax work against that. For
example, consider how little I had to change the grammar to make all
of this work:
$ git diff master --stat | grep gram
src/backend/parser/gram.y | 72 ++-
The code footprint of this patch is relatively small, and I think we
can all agree that that's a good thing.
--
Peter Geoghegan
From | Date | Subject | |
---|---|---|---|
Next Message | Petr Jelinek | 2014-10-08 20:16:21 | Re: pg_background (and more parallelism infrastructure patches) |
Previous Message | Robert Haas | 2014-10-08 20:01:53 | Re: Wait free LW_SHARED acquisition - v0.9 |