Re: Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Hmm. If we do

> INSERT INTO foo
> SELECT ... ORDER BY

> is that not guaranteed to insert in the desired order?

Well, what do you mean by "insert in the desired order"? Not that the
rows are guaranteed to wind up physically stored in that order, I hope
--- heap_insert has always felt free to use available free space
opportunistically. I think it's reasonable to guarantee that default
expressions with side effects (serial nextval()s for instance) are
applied to the rows in the order they come out of the SELECT ... ORDER
BY, because otherwise the user would have no way to control that at all.
But beyond that particular interaction, a multi-row INSERT is a bulk
operation, and SQL has always viewed the results of bulk operations as
unordered sets.

The other issue, which is probably more relevant to the original
question, is what is the ordering of the rows produced by RETURNING.
Let's try a thought experiment here. Currently, RETURNING clauses are
implemented by computing the RETURNING list on-the-fly as each row is
processed by the Insert, Update, or Delete plan node. But for bulk
operations that were touching most or all of a table, it's conceivable
that it'd make more sense to produce the RETURNING output by rescanning
the table after-the-fact, looking for rows with the correct XID/CID
for the operation. In that case the output would come out in stored
ctid order, not the order the rows were processed in. Is that
fundamentally an illegitimate optimization, and if so why?

regards, tom lane