Define and document a guaranteed ordering for RETURNING?

Hi all

It's recently occurred to me that many people rely on the RETURNING clause
for multi-valued INSERTs, INSERT INTO ... SELECT, etc, returning rows in
the same order they were supplied as input into the INSERT. I often see
lock-step iteration over the inputs to an INSERT and the RETURNING
result-tuples used to map generated sequence values to the corresponding
input.

I can't find anything relevant in the docs about the order of results in
RETURNING, and it strikes me that this should probably be codified one way
or the other.

The docs simply read:

> The optional RETURNING clause causes INSERT to compute and return
value(s) based on each row actually inserted. This is primarily useful for
obtaining values that were supplied by defaults, such as a serial sequence
number. However, any expression using the table's columns is allowed. The
syntax of the RETURNING list is identical to that of the output list of
SELECT.

Looking at the code I don't see any reason why it wouldn't currently be
safe to assume that

INSERT ... VALUES (...), (...), ... RETURNING ...

and

INSERT ... SELECT ... ORDER BY ... RETURNING ...

always return rows in the exact order of their input. But that may not
always be true and if it's not documented as safe, it's not necessarily
wise to rely on it.

If you can't rely on RETURNING order you have to instead identify a
candidate key on the input and use a wider RETURNING result that includes
that candidate key as well as whatever else you want returned. Then map the
results onto the inputs. No fun at all.

So - I propose to document that INSERT ... RETURNING guarantees that the
result-tuples will be in the same order as the input to the INSERT ...
RETURNING statement. So if that order is well-defined, as in the case of
multi-VALUES clauses or SELECT ... ORDER BY ..., the result order is also
well defined.

(An alternative would be adding RETURNING ... WITH ORDINALITY. Which seems
pretty horrible).

Thoughts/comments? Barring objections I'll write up a docs patch for the
docs list.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services