Re: Guidance on INSERT RETURNING order

On Tue, 11 Apr 2023 at 23:44, Thorsten Glaser <tg(at)evolvis(dot)org> wrote:
>
> On Tue, 11 Apr 2023, Federico wrote:
>
> >I was under the impression that when using INSERT SELECT ORDER BY the sequence
> >ids were generated using the select order.
>
> But someone said that’s not guaranteed, especially when INSERT will
> be parallelised later.

It was Tom Lane's message that said
> 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.

My reading of it is that we are talking about RETURNING, not about the order
in which the serial ids are generated. My understanding was that they
are generated
in select order, then the rows are inserted in any arbitrary order the
planner may choose
and returned again in any arbitrary order.

If my understanding is incorrect, would this alternative guarantee the above
(that nextval is called in the order set by ORDER BY),
again re-using the table in the original message?

INSERT INTO t(id, data)
SELECT nextval(pg_get_serial_sequence('t', 'id')) data
FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data, num)
ORDER BY num
RETURNING id

best,
Federico

> bye,
> //mirabilos
> --
> 15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)
>
>
>
>