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

On 1/12/14, 9:35 PM, Andreas Karlsson wrote:
> On 01/12/2014 11:20 PM, Peter Geoghegan wrote:
>> On Sun, Jan 12, 2014 at 8:12 AM, Andreas Karlsson <andreas(at)proxel(dot)se> wrote:
>>> On 01/11/2014 11:42 PM, Peter Geoghegan wrote:
>>>> I recently suggested that rather than RETURNING REJECTS, we could have
>>>> a REJECTING clause, which would see a DML statement project strictly
>>>> the complement of what RETURNING projects in the same context. So
>>>> perhaps you could also see what RETURNING would not have projected
>>>> because a before row trigger returned NULL (i.e. when a before trigger
>>>> indicates to not proceed with insertion). That is certainly more
>>>> general, and so is perhaps preferable. It's also less verbose, and it
>>>> seems less likely to matter that we'll need to make REJECTING a fully
>>>> reserved keyword, as compared to REJECTS. (RETURNING is already a
>>>> fully reserved keyword not described by the standard, so this makes a
>>>> certain amount of sense to me). If nothing else, REJECTING is more
>>>> terse than RETURNING REJECTS.
>>>
>>> I do not entirely understand what you are proposing here. Any example how
>>> this would look compared to your RETURNING REJECTS proposal?
>>
>> It's very similar - REJECTING is a total generalization of what I
>> already have. The difference is only that REJECTING is accepted in all
>> contexts that RETURNING is, and not just with INSERT...ON DUPLICATE
>> KEY LOCK FOR UPDATE. So you could potentially have REJECTING project
>> the slot proposed for insertion on an UPDATE where RETURNING would
>> not. If for example a BEFORE ROW trigger fired, and returned NULL,
>> perhaps it'd then be possible to project the slot as it was before
>> being passed to the trigger. Perhaps there is no real demand for that,
>> but, as I said, from a usability perspective it may be easier to
>> reason about a feature that projects strictly the complement of what
>> RETURNING would project in the same context.
>
> So simply this?
>
> WITH rej AS (
> INSERT INTO foo (a, b, c)
> VALUES (44, 1078, 'insert'), (55, 1088, 'insert')
> REJECTING a)
> UPDATE foo SET c = 'update' FROM rej WHERE foo.a = rej.a;
>
> Another question: have you given any thought on the case where you want to use both the successfully inserted tuples and the rejected and use in the CTE? Is that even something anyone would want? Would perhaps MERGE be more suited for that?

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.

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).
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net