Federico <cfederico87@gmail.com> writes:
> Searching the archive seems that a using the INSERT SELECT ORDER BY
> form should be a better solution,
> so the above insert should be rewritten as
> INSERT INTO t(data)
> SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,
> num) ORDER BY num
> RETURNING id
> to ensure that the id are created in the order specified by num. The
> returned id can again be in
> arbitrary order, but sorting them should enable correctly matching the
> orm object so that they can
> be properly updated.
> Is this correct?
No. Sadly, adding that ORDER BY is just voodoo programming, because
it applies to the result of the SELECT while promising nothing about
the order in which INSERT/RETURNING will act on those rows.
Re-reading that 2012 thread, the main new observation I'd make today
is that parallel operation is a thing now, and it's not hard to foresee
that sometime soon we'll want to parallelize INSERTs. Which'd make it
*really* hard to promise anything about the order of RETURNING output.
I think if you want to use RETURNING with multi-row inserts, the
thing to do is more like
INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING data, id
and then explicitly match up the returned "data" values rather than
presuming they appear in the same order you wrote them in in VALUES.
Admittedly this might be problematic if some of the VALUES rows
are identical, but how much should you care?
regards, tom lane