On Tue, Oct 15, 2013 at 9:56 AM, Robert Haas <robertmhaas@gmail.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
