Document ordering guarantees on INSERT/UPDATE RETURNING clause - Mailing list pgsql-hackers

I've seen various discussions around whether PG makes any guarantees on the ordering of rows returned by the RETURNING clause (e.g. [1]). 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] and UPDATE[3] 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

[2] https://www.postgresql.org/docs/current/sql-insert.html

[3] https://www.postgresql.org/docs/current/sql-update.html