pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedRETURNING order guarantees documentation
3+ messages / 3 participants
[nested] [flat]
* RETURNING order guarantees documentation
@ 2024-05-04 17:12 Dan Wainwright <danw.cobus@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Dan Wainwright @ 2024-05-04 17:12 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: RETURNING order guarantees documentation
@ 2024-05-04 17:29 David G. Johnston <david.g.johnston@gmail.com>
parent: Dan Wainwright <danw.cobus@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2024-05-04 17:29 UTC (permalink / raw)
To: Dan Wainwright <danw.cobus@gmail.com>; +Cc: PostgreSQL Documentation <pgsql-docs@lists.postgresql.org>
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.
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: RETURNING order guarantees documentation
@ 2024-05-04 17:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2024-05-04 17:52 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Dan Wainwright <danw.cobus@gmail.com>; PostgreSQL Documentation <pgsql-docs@lists.postgresql.org>
"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%40m...
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-05-04 17:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-04 17:12 RETURNING order guarantees documentation Dan Wainwright <danw.cobus@gmail.com>
2024-05-04 17:29 ` David G. Johnston <david.g.johnston@gmail.com>
2024-05-04 17:52 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox