From: | andreas(at)elbrief(dot)de |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | create rule with multiple commands |
Date: | 2020-01-15 09:46:50 |
Message-ID: | 1579081610.235f840.27092@debian3 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Mahendra Singh Thalor | 2020-01-15 10:02:57 | Re: BUG #16205: background worker "logical replication worker" (PID 25218) was terminated by signal 11: Segmentation |
Previous Message | PG Bug reporting form | 2020-01-15 08:35:08 | BUG #16206: Documentation for PGDATABASE libpq env var is misleading |