From: Mike Bayer <mike_mp@zzzcomputing.com>
To: Federico <cfederico87@gmail.com>
To: Thorsten Glaser <tg@evolvis.org>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Guidance on INSERT RETURNING order
Date: Tue, 11 Apr 2023 19:05:10 -0400
Message-ID: <1f38632d-a3b0-4122-bc06-5422f97598f2@app.fastmail.com> (raw)
In-Reply-To: <CAN19dydW+f2iQ4b0XrMWeoJBNMQMosWjbLc3uthZ=aG+EsuyCA@mail.gmail.com>
References: <CAN19dycS0oUorLOGgjxMX8NZq-hOA1RJrzwC4L3FxpMf4BQbBQ@mail.gmail.com>
<95fbd8c8-2f7a-38a1-4a41-b2ac3763d89c@aklaver.com>
<CAN19dyed0o5eCUYSEq30q39SpFmeZ7WSEHBfR042YYTVoipHcg@mail.gmail.com>
<a29ad53f-90ed-2e50-a28a-f3f15f3ddddc@evolvis.org>
<CAN19dydW+f2iQ4b0XrMWeoJBNMQMosWjbLc3uthZ=aG+EsuyCA@mail.gmail.com>
I'm not getting every reply in the list but I want to point this out from the archive version of this thread:
> > 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.
this should not matter. as in my other message if INSERT INTO table SELECT a, b FROM (VALUES () () ()) ORDER BY... takes the SERIAL or IDENTITY column of the table, and creates a parse tree for that looks like INSERT INTO table (SELECT nextval(table.id), a, b FROM (SELECT a, b FROM (VALUES () () ()) ORDER BY), the INSERT can put the rows in any way it wants. We don't care what INSERT does, we care about the generated sequence value, the nextval(table.id) part, which the SELECT should be emitting in order and occurs outside of the purview of the INSERT, according to other emails I have read on these lists (see my previous post).
On Tue, Apr 11, 2023, at 5:07 PM, Federico wrote:
> On Tue, 11 Apr 2023 at 22:59, Thorsten Glaser <tg@evolvis.org> wrote:
> >
> > On Tue, 11 Apr 2023, Federico wrote:
> >
> > >The problem here is not having the auto increment id in a particular
> >
> > The id might not even be auto-increment but UUID or something…
> > (I am surprised you would even try to insert multiple rows at once.)
>
> Well the documentation makes no mention of any limitation on returning
> and the observed behaviour has consistently been that returning is in
> values order.
> Again, that was SQLAlchemy's fault for assuming this (but the docs
> surely did not help).
>
> Also re-reading my reply, I've made a typo there, sorry. What it
> should have read is:
> The problem here is not having the returned ids in a particular
> order, is that there is apparently no correlation with the position of
> an element in the values clause with the id generated.
>
> Of course sorting the returned ids is only viable when using a serial
> or identity column, that's why in the general case I've mentioned the
> insert with sentinel column to ask if there are better or alternative
> solutions.
>
> Thanks for the reply, best
> Federico
>
> >
> > bye,
> > //mirabilos
> > --
> > 15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)
> >
> >
> >
> >
>
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, cfederico87@gmail.com, tg@evolvis.org
Subject: Re: Guidance on INSERT RETURNING order
In-Reply-To: <1f38632d-a3b0-4122-bc06-5422f97598f2@app.fastmail.com>
References: <CAN19dycS0oUorLOGgjxMX8NZq-hOA1RJrzwC4L3FxpMf4BQbBQ@mail.gmail.com> <95fbd8c8-2f7a-38a1-4a41-b2ac3763d89c@aklaver.com> <CAN19dyed0o5eCUYSEq30q39SpFmeZ7WSEHBfR042YYTVoipHcg@mail.gmail.com> <a29ad53f-90ed-2e50-a28a-f3f15f3ddddc@evolvis.org> <CAN19dydW+f2iQ4b0XrMWeoJBNMQMosWjbLc3uthZ=aG+EsuyCA@mail.gmail.com> <1f38632d-a3b0-4122-bc06-5422f97598f2@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