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 1ppA0Y-00066s-C1 for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 15:46:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppA0X-0007nf-9G for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 15:46:09 +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 1ppA0W-0007mw-UU for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 15:46:08 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ppA0U-000i6c-K3 for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 15:46:07 +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 33JFk4f72983383; Wed, 19 Apr 2023 11:46:04 -0400 From: Tom Lane To: Alex Bolenok cc: pgsql-general@lists.postgresql.org Subject: Re: Joins of data-modifying CTE with the target table In-reply-to: References: Comments: In-reply-to Alex Bolenok message dated "Wed, 19 Apr 2023 11:37:37 -0400" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <2983381.1681919164.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Wed, 19 Apr 2023 11:46:04 -0400 Message-ID: <2983382.1681919164@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Alex Bolenok writes: > I get why it's not working (because the statement is not allowed to see the > tuples with its own cmin), but I was wondering if it was worth it at least > to spell it out explicitly in the documentation. What's not explicit about this? The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables. regards, tom lane