From: Mike Bayer <mike_mp@zzzcomputing.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Federico <cfederico87@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Guidance on INSERT RETURNING order
Date: Tue, 11 Apr 2023 17:07:15 -0400
Message-ID: <779f312a-4c10-437e-bfc9-3f4c8eaea18b@app.fastmail.com> (raw)
In-Reply-To: <510048.1681244547@sss.pgh.pa.us>
References: <CAN19dycS0oUorLOGgjxMX8NZq-hOA1RJrzwC4L3FxpMf4BQbBQ@mail.gmail.com>
<510048.1681244547@sss.pgh.pa.us>
On Tue, Apr 11, 2023, at 4:22 PM, Tom Lane wrote:
> 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.
if I can state this without having RETURNING getting in the way, because we know RETURNING is not ordered.
Start with this table:
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
a INT,
b INT
)
Then insert two rows, where id SERIAL fires implicitly, assume the next value the sequence will give us is N1, and then the value after that is N2. It doesn't matter what N1 and N2 are (don't need to be consecutive) but we want N2 > N1, that is, increasing.
INSERT INTO mytable (a, b) SELECT p1, p2 FROM (VALUES (10, 11, 1), (12, 13, 2)) AS sel(p1, p2, num) ORDER BY num
Then SELECT with ORDER BY:
SELECT id, a, b FROM mytable ORDER BY id
We want the results to be:
(N1, 10, 11)
(N2, 12, 13)
and we dont want them to *ever* be:
(N1, 12, 13)
(N2, 10, 11)
that is, we want the SERIAL column (or an IDENTITY also) to be lined up with the VALUES.
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: mike_mp@zzzcomputing.com, tgl@sss.pgh.pa.us, cfederico87@gmail.com
Subject: Re: Guidance on INSERT RETURNING order
In-Reply-To: <779f312a-4c10-437e-bfc9-3f4c8eaea18b@app.fastmail.com>
References: <CAN19dycS0oUorLOGgjxMX8NZq-hOA1RJrzwC4L3FxpMf4BQbBQ@mail.gmail.com> <510048.1681244547@sss.pgh.pa.us> <779f312a-4c10-437e-bfc9-3f4c8eaea18b@app.fastmail.com>
* 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