From: andreas@elbrief.de
To: pgsql-bugs@lists.postgresql.org
Subject: create rule with multiple commands
Date: Wed, 15 Jan 2020 10:46:50 +0100
Message-ID: <1579081610.235f840.27092@debian3> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: andreas@elbrief.de
Subject: Re: create rule with multiple commands
In-Reply-To: <1579081610.235f840.27092@debian3>
References: <1579081610.235f840.27092@debian3>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox