Thread: RETURNING order guarantees documentation
Hi, I was looking for documentation on the ordering guarantees of RETURNING statements relative to the input data and found that there is nothing explicit.
The insert docs state
> If the INSERT command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) inserted or updated by the command.
'Similar to' doesn't provide anything concrete to the reader. There was a thread on [Hackers] back in 2015 proposing some docs to clearly explain that ordering is preserved and I am keen to write the documentation if it's agreed to be worth doing.
Thanks, Dan
On Sat, May 4, 2024, 10:13 Dan Wainwright <danw.cobus@gmail.com> wrote:
'Similar to' doesn't provide anything concrete to the reader. There was a thread on [Hackers] back in 2015 proposing some docs to clearly explain that ordering is preserved and I am keen to write the documentation if it's agreed to be worth doing.
Haven't reviewed the discussions recently but my understanding is that the lack of guarantee is correct and intentional. There is none. Though we are allergic to writing that out explicitly. Observations that aren't documented are not guaranteed.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Sat, May 4, 2024, 10:13 Dan Wainwright <danw.cobus@gmail.com> wrote: >> 'Similar to' doesn't provide anything concrete to the reader. There was a thread >> on [Hackers] >> <https://www.mail-archive.com/pgsql-hackers@postgresql.org/msg253743.html> >> back in 2015 proposing some docs to clearly explain that ordering is >> preserved and I am keen to write the documentation if it's agreed to be >> worth doing. > Haven't reviewed the discussions recently but my understanding is that the > lack of guarantee is correct and intentional. There is none. Indeed. If you must have ordering you can do something like with upd as (update foo set ... returning *) select * from upd order by ...; Otherwise it's going to be the order in which the rows were processed by the ModifyTable node, which is intentionally unspecified. The thread Dan refers to is here: https://www.postgresql.org/message-id/flat/CAMsr%2BYEn5TOuhv_tTwY70S1zXf8jCQ-uixU8aOs4OQs7kojf6Q%40mail.gmail.com and it doesn't seem to have gone anywhere. But I see that Craig was really only concerned with whether INSERTs are processed in the order returned by the data source, which is probably a pretty safe assumption. Still, SQL is a set-oriented language which means that it generally doesn't guarantee anything about row order, with the sole exception being the immediate output of a SELECT ... ORDER BY. So I think adding such guarantees isn't a great idea. regards, tom lane