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 1pmoX7-0000sg-7X for pgsql-general@arkaria.postgresql.org; Thu, 13 Apr 2023 04:26:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmoX5-0007Xy-Ml for pgsql-general@arkaria.postgresql.org; Thu, 13 Apr 2023 04:26:03 +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 1pmoX4-0007Xc-Ck for pgsql-general@lists.postgresql.org; Thu, 13 Apr 2023 04:26:03 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmoWw-0000Om-VK for pgsql-general@lists.postgresql.org; Thu, 13 Apr 2023 04:26:01 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id E1FCD5C00F8 for ; Thu, 13 Apr 2023 00:25:53 -0400 (EDT) Received: from imap44 ([10.202.2.94]) by compute2.internal (MEProxy); Thu, 13 Apr 2023 00:25:53 -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=1681359953; x= 1681446353; bh=SVIzs1mHvOUao0A1akLcVvwhpxJDp5R+VDdT9hdfNP4=; b=q S9QxCaFEUX96FlhZXbKJH5vzyU9f3UCPvJoTtvsAPaPFlvRZpAe18yGy3W2f+TSW Tr3iViOPQujUA0ioZDtj9KDnCU8JG4CkT2Po9vSQePxz5N4wccM97hSzldD+u/fk yFjOwC+0HkORuBXMpMp30r7yVoB+LrF5yBIZuZe+0lc9qs2oEF+5i/fZUJ6V4Msw DEMx1V+SoaHi1NVeKcw27rBY1AALkz5fevm/oOjzq/whc+VCgG+VXWYjvdA9+AWm biyeT9cgSpafsfCHCMfSNRHOh132GsVGaO/x350fioaFafqlsEi1UiHC7IKK+z1Z UoBa/KQXU/+RkmJ+PggRQ== 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=1681359953; x=1681446353; bh=SVIzs1mHvOUao 0A1akLcVvwhpxJDp5R+VDdT9hdfNP4=; b=fMYNS0w2+XkoW+gFOf/yGvgIA7uYp DpWxA7ZnsLqEGs7uaQmUi9DK3ctLaWxhJCFCyuDt15sh0EyBM+koVIbu2Gvs3PAe FcKJ8UODKqaGNetlwgVa0Kjh7P4SVyLiZoFBD1oB3c1PDr4Wg1DhwjBfbxEAN8cP qQGt/o7a7AheT/yd7jLRUhGYkOyKyXj3VHXMByK2d8VtNT+g2DXoJ0UVGGANsXvk CCKZQEDada89D7wPHzvwfw3w2yRPLOHfC3PjDf9yJ+kD3qzJH74+yfF3jXZp03EK PN5eyQ62sCjkZbetFbj4Gg2ARP+Y0Vi9iqpKPXR4IDh54zVkF1SPe3a0A== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvdekjedgkeegucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfgjfhffhffvufgtsegrtd erreerreejnecuhfhrohhmpedfofhikhgvuceurgihvghrfdcuoehmihhkvggpmhhpseii iiiitghomhhpuhhtihhnghdrtghomheqnecuggftrfgrthhtvghrnhepteehfeegfffgud eggffgvedvieelgeevueelheejfeehgfeuteehheekjeffheetnecuffhomhgrihhnpehg ihhthhhusgdrtghomhdpphhoshhtghhrvghsqhhlrdhorhhgnecuvehluhhsthgvrhfuih iivgeptdenucfrrghrrghmpehmrghilhhfrhhomhepmhhikhgvpghmphesiiiiiigtohhm phhuthhinhhgrdgtohhm X-ME-Proxy: Feedback-ID: i1c194411:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 5CFEA36A0075; Thu, 13 Apr 2023 00:25:53 -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> <84746f13-fc7f-9126-12f3-ae5fbd5e6b79@evolvis.org> <86f9eae7-acd6-45e7-b8a6-2a24b42fe306@app.fastmail.com> Date: Thu, 13 Apr 2023 00:25:22 -0400 From: "Mike Bayer" To: pgsql-general@lists.postgresql.org Subject: Re: Guidance on INSERT RETURNING order Content-Type: multipart/alternative; boundary=57815172a8dd486f85d10da80ecb3a5b List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --57815172a8dd486f85d10da80ecb3a5b Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: quoted-printable here's what would be cool, a new token called "tuple_order" or something INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING= table.id, inserted.tuple_order tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correl= ate the each row delivered by RETURNING to each entry in the VALUES clau= se, in the order they were stated in that VALUES clause, that is entry (= 1, 2, 3) would be tuple_order 1, entry (4, 5, 6) would be tuple order 2,= etc. That token would solve the entire problem in all cases. The 1800 lin= e changeset I've been working on all week would go away (if every databa= se had this) and the change would be reduced to a few dozen lines. On Wed, Apr 12, 2023, at 11:05 PM, John Howroyd wrote: > To clarify, the work around (for SQLAlchemy) does not require the RETU= RNING values to be ordered; they will be reordered code side. Longer te= rm matching the order up would be an added bonus! >=20 > On Thu, 13 Apr 2023 at 04:02, John Howroyd = wrote: >> The ideal solution would be that the RETURNING values are in the same= order as the declared rows in the table literal "FROM (VALUES (10, 11, = 1), (12, 13, 2)) AS sel(p1, p2, num)". But before any of you jump down = my throat about about sets and a bar to parallel inserts, I think it's a= small matter of some internal smoke and mirrors: simply marshal the RET= URNING results through and in memory/temporary table maintaining the ord= er from the SELECT and return these to the client upon commit. At this = moment, I believe the folk at SQLAlchemy are looking for a work around t= hat can work for most use cases with either an identity column or a fake= "sentinel" column for matching back to the ORM objects. There may be a= n upper limit of 1000 to the number rows in a single insert (this arises= in MS SQL Server, who absolutely do not guarantee the order of their O= UTPUT clause -- it's tied to the physical row insert rather than marshal= led). >>=20 >> My feeling is that PostgreSQL may already do this. See the commit: >> https://github.com/postgres/postgres/commit/9118d03a8cca3d97327c56bf8= 9a72e328e454e63 >> and the original message thread >> https://www.postgresql.org/message-id/flat/CADBwkHv1nUx5bqxqRLp26k8iU= WSnY3uW-1_0EMYxKns0t2JnUA%40mail.gmail.com >> and references to undesired behaviour prior to PostgreSQL 9.6 such as= in https://www.postgresql.org/docs/current/sql-select.html. >>=20 >> The test cases in the above commit use: >> SELECT nextval('sequence'), datavals FROM ... ORDER BY something; >> type syntax. And I presume that's exactly what's happening internall= y when there's a serial identity column (via query rewrite rules or anot= her mechanism). >>=20 >> So really, that just needs confirming. What happens with triggers? = Presumably the same as long as query rewrite rules are used, but this is= presumably getting deeper into the code for actual inserts after the in= itial select. The jump to the output being ordered, is just a guess but= there's some bits and pieces that seem to suggest that there may indeed= be a marshalling process going on in the background (whether or not tha= t is linked to the original order is another matter). >>=20 >> I have set up a PostgreSQL server to test if I can break this hypothe= sis and see what query explains can allude to. Does anyone have a test = case where the order is not preserved? >>=20 >> Might I also point out that if the implementation of parallel inserts= does create a bar then doing so may end up with the programmatic interf= aces (such as SQLAlchemy) not being able to use that feature (possibly r= everting back to single inserts). Ur, so what would be the problem bein= g solved with parallel inserts? >>=20 >> On Thu, 13 Apr 2023 at 02:27, Mike Bayer w= rote: >>> We do something different for uuids. These are usually created clie= nt side anyway or are very easy to create client side if not and then we= also have a separate sentinel column option.=20 >>>=20 >>> Overall the whole thing is an optimization that can be turned off fo= r uncommon cases. We just can't go back to having insert of 1000 rows = be 3-5x slower for all tables w server generated primary keys which is w= hat statement at a time gets us. Surrogate integer Pks w sequence or id= entity is the big case. >>>=20 >>> On Wed, Apr 12, 2023, at 8:58 PM, Thorsten Glaser wrote: >>> > On Wed, 12 Apr 2023, Mike Bayer wrote: >>> > >>> >>ascending values for "mytable_id_seq" will correspond to the ascen= ding >>> >>values for "num". >>> > >>> > But, again, what if it=E2=80=99s uuid or something instead of a se= quence? >>> > >>> > bye, >>> > //mirabilos >>> > --=20 >>> > 15:41=E2=8E=9C Somebody write a testsuite = for helloworld :-) >>>=20 --57815172a8dd486f85d10da80ecb3a5b Content-Type: text/html;charset=utf-8 Content-Transfer-Encoding: quoted-printable
here's what wou= ld be cool, a new token called "tuple_order" or something
=

INSERT INTO table (a, b, c) VALUES ((1, 2,= 3), (4, 5, 6), ...) RETURNING table.id, inserted.tuple_order
<= div>
tuple_order would be incrementing values 1, 2, 3, 4, = 5, ... which correlate the each row delivered by RETURNING to each entry= in the VALUES clause, in the order they were stated in that VALUES clau= se, that is entry (1, 2, 3) would be tuple_order 1, entry (4, 5, 6) woul= d be tuple order 2, etc.

That token would s= olve the entire problem in all cases.     The 1800 l= ine changeset I've been working on all week would go away (if every data= base had this) and the change would be reduced to a few dozen lines.
=


On Wed, Apr 12, 2023, at 11:05 = PM, John Howroyd wrote:
To clarify, the work around (for SQLAlchemy) do= es not require the RETURNING values to be ordered; they will be reordere= d code side.  Longer term matching the order up would be an added b= onus!

On Thu, 13 Apr 2023 at 04:02, John Howroyd = <jdhowroyd@googlemail.com= > wrote:
The ideal solution would = be that the RETURNING values are in the same order as the declared rows = in the table literal "FROM (VALUES (10, 11, 1), (12, 13, 2)) AS sel(p1, p2, num)".  But before any of= you jump down my throat about about sets and a bar to parallel inserts,= I think it's a small matter of some internal smoke and mirrors: simply = marshal the RETURNING results through and in memory/temporary table main= taining the order from the SELECT and return these to the client upon co= mmit.  At this moment, I believe the folk at SQLAlchemy are looking= for a work around that can work for most use cases with either an ident= ity column or a fake "sentinel" column for matching back to the ORM obje= cts.  There may be an upper limit of 1000 to the number rows in a s= ingle insert (this arises in  MS SQL Server, who absolutely do not = guarantee the order of their OUTPUT clause -- it's tied to the physical = row insert rather than marshalled).

My feel= ing is that PostgreSQL may already do this.  See the commit:
The test cases in the above commit use:
= SELECT nextval('sequence'), datavals FROM ... ORDER BY something;
type syntax.  And I presume that's exactly what's happening= internally when there's a serial identity column (via query rewrite rul= es or another mechanism).

So really, that j= ust needs confirming.  What happens with triggers?  Presumably= the same as long as query rewrite rules are used, but this is presumabl= y getting deeper into the code for actual inserts after the initial sele= ct.  The jump to the output being ordered, is just a guess but ther= e's some bits and pieces that seem to suggest that there may indeed be a= marshalling process going on in the background (whether or not that is = linked to the original order is another matter).

I have set up a PostgreSQL server to test if I can break this hypo= thesis and see what query explains can allude to.  Does anyone have= a test case where the order is not preserved?

<= div>Might I also point out that if the implementation of parallel insert= s does create a bar then doing so may end up with the programmatic inter= faces (such as SQLAlchemy) not being able to use that feature (possibly = reverting back to single inserts).  Ur, so what would be the proble= m being solved with parallel inserts?

On Th= u, 13 Apr 2023 at 02:27, Mike Bayer <mike_mp@zzzcomputing.com> wrote:
We do something different for uuids.  These are usually= created client side anyway or are very easy to create client side if no= t and then we also have a separate sentinel column option.

Overall the whole thing is an optimization that can b= e turned off for uncommon cases.   We just can't go back to ha= ving insert of 1000 rows be 3-5x slower for all tables w server generate= d primary keys which is what statement at a time gets us.  Surrogat= e integer Pks w sequence or identity is the big case.
On Wed, Apr 12, 2023, at 8:58 PM, Thorsten Glaser wrote:
> On Wed, 12 Apr 2023, Mike Bayer wrote:
= >
>>ascending values for "mytable_id_seq" will c= orrespond to the ascending
>>values for "num".
<= /div>
>
> But, again, what if it=E2=80=99s uui= d or something instead of a sequence?
>
= > bye,
> //mirabilos
> --
> 15:41=E2=8E=9C<Lo-lan-do:#fusionforge> Somebody wri= te a testsuite for helloworld :-)

<= /div>

--57815172a8dd486f85d10da80ecb3a5b--