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 1pmKVd-0007kn-Fw for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 20:22:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmKVc-0006bU-BV for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 20:22:32 +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 1pmKVc-0006bK-0w for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 20:22:32 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pmKVZ-002IiS-DE for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 20:22:31 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 33BKMR88510049; Tue, 11 Apr 2023 16:22:27 -0400 From: Tom Lane To: Federico cc: pgsql-general@lists.postgresql.org, Mike Bayer Subject: Re: Guidance on INSERT RETURNING order In-reply-to: References: Comments: In-reply-to Federico message dated "Tue, 11 Apr 2023 21:47:08 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <510047.1681244547.1@sss.pgh.pa.us> Date: Tue, 11 Apr 2023 16:22:27 -0400 Message-ID: <510048.1681244547@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Federico 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. I think if you want to use RETURNING with multi-row inserts, the thing to do is more like INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING data, id and then explicitly match up the returned "data" values rather than presuming they appear in the same order you wrote them in in VALUES. Admittedly this might be problematic if some of the VALUES rows are identical, but how much should you care? regards, tom lane