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 1pmk3Q-00018m-9t for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 23:39:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmk3P-0003Wc-6K for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 23:39:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmk3N-0003WS-G7 for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 23:39:06 +0000 Received: from wout5-smtp.messagingengine.com ([64.147.123.21]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmk3J-0006EQ-UB for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 23:39:04 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.west.internal (Postfix) with ESMTP id D69C73200977 for ; Wed, 12 Apr 2023 19:38:59 -0400 (EDT) Received: from imap44 ([10.202.2.94]) by compute2.internal (MEProxy); Wed, 12 Apr 2023 19:38:59 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= zzzcomputing.com; h=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=1681342739; x= 1681429139; bh=RLhHY58aVpR2nQiFxfjfbeoqTJENUwGAM1z9WFhDW3c=; b=n BxIrxCfTExYh0fjABMkOdavVM7RO9ne+dv2WT1YpskvhoyXnxuExBYaGVSEAcO5c t1QnhxTUitpXLdCIeX2cybKjIUlmKKPWrN/3I3RJSAOhnq+kfLDWmKF2d9qyrbpH X5TODJcoilKFtuhfCHrw5PpnmjoQb3B/D3QtFzPDnGpqAyue9XiyvD8yKdo7vy/e tzadagKEwnMKwqtNGFEHgOC19UtFLTKmM78TwcDs4zUhkjBhMx1ere8ti+HZShZh k7bkC5urstLZuTo0/1w5klYiWJZ2vg0YDghNTJ/MLhREObRNu58Wsyc+w5jkIAt6 IdJn+130h8GjJ6z2atOfQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=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=1681342739; x=1681429139; bh=RLhHY58aVpR2n QiFxfjfbeoqTJENUwGAM1z9WFhDW3c=; b=hBBFUL5YjtFTG0lklwz8sFS6Tag60 oJoMVtr+/fNjh3NABQSzqVdqjDtCOuQl9CSU8s9c/4ssLi0vU3w7RxPvmvkL78Jn FD5syvBaT9QNQhJwAyU0czrvqsZ6cXLTLt0h+clUMecBDPcIBbZ7k8r7ZY1JIQQp FMOKaV2I7ku4SP4+nCItms7wF6qwQ1sqZUwmj9fKTVb0iFgvAbQkzQG8/cRVWvY1 K+79+veQBTt7t/2YTuS/NCy3kO6Ds8U1OHpzJLxM7osFZkYgLFGY8831Vhx92WE7 /JYkzxGoheRkxlEm39lTMGi5pXryl45u0Tkl0QRMP9xAQIq5iv90iWlVg== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvdekjedgvdehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfgjfhffhffvufgtsegrtd erreerreejnecuhfhrohhmpedfofhikhgvuceurgihvghrfdcuoehmihhkvggpmhhpseii iiiitghomhhpuhhtihhnghdrtghomheqnecuggftrfgrthhtvghrnhepheegkeefudegue egheehheetffffkeefveekleejkeetgeefgfdvgffgveduffdunecuffhomhgrihhnpehp ohhsthhgrhgvshhqlhdrohhrghdpmhhitghrohhsohhfthdrtghomhdpmhgrihhlqdgrrh gthhhivhgvrdgtohhmnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghi lhhfrhhomhepmhhikhgvpghmphesiiiiiigtohhmphhuthhinhhgrdgtohhm X-ME-Proxy: Feedback-ID: i1c194411:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 453BA36A0073; Wed, 12 Apr 2023 19:38:59 -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: In-Reply-To: References: <95fbd8c8-2f7a-38a1-4a41-b2ac3763d89c@aklaver.com> Date: Wed, 12 Apr 2023 19:38:38 -0400 From: "Mike Bayer" To: pgsql-general@lists.postgresql.org Subject: Re: Guidance on INSERT RETURNING order Content-Type: multipart/alternative; boundary=67e8feaeaa364fd7b40928779920c8c4 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --67e8feaeaa364fd7b40928779920c8c4 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: quoted-printable On Wed, Apr 12, 2023, at 6:24 PM, Thorsten Glaser wrote: > On Wed, 12 Apr 2023, Kirk Wolak wrote: >=20 > >The solution exists. Pre-fetch the IDs, assign them and insert them = with > >the IDs. Then you have 100% control. > >SELECT NEXTVAL('tbl_seq') from GENERATE_SERIES(1, ); >=20 > This would be a nice solution=E2=80=A6 but the PK could be always gene= rated, > or not even sequential (UUIDs for example). If you=E2=80=99re developi= ng > schema-first the application would not even (need to) know about how > the IDs are generated as it=E2=80=99s the DB that generates them. >=20 > AIUI the OP=E2=80=99s an SQLAlchemy developer, which is an ORM. Theref= ore he > needs a generic solution. im my view I already have the solution which is the form I referred to i= n my earlier email: INSERT INTO mytable (a, b)=20 SELECT p1, p2 FROM (VALUES (10, 11, 1), (12, 13, 2)) AS sel(p1, p2, num)= ORDER BY num=20 RETURNING mytable.id the columns we want to INSERT, a and b, come from p1 and p2 in the SELEC= T. the third value in each table-row value inside the VALUES clause we c= all the "sentinel" and we apply it as an incrementing number which we al= so ORDER BY. per Tom Lane's email at https://www.postgresql.org/message-id/29386.1528= 813619%40sss.pgh.pa.us in 2018, assuming mytable.id is serial (or I woul= d assume IDENTITY), this resolves to something equivalent to: INSERT INTO mytable (id, a, b) SELECT nextval("mytable_id_seq"), p1, p2 FROM ( SELECT p1, p2 FROM (VALUES (10, 11, 1), (12, 13, 2)) AS sel(p1, p2, = num) ORDER BY num ) AS ss RETURNING mytable.id So the above is one part we want to confirm is accurate, hasn't changed,= is not going to change much as far as its ultimate behavior. It means = that as rows are generated from the SELECT, the ascending values for "my= table_id_seq" will correspond to the ascending values for "num". That's= the important part; we are sending in an number that controls the sorti= ng of records as they are delivered outwards, and the generated identifi= ers, assuming they are incrementing, will sort in that same way as each = value is associated with the rows emanating from the inner SELECT. it d= oes not matter if the sequence has gaps in it from other transactions or= whatever, only that it increments in the same direction as "num" which = we pass in. In this sequence of events, the INSERT conceptually hasn't even happened= for a particular row before the row has been associated with a sequence= value. INSERT can do whatever it wants, it can break out into 20 threa= ds and jumble up all the rows or whatever. When it sends them out in RE= TURNING, whatever order it comes back is no problem; we just want those = "mytable_id_seq" values back. We sort by that when we get the rows back= . This ordering matches the one we put in. So the way these threads of= ten seem to discuss how INSERTS and RETURNING are both not ordered are m= issing this particular point, that we really just need a way to relate s= erver generated values to the rows we put in. =20 This form comes from two places, one is from Microsoft SQL Server which = documents as well as guaranteed to maintain order at https://learn.micro= soft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=3Dsql-serve= r-ver16; "INSERT queries that use SELECT with ORDER BY to populate rows = guarantees how identity values are computed but not the order in which t= he rows are inserted." ; for PostgreSQL, the 2018 thread above seems to = confirm a similar behavior for PostgreSQL. So we were looking for feedback from PG developers on the above assumpti= ons. Beyond that, throughout all the theoretical talk about "well PG mi= ght change this someday", while PG most certainly can and maybe will cha= nge some of these assumptions, it would produce a lot of surprises for m= any users who knowingly or unknowingly have relied on various orderings = of this way and it would likely lead to some very good documentation abo= ut this area of functionality, maybe even some new syntaxes or keywords = to help with this extremely common use case. Documentation for these th= ings is what the various threads on this subject often ask for. The fir= st such thread I can find asking about this, which also ended kind of am= biguously, was in 2012 here: https://www.mail-archive.com/pgsql-hackers@= postgresql.org/msg204156.html . Then there's the 2018 thread, and als= o a bunch on stackoverflow. Everyone is left guessing or not totally su= re since there's no official documentation for this. We're going to document in SQLAlchemy the approaches we are using for di= fferent backends and I wouldnt be surprised if when people google for "P= ostgreSQL INSERT RETURNING ORDERING" our own docs are going to come up n= ear the top. That seems to happen a lot. --67e8feaeaa364fd7b40928779920c8c4 Content-Type: text/html;charset=utf-8 Content-Transfer-Encoding: quoted-printable

=
On Wed, Apr 12, 2023, at 6:24 PM, Thorsten Glaser wrote:<= br>
On Wed, 12 = Apr 2023, Kirk Wolak wrote:

>The solutio= n exists.  Pre-fetch the IDs, assign them and insert them with
<= /div>
>the IDs.  Then you have 100% control.
&= gt;SELECT NEXTVAL('tbl_seq') from GENERATE_SERIES(1, <total_needed>= ;);

This would be a nice solution=E2=80=A6 = but the PK could be always generated,
or not even sequenti= al (UUIDs for example). If you=E2=80=99re developing
schem= a-first the application would not even (need to) know about how
the IDs are generated as it=E2=80=99s the DB that generates them.<= br>

AIUI the OP=E2=80=99s an SQLAlchemy develop= er, which is an ORM. Therefore he
needs a generic solution= .

im my view I already have th= e solution which is the form I referred to in my earlier email:

INSERT INTO mytable (a, b)
SELECT p1= , p2 FROM (VALUES (10, 11, 1), (12, 13, 2)) AS sel(p1, p2, num) ORDER BY= num
RETURNING mytable.id

th= e columns we want to INSERT, a and b, come from p1 and p2 in the SELECT.= the third value in each table-row value inside the VALUES clause we cal= l the "sentinel" and we apply it as an incrementing number which we also= ORDER BY.


per Tom Lane's em= ail at https://www.postgresql.org/message-id/29386.1528813619= %40sss.pgh.pa.us in 2018, assuming mytable.id is serial (or I would = assume IDENTITY), this resolves to something equivalent to:

INSERT INTO mytable (id, a, b)
SELECT nex= tval("mytable_id_seq"), p1, p2 FROM (
    S= ELECT p1, p2 FROM (VALUES (10, 11, 1), (12, 13, 2)) AS sel(p1, p2, num) = ORDER BY num
) AS ss RETURNING mytable.id


So the above is one part we want to confirm is a= ccurate, hasn't changed, is not going to change much as far as its ultim= ate behavior.  It means that as rows are generated from the SELECT,= the ascending values for "mytable_id_seq" will correspond to the ascend= ing values for "num".  That's the important part; we are sending in= an number that controls the sorting of records as they are delivered ou= twards, and the generated identifiers, assuming they are incrementing, w= ill sort in that same way as each value is associated with the rows eman= ating from the inner SELECT.  it does not matter if the sequence ha= s gaps in it from other transactions or whatever, only that it increment= s in the same direction as "num" which we pass in.

In this sequence of events, the INSERT conceptually hasn't even = happened for a particular row before the row has been associated with a = sequence value.  INSERT can do whatever it wants, it can break out = into 20 threads and jumble up all the rows or whatever.  When it se= nds them out in RETURNING, whatever order it comes back is no problem; w= e just want those "mytable_id_seq" values back.  We sort by that wh= en we get the rows back.  This ordering matches the one we put in.&= nbsp; So the way these threads often seem to discuss how INSERTS and RET= URNING are both not ordered are missing this particular point, that we r= eally just need a way to relate server generated values to the rows we p= ut in. 

This form comes from two plac= es, one is from Microsoft SQL Server which documents as well as guarante= ed to maintain order at https://l= earn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=3D= sql-server-ver16; "INSERT queries that use SELECT with ORDER BY to p= opulate rows guarantees how identity values are computed but not the order in which the rows=20 are inserted." ; for PostgreSQL, the 2018 thread above seems to confirm = a similar behavior for PostgreSQL.

So we we= re looking for feedback from PG developers on the above assumptions.&nbs= p; Beyond that, throughout all the theoretical talk about "well PG might= change this someday", while PG most certainly can and maybe will change= some of these assumptions, it would produce a lot of surprises for many= users who knowingly or unknowingly have relied on various orderings of = this way and it would likely lead to some very good documentation about = this area of functionality, maybe even some new syntaxes or keywords to = help with this extremely common use case.  Documentation for these = things is what the various threads on this subject often ask for.  = The first such thread I can find asking about this, which also ended kin= d of ambiguously, was in 2012 here: https://www.mail-archiv= e.com/pgsql-hackers@postgresql.org/msg204156.html  .  = ; Then there's the 2018 thread, and also a bunch on stackoverflow. = Everyone is left guessing or not totally sure since there's no official= documentation for this.

We're going to doc= ument in SQLAlchemy the approaches we are using for different backends a= nd I wouldnt be surprised if when people google for "PostgreSQL INSERT R= ETURNING ORDERING" our own docs are going to come up near the top. = That seems to happen a lot.




--67e8feaeaa364fd7b40928779920c8c4--