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]
<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.
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]
<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. Though we
are allergic to writing that out explicitly. Observations that aren't
documented are not guaranteed.
David J.
Show quoted text
"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:
/messages/by-id/CAMsr+YEn5TOuhv_tTwY70S1zXf8jCQ-uixU8aOs4OQs7kojf6Q@mail.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