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 1ppAtS-0000gA-0N for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 16:42:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppAtQ-0006Cx-82 for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 16:42:52 +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 1ppAtO-0006Cn-PP for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 16:42:51 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ppAtL-000ig1-Lg for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 16:42:49 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id 72E885C012B; Wed, 19 Apr 2023 12:42:46 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute1.internal (MEProxy); Wed, 19 Apr 2023 12:42:46 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding: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=fm2; t= 1681922566; x=1682008966; bh=QrU1sz1LR9OqFzfajB8fBtHshgzxH/3XoLS 3CjJbng8=; b=bKaOskF/w/uC7zmFUp+lvSfwXFHd1Agng0FstdUde2oReY6cqmd eJxyDOuDsEWWMCCr+slt0pGwJRFyvt12bjgSbvBUtdadP6OUOfhHOB7XNDtU9asr QfmJzA6lvOs7vWd+J6m9uhxwIEDI57pIQNVE6QstKW9VMMjk07nbqfPnPUh69GB6 wW7h0fKTQ0aymNIs1i8uWoVzL0CIltW9MgwMDsxeEE+VZQ1eEiuaxJWByLvszZuQ IYlJF8RXzvSbZC87PUnA/MG6rb+nzOZCzMsv6mb7viV874Z24XXHtSCnl7m2Gp91 2nGWEqsUWO1eH4YIJJjSJIFpcUYtwk/3s4g== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=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: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=1681922566; x= 1682008966; bh=QrU1sz1LR9OqFzfajB8fBtHshgzxH/3XoLS3CjJbng8=; b=W 1ZoU9j3+pry1OPb5TYPWeUYyp8KDVO+JeSy6+7CN2pfi+4iFhxZq7a+FKVMeW7vu Plo8rGzEjkpzs8XTHe3UtUZ+SwAzDiKKA6+8XaLbzHrGsMtD+mvJrRpmp66YOp14 frMrhSNgmMiC0huQH3eRBuOneQTCdw/dZ/F/LH01eYS4ma3HLvBI+hDJqh/A8RaW RSZViW0v0gdJ8MY6qjcQxnJ/O7CLYCWUC6TwiGoQaWgklavs/O/w0iRLK4ByPIOI 1L8dkyyj1FXwteRSN7gxlZ0MkJx4kXbKbuetzn4lnw5e2xjt4ACmpKpxjfU4isbs wICWr8MdEVozNMqZpbLgg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrfedttddguddthecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepkfffgggfuffvfhfhjggtgfesth ekredttdefjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpedugfeule egveffhfegleejhefhtedtgeeileeggfehfefffeevgeejtdeutefftdenucevlhhushht vghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrg hvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 19 Apr 2023 12:42:45 -0400 (EDT) Message-ID: Date: Wed, 19 Apr 2023 09:42:45 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: Joins of data-modifying CTE with the target table Content-Language: en-US To: Alex Bolenok , pgsql-general@lists.postgresql.org References: <2983382.1681919164@sss.pgh.pa.us> From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 4/19/23 09:09, Alex Bolenok wrote: > To reiterate, I (I personally) get why it's not working, but the > documentation is something that is intended for people who don't. > > Technically, anyone can deduce it from reading the documentation > thoroughly enough, but do we need the documentation to be as terse > as possible? Not so much terse as possible as not as verbose as possible. The difference being that documenting every possible usage would turn the page into an eye glazing experience. At that point folks would really just move on. The documentation expectations(as I see it) are that: 1) People can work from basic principles to specific usage. 2) They also actually test/verify what they run. What you are after is more on the lines of a separate enhanced tutorial. That has been discussed on this list and elsewhere. So far it has not progressed beyond discussion. > > To answer your question, by "explicitly" I mean mentioning this very > pattern, where you join the returned id with the target table. People > often try to use this pattern for queries like "add an item to the order > in a CTE, select the order total in the main query", and often don't > notice that the order total doesn't include the new item until it hits > production. > -- Adrian Klaver adrian.klaver@aklaver.com