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 1pmy58-0004j7-Lc for pgsql-general@arkaria.postgresql.org; Thu, 13 Apr 2023 14:37:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmy57-000731-H7 for pgsql-general@arkaria.postgresql.org; Thu, 13 Apr 2023 14:37:49 +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 1pmy57-00072s-6E for pgsql-general@lists.postgresql.org; Thu, 13 Apr 2023 14:37:49 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmy50-0005g5-NC for pgsql-general@lists.postgresql.org; Thu, 13 Apr 2023 14:37:48 +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 33DEbePn1332833; Thu, 13 Apr 2023 10:37:40 -0400 From: Tom Lane To: "David G. Johnston" cc: Ciprian Craciun , "pgsql-general@lists.postgresql.org" Subject: Re: Unexpected behavior when combining `generated always` columns and update rules In-reply-to: References: Comments: In-reply-to "David G. Johnston" message dated "Thu, 13 Apr 2023 07:32:08 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1332831.1681396660.1@sss.pgh.pa.us> Date: Thu, 13 Apr 2023 10:37:40 -0400 Message-ID: <1332832.1681396660@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "David G. Johnston" writes: > On Thursday, April 13, 2023, Ciprian Craciun > wrote: >> create or replace rule propagate_x_and_d as on update to x do also >> update y set x = new.x, d = new.d where y.x = old.x; >> >> As seen above, although the rule correctly propagates the change to >> the `x` column, it fails to use the new value for the `d` column, but >> instead uses the previous one. > ALSO rules behave like before triggers, not after triggers. The original > command is appended to the end of the list of commands, not the start. It depends actually ... per [1], For ON INSERT rules, the original query (if not suppressed by INSTEAD) is done before any actions added by rules. This allows the actions to see the inserted row(s). But for ON UPDATE and ON DELETE rules, the original query is done after the actions added by rules. This ensures that the actions can see the to-be-updated or to-be-deleted rows; otherwise, the actions might do nothing because they find no rows matching their qualifications. regards, tom lane [1] https://www.postgresql.org/docs/current/rules-update.html