Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Tue, Oct 15, 2013 at 11:34 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>> Again, other people can have different opinions on this, and that's
>> fine. I'm just giving you mine.
>
> I will defer to the majority opinion here. But you also expressed
> concern about surprising results due to the wrong unique constraint
> violation being the source of a conflict. Couldn't this syntax (with
> the wCTE upsert pattern) help with that, by naming the constant
> inserted in the update too? It would be pretty simple to expose that,
> and far less grotty than naming a unique index in DML.

Well, I don't know that any of us can claim to have a lock on what the
syntax should look like. I think we need to hear some proposals.
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. 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company