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

On Mon, Jan 13, 2014 at 2:20 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> Well, a common case for INSERT RETURNING is to get your set of surrogate
> keys back; so I think users would want the ability to RETURN what finally
> made it into the table.

Your update can also have a RETURNING clause. I'm not necessarily that
attached to fully generalizing RETURNING REJECTS as REJECTING. It was
just an idea. When an insert is rejected and you lock a conflicting
row, it hardly matters what your surrogate key might have been had
that insert succeeded.

To get the surrogate key when it upsert inserts, do a regular
INSERT....RETURNING..., and break the work up into multiple commands.
That will almost always be sufficient, because you'll almost always
know ahead of time where the conflict might be (certainly, the MySQL
feature mandates that you do know).

> Also, if we want to support the case of identifying tuples where a BEFORE
> trigger disallowed the insert, we probably want to expose that that's why
> those tuples were rejected (as opposed to them being rejected due to a
> duplicate key violation).

The ctid *won't* indicate a specific rejecting row then, I guess,
which will do it.

--
Peter Geoghegan