pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (2+ messages)  latest in thread

Message-ID: <1579081610.235f840.27092@debian3>
Permalink:  ../1579081610.235f840.27092@debian3/
Also on:    postgresql.org/message-id/1579081610.235f840.27092@debian3

 · 

reply

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