Document ordering guarantees on INSERT/UPDATE RETURNING clause
Hi all,
I've seen various discussions around whether PG makes any guarantees on the
ordering of rows returned by the RETURNING clause (e.g. [1]https://stackoverflow.com/questions/5439293/is-insert-returning-guaranteed-to-return-things-in-the-right-order). In a
nutshell, when executing a statement such as the following:
CREATE TABLE foo (id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, data
INT);
INSERT INTO foo (data) VALUES (8), (9), (10) RETURNING id, data;
... us the INSERT guaranteed to return the rows (1,8), (2,9) and (3,10)
(and in that order)? This point is important when inserting multiple rows
and wanting to e.g. match a database-generated ID back to memory structures
on the client.
FWIW I've received feedback from a SQL Server engineer that one definitely
should *not* depend on such ordering there, and that future optimizations
(e.g. parallel insertion of many rows) could result in row ordering which
differs from the lexical ordering of the VALUES clause. That seems very
reasonable; if the situation is similar on PostgreSQL, then I'd suggest
making that very clear in the INSERT[2]https://www.postgresql.org/docs/current/sql-insert.html and UPDATE[3]https://www.postgresql.org/docs/current/sql-update.html docs. I'd also
possibly point to the workaround of wrapping the INSERT/UPDATE in a CTE
which then defines the ordering.
Thanks,
Shay
[1]: https://stackoverflow.com/questions/5439293/is-insert-returning-guaranteed-to-return-things-in-the-right-order
https://stackoverflow.com/questions/5439293/is-insert-returning-guaranteed-to-return-things-in-the-right-order
[2]: https://www.postgresql.org/docs/current/sql-insert.html
[3]: https://www.postgresql.org/docs/current/sql-update.html
On Sat, Feb 26, 2022 at 5:42 AM Shay Rojansky <roji@roji.org> wrote:
FWIW I've received feedback from a SQL Server engineer that one definitely
should *not* depend on such ordering there, and that future optimizations
(e.g. parallel insertion of many rows) could result in row ordering which
differs from the lexical ordering of the VALUES clause.
That seems very reasonable; if the situation is similar on PostgreSQL,
then I'd suggest making that very clear in the INSERT[2] and UPDATE[3] docs.
There is clearly no mention of such a guarantee in our documentation.
David J.
Hi,
On Sat, Feb 26, 2022 at 06:25:22AM -0700, David G. Johnston wrote:
On Sat, Feb 26, 2022 at 5:42 AM Shay Rojansky <roji@roji.org> wrote:
FWIW I've received feedback from a SQL Server engineer that one definitely
should *not* depend on such ordering there, and that future optimizations
(e.g. parallel insertion of many rows) could result in row ordering which
differs from the lexical ordering of the VALUES clause.That seems very reasonable; if the situation is similar on PostgreSQL,
then I'd suggest making that very clear in the INSERT[2] and UPDATE[3] docs.There is clearly no mention of such a guarantee in our documentation.
Yes, which is just how SQL works: a set doesn't have any ordering unless an
explicit one has been defined, RETURNING is no exception to that.
That seems very reasonable; if the situation is similar on PostgreSQL,
then I'd suggest making that very clear in the INSERT[2] and
UPDATE[3] docs.
There is clearly no mention of such a guarantee in our documentation.
Yes, which is just how SQL works: a set doesn't have any ordering unless
an
explicit one has been defined, RETURNING is no exception to that.
Thanks for confirming that such a guarantee doesn't exist. I would still
suggest explicitly calling that out in the docs around RETURNING, since
that seems like an understand pitfall; personally-speaking, this certainly
wasn't clear to me when first looking at it (even if it is now).