Re: Insert Documentation - Returning Clause and Order
On Mon, Dec 14, 2020 at 7:09 AM Ashutosh Bapat 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.
Re: Insert Documentation - Returning Clause and Order
On Sat, Dec 12, 2020 at 8:41 PM David G. Johnston wrote: > > On Sat, Dec 12, 2020 at 7:02 AM James Coleman 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. > 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
On Sat, Dec 12, 2020 at 10:11 AM David G. Johnston wrote: > > On Sat, Dec 12, 2020 at 7:02 AM James Coleman 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
On Sat, Dec 12, 2020 at 7:02 AM James Coleman 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
On Friday, December 11, 2020, David G. Johnston wrote: > > On Fri, Dec 11, 2020 at 6:24 AM Ashutosh Bapat > wrote: >> >> On Thu, Dec 10, 2020 at 7:49 PM David G. Johnston >> 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
Re: Insert Documentation - Returning Clause and Order
On Fri, Dec 11, 2020 at 6:24 AM Ashutosh Bapat wrote: > On Thu, Dec 10, 2020 at 7:49 PM David G. Johnston > 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
On Thu, Dec 10, 2020 at 7:49 PM David G. Johnston wrote: > > On Thursday, December 10, 2020, Ashutosh Bapat > wrote: >> >> On Wed, Dec 9, 2020 at 9:10 PM David G. Johnston >> 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” 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
On Thursday, December 10, 2020, Ashutosh Bapat wrote: > On Wed, Dec 9, 2020 at 9:10 PM David G. Johnston > 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
On Wed, Dec 9, 2020 at 9:10 PM David G. Johnston 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. -- Best Wishes, Ashutosh Bapat
Insert Documentation - Returning Clause and Order
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. https://www.postgresql.org/docs/current/sql-insert.html David J.
