Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3fvm-0053Qi-Ay for pgsql-general@arkaria.postgresql.org; Thu, 16 Nov 2023 17:13:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r3fvk-007p08-KL for pgsql-general@arkaria.postgresql.org; Thu, 16 Nov 2023 17:13:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3fvi-007ozx-R1 for pgsql-general@lists.postgresql.org; Thu, 16 Nov 2023 17:13:28 +0000 Received: from wout5-smtp.messagingengine.com ([64.147.123.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3fve-006lpD-UQ for pgsql-general@lists.postgresql.org; Thu, 16 Nov 2023 17:13:26 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.west.internal (Postfix) with ESMTP id 1009132008C0; Thu, 16 Nov 2023 12:13:18 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute6.internal (MEProxy); Thu, 16 Nov 2023 12:13:19 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1700154798; x= 1700241198; bh=DAP1y21d0qV1woxoRPGXfVeFmkYtKQWJOyaXWOUv++4=; b=q b4vqkG59GGhebPgModugDJ/BqsHkEbd/fWfyT6bOtgLmuQViiJFm1Ra3i7wzWXT/ wgy95iBkfxGhOupuDMg9Z8zYJ2MP2OmLhLkCHNJ/EYBOgNPpE6LY0k5dW5bZZDO4 w4QnoF112hzgORLz+YUuvzcnePvZ3AzrIwdQ1D03H1vU8WvNr0Azt8ta4onmAUaP U+J8pbKuNSI979buc21MGG064sr8MIPRvMPtp6H/HTSCm8c0mTYKF/sBYz9sAWG3 S1NlH6YTdMtAk+q0ITff8IMCFCdOcMp+g4e0e4wPqr/snsAMaizjWSghgSv67Bdx h5jfCyWpoPQ27mA/r4waQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrudefkedgleehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepfffhvfevuffkgggtugfgjgesthekredttddtjeenucfhrhhomheptehlvhgr rhhoucfjvghrrhgvrhgruceorghlvhhhvghrrhgvsegrlhhvhhdrnhhoqdhiphdrohhrgh eqnecuggftrfgrthhtvghrnhepvdektdffudfftdffffehfffhjeejhffgieeuueekjeek fffgudffhfduffffueevnecuffhomhgrihhnpegvnhhtvghrphhrihhsvggusgdrtghomh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrlhhv hhgvrhhrvgesrghlvhhhrdhnohdqihhprdhorhhg X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 16 Nov 2023 12:13:17 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=alvh.no-ip.org; s=schmee; t=1700154794; bh=owGtwgvym0d5nXYIuTdGaTvF35HDBDhTOmW7NKBXSN4=; h=Date:From:To:Cc:Subject:In-Reply-To:From; b=GUim6NgbC/pdw0RKs6FCVN5H1AbkuWXHSabMhBXLVFdfmDuhNmxNqMJ2bHhPNG5nW uSdqBFf/8Ab6BwyztFCNygKaamWTHO0Gkx8VLg2TDpjkzv7ouS5gLY9rGjbMYR7Gwp Kfs7DxDbNMPVF0o7Uv3uhEVf/px0uIFC5vEhJMKJqEXL1cfiP9qABQeCaWCV+AjiAb 5D3hukgMk00mVIQHd3/7r8Fj3vUWNbF13ROUb0CW8qu7LDXeQWzlNpv6GGZK8DfoLm V4NHHMxl1tt4rXfl1+oBkBFLXMiPB4+ghU4NE6GTe/uqMeTVsxUyBYeoonRtshCbgS IP1Ogr8cRr4Gg== Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 91B1FDE; Thu, 16 Nov 2023 18:13:14 +0100 (CET) Date: Thu, 16 Nov 2023 18:13:14 +0100 From: Alvaro Herrera To: Nick DeCoursin Cc: pgsql-general@lists.postgresql.org Subject: Re: New addition to the merge sql standard Message-ID: <202311161713.n56pwdbsmaln@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2023-Nov-16, Nick DeCoursin wrote: > In my opinion, it would be better for merge to offer the functionality to > simply ignore the rows that cause unique violation exceptions instead of > tanking the whole query. "ignore" may not be what you want, though. Perhaps the fact that insert (coming from the NOT MATCHED clause) fails (== conflicts with a tuple concurrently inserted in an unique or exclusion constraint) should transform the row operation into a MATCHED case, so it'd fire the other clauses in the overall MERGE operation. Then you could add a WHEN MATCHED DO NOTHING case which does the ignoring that you want; or just let them be handled by WHEN MATCHED UPDATE or whatever. But you may need some way to distinguish rows that appeared concurrently from rows that were there all along. In regards to the SQL standard, I hope what you're saying is merely not documented by them. If it indeed isn't, it may be possible to get them to accept some new behavior, and then I'm sure we'd consider implementing it. If your suggestion goes against what they already have, I'm afraid you'd be doomed. So the next question is, how do other implementations handle this case you're talking about? SQL Server, DB2 and Oracle being the relevant ones. Assuming the idea is good and there are no conflicts, then maybe it's just lack of round tuits. Happen to have some? I vaguely recall thinking about this, and noticing that implementing something of this sort would require messing around with the ExecInsert interface. It'd probably require splitting it in pieces, similar to how ExecUpdate was split. There are some comments in the code about possible "live-locks" where merge would be eternally confused between inserting a new row which it then wants to delete; or something like that. For sure we would need to understand the concurrent behavior of this new feature very clearly. An interesting point is that our inserts *wait* to see whether the concurrent insertion commits or aborts, when a unique constraint is involved. I'm not sure you want to have MERGE blocking on concurrent inserts. This is all assuming READ COMMITTED semantics; on REPEATABLE READ or higher, I think you're just screwed, because of course MERGE is not going to get a snapshot that sees the rows inserted by transactions that started after. You'd need to explore all this very carefully. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/