create rule with multiple commands - Mailing list pgsql-bugs

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