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 1pmLDW-0001XV-Fx for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 21:07:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmLDU-0008Fs-Vc for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 21:07:52 +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 1pmLDT-0008Fh-Bc for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 21:07:52 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pmLDO-002J6T-9h for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 21:07:50 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 292995C0095; Tue, 11 Apr 2023 17:07:43 -0400 (EDT) Received: from imap44 ([10.202.2.94]) by compute2.internal (MEProxy); Tue, 11 Apr 2023 17:07:43 -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= 1681247263; x=1681333663; bh=sWdwZF47YPEESTDPzKi3ONLLrHr30MaKbfx Hi/MsVl0=; b=a15nfA5Eu5IrQxEdYWNoR5YFRygqgZES1ATV1wkBEMhDQebTLcZ uSEWWcp1QonjsoA+35ydeovgYNQh11DW1UR1QGkqOlHeA3JCgxYRKt1DkBRWGyn/ q1AcJDqLILF4Kt1tMXbEuOVzhTjyfuF7s4CY9nlbGDpVTDdbJiK7FGgHfLYu/whs f4u8a1GELyEezLV0/ndB4vz0PQkPL89uMWSpduFISmp9SJ+mDYWQCO6aqhtCstWA PBTLadguLcGYF+TXdund2rrXsIOsc1faNtxO3DUzpgttzuKWNH/oxXVYXjH+yG5k nmmT/T1+pez45EtCFQpuW0/c8bY7JNHIdBw== 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=1681247263; x=1681333663; bh=sWdwZF47YPEES TDPzKi3ONLLrHr30MaKbfxHi/MsVl0=; b=WPbtwotfU6FCddf3jEm0R7PFrtbKf Sp9ssn8Wjs1IetOjpzZNPJKMpXcwoaKdtHwSLMwZFJJUUalFNElkFFIxjzp3V1pw jkr1LLReYnat8FgfTS29oxs60Y9pbgLU8MC7TIMx9kX7ZObGFtHahTGQrCW/nfKa blC+EfAA2qA/sYMhMEPde8UGmQc/HngClIBw1uQXbB4CT6bzkIuO00tVpnrlXuOe F0aYgMR9R+djS6+xeoL57OYhmJJySAmJREmgWTbCmEgudSYuB793+u9BgzJNKvqt cG2BDe6o0Tfds0/FZLjUGeUGn+qgdTDxHtzTCBCJXwSIF39vNqJkzrqIQ== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvdekgedgudehkecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepofgfggfkjghffffhvfevufgtse grtderreerredtnecuhfhrohhmpedfofhikhgvuceurgihvghrfdcuoehmihhkvggpmhhp seiiiiiitghomhhpuhhtihhnghdrtghomheqnecuggftrfgrthhtvghrnhepvdfgleffgf dtkeekfeeiteeguedtkeegtdduudeltefftdejhedvudeltdeutdeunecuffhomhgrihhn pehpohhsthhgrhgvshhqlhdrohhrghenucevlhhushhtvghrufhiiigvpedtnecurfgrrh grmhepmhgrihhlfhhrohhmpehmihhkvggpmhhpseiiiiiitghomhhpuhhtihhnghdrtgho mh X-ME-Proxy: Feedback-ID: i1c194411:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id B3A0336A0073; Tue, 11 Apr 2023 17:07:42 -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: <779f312a-4c10-437e-bfc9-3f4c8eaea18b@app.fastmail.com> In-Reply-To: <510048.1681244547@sss.pgh.pa.us> References: <510048.1681244547@sss.pgh.pa.us> Date: Tue, 11 Apr 2023 17:07:15 -0400 From: "Mike Bayer" To: "Tom Lane" , Federico Cc: pgsql-general@lists.postgresql.org Subject: Re: Guidance on INSERT RETURNING order Content-Type: multipart/alternative; boundary=951fae6fe5474f9ba4514f96ec885822 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --951fae6fe5474f9ba4514f96ec885822 Content-Type: text/plain On Tue, Apr 11, 2023, at 4:22 PM, Tom Lane wrote: > 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. if I can state this without having RETURNING getting in the way, because we know RETURNING is not ordered. Start with this table: CREATE TABLE mytable ( id SERIAL PRIMARY KEY, a INT, b INT ) Then insert two rows, where id SERIAL fires implicitly, assume the next value the sequence will give us is N1, and then the value after that is N2. It doesn't matter what N1 and N2 are (don't need to be consecutive) but we want N2 > N1, that is, increasing. INSERT INTO mytable (a, b) SELECT p1, p2 FROM (VALUES (10, 11, 1), (12, 13, 2)) AS sel(p1, p2, num) ORDER BY num Then SELECT with ORDER BY: SELECT id, a, b FROM mytable ORDER BY id We want the results to be: (N1, 10, 11) (N2, 12, 13) and we dont want them to *ever* be: (N1, 12, 13) (N2, 10, 11) that is, we want the SERIAL column (or an IDENTITY also) to be lined up with the VALUES.