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

On Tue, Oct 15, 2013 at 8:11 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I'm not saying "go implement MERGE". I'm saying, make the
> insert-or-update operation a single statement, using some syntax TBD,
> instead of requiring the use of a new insert statement that makes
> invisible rows visible as a side effect, so that you can wrap that in
> a CTE and feed it to an update statement. That's complex and, AFAICS,
> unlike how any other database product handles this.

Well, lots of other databases have their own unique way of doing this
- apart from MySQL's INSERT...ON DUPLICATE KEY UPDATE, there is a
variant within Teradata, Sybase and SQLite. They're all different. And
in the case of Teradata, it was an interim feature towards MERGE which
came in a much later release, which is how I see this.

No other database system even has writeable CTEs, of course. It's a
fairly recent idea.

> 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.

--
Peter Geoghegan