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

On 1/13/14, 5:05 PM, Peter Geoghegan wrote:
> 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).

As long as there's a way to get back what was ultimately inserted or updated that'd work... there might be some cases where you'd actually want to know what the result of the REJECTING command was (ie: did the update do something fancy?).

Actually, you'd also want to know if triggers did anything. So we definitely want to keep the existing RETURNING behavior (sorry, I don't know offhand if you've kept that or not).

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

Yeah, the only other thing you might want is the name of the trigger that returned NULL... that would allow you to do something different based on which trigger it was.

Part of me thinks that'd be useful... part of me thinks it's just a foot-gun...
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net