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 1irfou-0008HD-RO for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Jan 2020 10:22:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1irfot-0004zg-J2 for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Jan 2020 10:22:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1irfGQ-0004Kq-19 for pgsql-bugs@lists.postgresql.org; Wed, 15 Jan 2020 09:47:02 +0000 Received: from s19187171.onlinehome-server.info ([2001:8d8:993:2900::52:cc1d]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1irfGI-00082U-2S for pgsql-bugs@lists.postgresql.org; Wed, 15 Jan 2020 09:47:01 +0000 Received: from p5b016e9a.dip0.t-ipconnect.de ([91.1.110.154]:51886 helo=debian3.lan) by s19187171.onlinehome-server.info with esmtpsa (TLS1.2:DHE_RSA_AES_128_CBC_SHA1:128) (Exim 4.84_2) (envelope-from ) id 1irfGE-0000n8-Ju for pgsql-bugs@lists.postgresql.org; Wed, 15 Jan 2020 10:46:51 +0100 Received: from www-data by debian3.lan with local (Exim 4.80) (envelope-from ) id 1irfGE-0007Qe-6z for pgsql-bugs@lists.postgresql.org; Wed, 15 Jan 2020 10:46:50 +0100 Content-Type: text/plain; charset="iso-8859-1" Content-Disposition: inline Content-Transfer-Encoding: binary MIME-Version: 1.0 X-Mailer: MIME-tools 5.503 (Entity 5.503) Date: Wed, 15 Jan 2020 10:46:50 +0100 Message-ID: <1579081610.235f840.27092@debian3> To: pgsql-bugs@lists.postgresql.org From: andreas@elbrief.de X-SA-Exim-Connect-IP: 91.1.110.154 X-SA-Exim-Rcpt-To: pgsql-bugs@lists.postgresql.org X-SA-Exim-Mail-From: andreas@elbrief.de X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on s19187171.onlinehome-server.info X-Spam-Level: X-Spam-Status: No, score=-1.9 required=2.5 tests=ALL_TRUSTED,BAYES_00 autolearn=ham autolearn_force=no version=3.4.2 X-Spam-s19187171: ALL_TRUSTED=-1,BAYES_00=-0.9 Subject: create rule with multiple commands X-SA-Exim-Version: 4.2.1 (built Mon, 26 Dec 2011 16:24:06 +0000) X-SA-Exim-Scanned: Yes (on s19187171.onlinehome-server.info) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk select version(); begin; create table _a (id serial primary key, value int not null); create table deleted (id serial primary key, tid int not null); create view a as select a.id, a.value from _a a left join deleted s on s.tid = a.id where s.id is null; create rule a_insert as on insert to a do instead insert into _a (value) values (new.value); 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) ); insert into a (value) values (1); update a set value = 2; select * from _a; select * from deleted; rollback; begin; create table _a (id serial primary key, value int not null); create table deleted (id serial primary key, tid int not null); create view a as select a.id, a.value from _a a left join deleted s on s.tid = a.id where s.id is null; create rule a_insert as on insert to a do instead insert into _a (value) values (new.value); 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) ); insert into a (value) values (1); update a set value = 2; select * from _a; select * from deleted; rollback; ############### version ---------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 12.1 (Debian 12.1-1.pgdg90 1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18 deb9u1) 6.3.0 20170516, 64-bit (1 Zeile) BEGIN CREATE TABLE CREATE TABLE CREATE VIEW CREATE RULE CREATE RULE INSERT 0 1 UPDATE 0 id | value ---- ------- 1 | 1 2 | 2 (2 Zeilen) id | tid ---- ----- 1 | 1 2 | 2 (2 Zeilen) ROLLBACK BEGIN CREATE TABLE CREATE TABLE CREATE VIEW CREATE RULE CREATE RULE INSERT 0 1 UPDATE 0 id | value ---- ------- 1 | 1 (1 Zeile) id | tid ---- ----- 1 | 1 (1 Zeile) ROLLBACK ############### 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. best regards, Andreas Sakowski