Thread: Insert Documentation - Returning Clause and Order

Insert Documentation - Returning Clause and Order

From
"David G. Johnston"
Date:
Hey,

Would it be accurate to add the following sentence to the INSERT documentation under "Outputs"?

"...inserted or updated by the command."  For a multiple-values insertion, the order of output rows will match the order that rows are presented in the values or query clause.


David J.

Re: Insert Documentation - Returning Clause and Order

From
Ashutosh Bapat
Date:
On Wed, Dec 9, 2020 at 9:10 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> Hey,
>
> Would it be accurate to add the following sentence to the INSERT documentation under "Outputs"?
>
> "...inserted or updated by the command."  For a multiple-values insertion, the order of output rows will match the
orderthat rows are presented in the values or query clause.
 

Postgres's current implementation may be doing so, but I don't think
that can be guaranteed in possible implementations. I don't think
restricting choice of implementation to guarantee that is a good idea
either.

-- 
Best Wishes,
Ashutosh Bapat



Re: Insert Documentation - Returning Clause and Order

From
"David G. Johnston"
Date:
On Thursday, December 10, 2020, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
On Wed, Dec 9, 2020 at 9:10 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> Hey,
>
> Would it be accurate to add the following sentence to the INSERT documentation under "Outputs"?
>
> "...inserted or updated by the command."  For a multiple-values insertion, the order of output rows will match the order that rows are presented in the values or query clause.

Postgres's current implementation may be doing so, but I don't think
that can be guaranteed in possible implementations. I don't think
restricting choice of implementation to guarantee that is a good idea
either.


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”

David J.

Re: Insert Documentation - Returning Clause and Order

From
Ashutosh Bapat
Date:
On Thu, Dec 10, 2020 at 7:49 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Thursday, December 10, 2020, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
>>
>> On Wed, Dec 9, 2020 at 9:10 PM David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>> >
>> > Hey,
>> >
>> > Would it be accurate to add the following sentence to the INSERT documentation under "Outputs"?
>> >
>> > "...inserted or updated by the command."  For a multiple-values insertion, the order of output rows will match the
orderthat rows are presented in the values or query clause. 
>>
>> Postgres's current implementation may be doing so, but I don't think
>> that can be guaranteed in possible implementations. I don't think
>> restricting choice of implementation to guarantee that is a good idea
>> either.
>>
>
> Yeah, the ongoing work on parallel inserts would seem to be an issue.  We should probably document that though.  And
maybeas part of parallel inserts patch provide a user-specifiable way to ask for such a guarantee if needed.  ‘Insert
returningordered” 

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?

--
Best Wishes,
Ashutosh Bapat



Re: Insert Documentation - Returning Clause and Order

From
"David G. Johnston"
Date:
On Fri, Dec 11, 2020 at 6:24 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
On Thu, Dec 10, 2020 at 7:49 PM David G. Johnston
<david.g.johnston@gmail.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.

David J.

Re: Insert Documentation - Returning Clause and Order

From
James Coleman
Date:
On Friday, December 11, 2020, David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Fri, Dec 11, 2020 at 6:24 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
>>
>> On Thu, Dec 10, 2020 at 7:49 PM David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>>
>> > Yeah, the ongoing work on parallel inserts would seem to be an issue.  We should probably document that though.
Andmaybe as part of parallel inserts patch provide a user-specifiable way to ask for such a guarantee if needed.
‘Insertreturning 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
wouldbe 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



Re: Insert Documentation - Returning Clause and Order

From
"David G. Johnston"
Date:
On Sat, Dec 12, 2020 at 7:02 AM James Coleman <jtc331@gmail.com> wrote:

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.

Yet most ORM would perform single inserts at a time, not in bulk, making such a feature irrelevant to them.

I don't think having such a feature is all that important personally, but the question comes every so often and it would be nice to be able to point at the documentation for a definitive answer - not just one inferred from a lack of documentation - especially since the observed behavior is that order is preserved today.

David J.

Re: Insert Documentation - Returning Clause and Order

From
James Coleman
Date:
On Sat, Dec 12, 2020 at 10:11 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Sat, Dec 12, 2020 at 7:02 AM James Coleman <jtc331@gmail.com> wrote:
>>
>>
>> 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.
>
>
> Yet most ORM would perform single inserts at a time, not in bulk, making such a feature irrelevant to them.

I think that's a pretty hasty generalization. It's the majority of use
cases in an ORM, sure, but plenty of ORMs (and libraries or
applications using them) support inserting batches where performance
requires it. Rails/ActiveRecord is actually integrating that feature
into core (though many Ruby libraries already add that support, as
does, for example, the application I spend the majority of time
working on).

James



Re: Insert Documentation - Returning Clause and Order

From
Ashutosh Bapat
Date:
On Sat, Dec 12, 2020 at 8:41 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Sat, Dec 12, 2020 at 7:02 AM James Coleman <jtc331@gmail.com> wrote:
>>
>>
>> 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.
>
>
> Yet most ORM would perform single inserts at a time, not in bulk, making such a feature irrelevant to them.
>
> I don't think having such a feature is all that important personally, but the question comes every so often and it
wouldbe nice to be able to point at the documentation for a definitive answer - not just one inferred from a lack of
documentation- especially since the observed behavior is that order is preserved today. 
>

That's a valid usecase, but adding such a guarantee in documentation
would restrict implementation. So at best we can say "no order is
guaranteed". But we write what's guaranteed. Anything not written in
the documents is not guaranteed.

There are ways to get it working, but let's not go into those details
in this thread.

--
Best Wishes,
Ashutosh Bapat



Re: Insert Documentation - Returning Clause and Order

From
"David G. Johnston"
Date:
On Mon, Dec 14, 2020 at 7:09 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
But we write what's guaranteed. Anything not written in
the documents is not guaranteed.

In the case of LIMIT we go to great lengths to write what isn't guaranteed.  I suggest that this is similar enough in nature to warrant the same emphasis.

"Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

It is even possible for repeated executions of the same LIMIT query to return different subsets of the rows of a table, if there is not an ORDER BY to enforce selection of a deterministic subset. Again, this is not a bug; determinism of the results is simply not guaranteed in such a case."

I'd go so far as to say that it's more important here since the observed behavior is that things are ordered, and expected to be ordered, while with limit the non-determinism seems more obvious.

David J.