Andrew Dunstan <and...@dunslane.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers