From: | Peter Geoghegan <pg(at)heroku(dot)com> |
---|---|
To: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
Cc: | Simon Riggs <simon(at)2ndquadrant(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-09-29 20:49:44 |
Message-ID: | CAM3SWZTe+XZ2+y4bYxF8by3mFb+2Kqyr_m-96FamXFHGQxCu-Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Sep 29, 2014 at 1:40 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> I think that the subset of the MERGE syntax that would be needed
> for UPSERT behavior would be as follows. For one row as literals:
>
> MERGE INTO tab t
> USING (VALUES ('foo', 'p1')) new(id, colB)
> ON (t.id = new.id)
> WHEN MATCHED THEN
> UPDATE SET colB = new.colB
> WHEN NOT MATCHED THEN
> INSERT (id, colB) VALUES (new.id, new.colB);
>
> If you have a bunch of rows in a "bar" table you want to merge in:
>
> MERGE INTO tab t
> USING (SELECT id, colB FROM bar) b
> ON (t.id = b.id)
> WHEN MATCHED THEN
> UPDATE SET colB = b.colB
> WHEN NOT MATCHED THEN
> INSERT (id, colB) VALUES (b.id, b.colB);
>
> I fail to see how this is harder or more problematic than the
> nonstandard suggestions that have been floated. I don't know why
> we would be even *considering* a nonstandard syntax rather than
> saying that only this subset is supported *so far*.
Heikki, Andres and I are against using MERGE for this, fwiw. Tom
seemed to think so too, on previous occasions. It isn't a matter of
alternative syntaxes. I have described in detail why I think it's a
bad idea - I have linked to that about 3 times in this thread. It
paints us into a corner when we go to make this do what MERGE is
supposed to do. Do you want a feature that, when fully generalized,
plays a special visibility game based on whether or not some exact set
of conditions are met? That is a non-starter, IMV.
The whole idea of using an arbitrary join syntax seems great, but I
need something that works backwards from would-be unique violations.
That's the only way to preserve the UPSERT guarantees (atomicity,
definite insert or update).
--
Peter Geoghegan
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2014-09-29 20:53:10 | Re: Yet another abort-early plan disaster on 9.3 |
Previous Message | Andres Freund | 2014-09-29 20:41:45 | Re: open items for 9.4 |