pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Federico <cfederico87@gmail.com>
Cc: pgsql-general@lists.postgresql.org, Mike Bayer <mike_mp@zzzcomputing.com>
Subject: Re: Guidance on INSERT RETURNING order
Date: Tue, 11 Apr 2023 16:22:27 -0400
Message-ID: <510048.1681244547@sss.pgh.pa.us> (raw)
In-Reply-To: <CAN19dycS0oUorLOGgjxMX8NZq-hOA1RJrzwC4L3FxpMf4BQbBQ@mail.gmail.com>
References: <CAN19dycS0oUorLOGgjxMX8NZq-hOA1RJrzwC4L3FxpMf4BQbBQ@mail.gmail.com>

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





view thread (56+ messages)  latest in thread

Message-ID: <510048.1681244547@sss.pgh.pa.us>
Permalink:  ../510048.1681244547@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/510048.1681244547@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, cfederico87@gmail.com, mike_mp@zzzcomputing.com
  Subject: Re: Guidance on INSERT RETURNING order
  In-Reply-To: <510048.1681244547@sss.pgh.pa.us>
  References: <CAN19dycS0oUorLOGgjxMX8NZq-hOA1RJrzwC4L3FxpMf4BQbBQ@mail.gmail.com> <510048.1681244547@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox