Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmN3V-0007bB-Cv for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 23:05:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmN3S-0007uI-Sv for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 23:05:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmN3R-0007u0-Po for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 23:05:38 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pmN3N-002K6a-NR for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 23:05:37 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 61EEA5C00A9; Tue, 11 Apr 2023 19:05:31 -0400 (EDT) Received: from imap44 ([10.202.2.94]) by compute2.internal (MEProxy); Tue, 11 Apr 2023 19:05:31 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= zzzcomputing.com; h=cc:cc:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm3; t= 1681254331; x=1681340731; bh=YrHXEJFVT/XFnYf4MBNdESADh3jmcpRzZb9 sxC5WcyI=; b=YpGEGx65zO17fEaxvbX6vSDPl3lL/zsFOytti6rYQTorL86tqHS 6OSiFU95hefXQUVTjEY/eCe5KVVuRpHktuK5053RQlQ6Ut45mc55ZF86T8Po/9hB hog3uRX05lQuq01xY5yLZMBRAt8RC0YxHWb+mbbjGesG5mwJrl+CNXNSK0GmY1/6 P8kcuhDsdargVZVULilI94wYbVFEHrCOpKXdKSFAJzQiVhNj5NAk3O7djaDKIhfO 0nL7Jdez7ZIjDZhhWxwfwogKdgL5kfLvh7KaA0Ft6UoqWU8axUbfnKL0SbDV8lH6 Sr3ikOIzZbGTJoXKWou0MuEGdWgOJfJaWiQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm3; t=1681254331; x=1681340731; bh=YrHXEJFVT/XFn Yf4MBNdESADh3jmcpRzZb9sxC5WcyI=; b=I2+ORgjxtft6pWLZ1JeiOObAaXRlz le5DdwSCA5n2mM2nE69vypd5Sh0q2zph2f/j46skAfGVBsYpjsH9Am6l53m/ukYr 3SBn9cOE5JGmtaWLzWnTY0sre1hEIdap2q1jY5x7kVmC2JH5A1evZU1G0mU0nJWI 5AtpPjAF92hrKRULjsM/+fCghTsxt9R+yn2IW+60BZqYyoNu2xrqrT18cWa0aukQ 0Kq2yp3eBTXKB5gcWx7zvNXYAmVsdiwuYQPoVAf7XX6SOAJ7wZRsBE1L9PATexl2 A4Z34A6zcsGezMt0kCh+K+I4lDug0b9281RRep4/AyX3wnw9muv0G8RBg== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvdekhedgudejucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepofgfggfkjghffffhvfevufgtsegrtderreerreejnecuhfhrohhmpedfofhi khgvuceurgihvghrfdcuoehmihhkvggpmhhpseiiiiiitghomhhpuhhtihhnghdrtghomh eqnecuggftrfgrthhtvghrnhepheehfeeutdfgfffgffevkeelhfetleetuefhheegvdef leejleethfdujeejuedtnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrg hilhhfrhhomhepmhhikhgvpghmphesiiiiiigtohhmphhuthhinhhgrdgtohhm X-ME-Proxy: Feedback-ID: i1c194411:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id D38D136A0075; Tue, 11 Apr 2023 19:05:30 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.9.0-alpha0-334-g8c072af647-fm-20230330.001-g8c072af6 Mime-Version: 1.0 Message-Id: <1f38632d-a3b0-4122-bc06-5422f97598f2@app.fastmail.com> In-Reply-To: References: <95fbd8c8-2f7a-38a1-4a41-b2ac3763d89c@aklaver.com> Date: Tue, 11 Apr 2023 19:05:10 -0400 From: "Mike Bayer" To: Federico , "Thorsten Glaser" Cc: pgsql-general@lists.postgresql.org Subject: Re: Guidance on INSERT RETURNING order Content-Type: multipart/alternative; boundary=c2c04f717e0a4966aa22a44e5c4ccaa1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --c2c04f717e0a4966aa22a44e5c4ccaa1 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: quoted-printable I'm not getting every reply in the list but I want to point this out fro= m the archive version of this thread: > > I was under the impression that when using INSERT SELECT ORDER BY th= e sequence > > ids were generated using the select order. > But someone said that=E2=80=99s not guaranteed, especially when INSERT= will > be parallelised later. this should not matter. as in my other message if INSERT INTO table SE= LECT a, b FROM (VALUES () () ()) ORDER BY... takes the SERIAL or IDENTIT= Y column of the table, and creates a parse tree for that looks like INSE= RT INTO table (SELECT nextval(table.id), a, b FROM (SELECT a, b FROM (VA= LUES () () ()) ORDER BY), the INSERT can put the rows in any way it want= s. 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 o= ther 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 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=E2=80=A6 > > (I am surprised you would even try to insert multiple rows at once.) >=20 > 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). >=20 > 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. >=20 > 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. >=20 > Thanks for the reply, best > Federico >=20 > > > > bye, > > //mirabilos > > -- > > 15:41=E2=8E=9C Somebody write a testsuite fo= r helloworld :-) > > > > > > > > >=20 --c2c04f717e0a4966aa22a44e5c4ccaa1 Content-Type: text/html;charset=utf-8 Content-Transfer-Encoding: quoted-printable
I'm not getting= every reply in the list but I want to point this out from the archive v= ersion of this thread:

> > I w= as under the impression that when using INSERT SELECT ORDER BY the seque= nce
> > ids were generated using the select order.

> But someone said that=E2=80=99s not guar= anteed, especially when INSERT will
> be parallelised l= ater.

this should not matter.   a= s 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 nextv= al(table.id), a, b FROM (SELECT a, b FROM (VALUES () () ()) ORDER BY), t= he 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(ta= ble.id) part, which the SELECT should be emitting in order and occurs ou= tside of the purview of the INSERT, according to other emails I have rea= d 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.or= g> wrote:
>
> On Tue, 11 Apr 20= 23, Federico wrote:
>
> >The proble= m here is not having the auto increment id in a particular
>
> The id might not even be auto-increment but UUI= D or something=E2=80=A6
> (I am surprised you would eve= n try to insert multiple rows at once.)

Wel= l the documentation makes no mention of any limitation on returning
<= /div>
and the observed behaviour has consistently been that returnin= g is in
values order.
Again, that was SQLAlc= hemy's fault for assuming this (but the docs
surely did no= t help).

Also re-reading my reply, I've mad= e a typo there, sorry. What it
should have read is:
  The problem here is not having the returned ids in a part= icular
order, is that there is apparently no correlation w= ith the position of
an element in the values clause with t= he id generated.

Of course sorting the retu= rned ids is only viable when using a serial
or identity co= lumn, that's why in the general case I've mentioned the
in= sert with sentinel column to ask if there are better or alternative
<= /div>
solutions.

Thanks for the reply, = best
    Federico

<= div>>
> bye,
> //mirabilos
> --
> 15:41=E2=8E=9C<Lo-lan-do:#fusionforg= e> Somebody write a testsuite for helloworld :-)
>
>
>
>

--c2c04f717e0a4966aa22a44e5c4ccaa1--