Re: Insert Documentation - Returning Clause and Order

On Friday, December 11, 2020, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Fri, Dec 11, 2020 at 6:24 AM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>>
>> On Thu, Dec 10, 2020 at 7:49 PM David G. Johnston
>> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>> > Yeah, the ongoing work on parallel inserts would seem to be an issue. We should probably document that though. And maybe as part of parallel inserts patch provide a user-specifiable way to ask for such a guarantee if needed. ‘Insert returning ordered”
>>
>> I am curious about the usecase which needs that guarantee? Don't you
>> have a column on which you can ORDER BY so that it returns the same
>> order as INSERT?
>
>
> This comes up periodically in the context of auto-generated keys being returned - specifically on the JDBC project list (maybe elsewhere...). If one adds 15 VALUES entries to an insert and then sends them in bulk to the server it would be helpful if the generated keys could be matched up one-to-one with the keyless objects in the client. Basically "pipelining" the client and server.

That’s a great use case. It’s not so much about ordering, per se, but
about identity.

Certainly almost every ORM, and maybe even other forms of application
code, need to be able to associate the serial column value returned
with what it inserted. I'd expect something like that (whether by
ordering explicitly or by providing some kind of mapping between
indexes in the statement data and the inserted/returned row values).

James