From: | Peter Geoghegan <pg(at)heroku(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Andres Freund <andres(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE |
Date: | 2013-10-15 17:19:17 |
Message-ID: | CAM3SWZS8CWi6bbkT2M+dMukLfpdV8KAV-Vh8HA7O3vxKpE7axw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Oct 15, 2013 at 9:56 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Well, I don't know that any of us can claim to have a lock on what the
> syntax should look like.
Sure. But it's not just syntax. We're talking about functional
differences too, since you're talking about mandating an update, which
is a not the same as an "update locked row only conditionally", or a
delete.
I get that it's a little verbose, but then this is ORM plumbing for
many of those that would prefer a more succinct syntax. Those people
would also benefit from having their ORM do something much more
powerful for them when needed.
> I think we need to hear some proposals.
Agreed.
> You've heard my gripe about the current syntax (which Andres appears
> to share), but I shan't attempt to prejudice you in favor of my
> preferred alternative, because I don't have one yet.
FWIW, I sincerely see very real advantages to what I've proposed here.
To me, the fact that it's convenient to implement is beside the point.
> There could be
> other ways of avoiding that problem, though. Here's an example:
>
> UPSERT table (keycol1, ..., keycoln) = (keyval1, ..., keyvaln) SET
> (nonkeycol1, ..., nonkeycoln) = (nonkeyval1, ..., nonkeyvaln)
>
> That's pretty ugly on multiple levels, and I'm definitely not
> proposing that exact thing, but the idea is: look for a record that
> matches on the key columns/values; if found, update the non-key
> columns with the corresponding values; if not found, construct a new
> row with both the key and nonkey column sets and insert it. If no
> matching unique index exists we'll have to fail, but we stop short of
> having to mention the name of that index.
What if you want to update the key columns - either the potential
conflict-causing one, or another? What about composite unique
constraints? MySQL certainly supports all that, for example.
--
Peter Geoghegan
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2013-10-15 17:26:11 | Re: Auto-tuning work_mem and maintenance_work_mem |
Previous Message | Josh Berkus | 2013-10-15 17:19:06 | Re: Auto-tuning work_mem and maintenance_work_mem |