Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1irkmt-0004Zs-JE for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Jan 2020 15:40:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1irkmr-0001cx-Iu for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Jan 2020 15:40:53 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1irkmr-0001cq-6M for pgsql-bugs@lists.postgresql.org; Wed, 15 Jan 2020 15:40:53 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1irkmk-0005yk-Ck for pgsql-bugs@lists.postgresql.org; Wed, 15 Jan 2020 15:40:51 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 00FFeVbX004634; Wed, 15 Jan 2020 10:40:32 -0500 From: Tom Lane To: andreas@elbrief.de cc: pgsql-bugs@lists.postgresql.org Subject: Re: create rule with multiple commands In-reply-to: <1579081610.235f840.27092@debian3> References: <1579081610.235f840.27092@debian3> Comments: In-reply-to andreas@elbrief.de message dated "Wed, 15 Jan 2020 10:46:50 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <4632.1579102831.1@sss.pgh.pa.us> Date: Wed, 15 Jan 2020 10:40:31 -0500 Message-ID: <4633.1579102831@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk andreas@elbrief.de writes: > create rule a_update as on update to a do instead ( > | insert into _a (value) values (new.value); > | insert into deleted (tid) values (old.id) > ); > [ vs ] > create rule a_update as on update to a do instead ( > | insert into deleted (tid) values (old.id); > | insert into _a (value) values (new.value) > ); > i expected the same result in both begin ... rollback sequences, > but i get different results. > i expect 2 rows in _a and one row in deleted, but both results are not that. Neither of these will do what you want, because in an ON UPDATE rule "new" and "old" represent references to the output of the view; the only difference between them is that the desired values are substituted into the targetlist of "new". So in either case, the first INSERT action causes the output of the view to change, and then the second INSERT action computes what to insert based on the new view output and does the wrong thing. See https://www.postgresql.org/docs/current/rules-update.html Using EXPLAIN on the view UPDATE command is another way to get some insight. The reason why using rules to update views is semi-deprecated is exactly that it's really hard to get the mechanism to do "simple" things like what you'd like to do here. I'd suggest using a trigger instead. If you want to work with rules anyway, the first thing that you have to remember is that unlike a trigger, "new" and "old" are not simple composite values describing single rows --- they are references to the whole view output. So an absolutely essential part of any rule is some WHERE clauses to constrain the action to just the rows you want to modify. There are examples in the chapter I linked to above. regards, tom lane