can't delete record from second table in rules of view with join select

Started by Sergey Burladyanabout 18 years ago3 messagesbugs
Jump to latest
#1Sergey Burladyan
eshkinkot@gmail.com

Hello, all

From sql.ru forum:

not important, is it one rule like:
create rule v_del as on delete to v do instead (
delete from o1 where id = old.o1_id;
delete from o2 where id = old.o2_id;
);

or split into two rule like:
create rule v_del1 as on delete to v do instead (
delete from o1 where id = old.o1_id;
);
create rule v_del2 as on delete to v do instead (
delete from o2 where id = old.o2_id;
);

delete from second table (o2) do not delete anything.

test case:
begin;

select version();

create table o1 (id int, val text);
create table o2 (id int, val text);
create view v as select o1.id as o1_id, o1.val as o1_val, o2.id as o2_id, o2.val as o2_val from o1, o2 where o1.id=o2.id;

create rule v_del as on delete to v do instead (
delete from o1 where id = old.o1_id;
delete from o2 where id = old.o2_id;
);
-- create rule v_del1 as on delete to v do instead (
-- delete from o1 where id = old.o1_id;
-- );
-- create rule v_del2 as on delete to v do instead (
-- delete from o2 where id = old.o2_id;
-- );

insert into o1 values (1, 'o1 1'), (2, 'o1 2'), (3, 'o1 3');
insert into o2 values (1, 'o2 1'), (2, 'o2 2'), (3, 'o2 3');

select * from v;

delete from v where o1_id = 1;
explain analyze delete from v where o2_id = 2;

select * from v;

select * from o1;
select * from o2;

rollback;

=== output ===
version
----------------------------------------------------------------------------------------
PostgreSQL 8.3.0 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Debian 4.2.3-1)

--- select * from v;
  o1_id | o1_val | o2_id | o2_val 
-------+--------+-------+--------
     1 | o1 1   |     1 | o2 1
     2 | o1 2   |     2 | o2 2
     3 | o1 3   |     3 | o2 3
(3 rows)
--- delete from v where o1_id = 1;
seb=> DELETE 0
--- explain analyze delete from v where o2_id = 2;
                                                   QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=50.76..81.18 rows=216 width=6) (actual time=0.040..0.050 rows=1 loops=1)
   ->  Nested Loop  (cost=25.38..51.48 rows=36 width=14) (actual time=0.030..0.034 rows=1 loops=1)
         ->  Seq Scan on o1  (cost=0.00..25.38 rows=6 width=10) (actual time=0.014..0.015 rows=1 loops=1)
               Filter: (id = 2)
         ->  Materialize  (cost=25.38..25.44 rows=6 width=4) (actual time=0.012..0.014 rows=1 loops=1)
               ->  Seq Scan on o2  (cost=0.00..25.38 rows=6 width=4) (actual time=0.007..0.008 rows=1 loops=1)
                     Filter: (id = 2)
   ->  Materialize  (cost=25.38..25.44 rows=6 width=4) (actual time=0.007..0.010 rows=1 loops=1)
         ->  Seq Scan on o1  (cost=0.00..25.38 rows=6 width=4) (actual time=0.005..0.008 rows=1 loops=1)
               Filter: (id = 2)
 Total runtime: 0.135 ms

Nested Loop (cost=50.76..81.18 rows=216 width=6) (actual time=0.034..0.034 rows=0 loops=1)
-> Nested Loop (cost=25.38..51.48 rows=36 width=10) (actual time=0.019..0.023 rows=1 loops=1)
-> Seq Scan on o2 (cost=0.00..25.38 rows=6 width=10) (actual time=0.008..0.009 rows=1 loops=1)
Filter: (id = 2)
-> Materialize (cost=25.38..25.44 rows=6 width=4) (actual time=0.009..0.011 rows=1 loops=1)
-> Seq Scan on o2 (cost=0.00..25.38 rows=6 width=4) (actual time=0.006..0.007 rows=1 loops=1)
Filter: (id = 2)
-> Materialize (cost=25.38..25.44 rows=6 width=4) (actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on o1 (cost=0.00..25.38 rows=6 width=4) (actual time=0.007..0.007 rows=0 loops=1)
Filter: (id = 2)
Total runtime: 0.083 ms
(23 rows)

--- select * from v;
  o1_id | o1_val | o2_id | o2_val 
-------+--------+-------+--------
     3 | o1 3   |     3 | o2 3
(1 запись)
--- select * from o1; (all correctly deleted)
  id | val  
----+------
  3 | o1 3
(1 запись)
--- select * from o2; (no one deleted)
  id | val  
----+------
  1 | o2 1
  2 | o2 2
  3 | o2 3
(3 rows)

seb=> ROLLBACK

---

#2Sergey Burladyan
eshkinkot@gmail.com
In reply to: Sergey Burladyan (#1)
Re: can't delete record from second table in rules of view with join select

Hello, all

not important, is it one rule like:
create rule v_del as on delete to v do instead (
delete from o1 where id = old.o1_id;
delete from o2 where id = old.o2_id;
);

or split into two rule like:
create rule v_del1 as on delete to v do instead (
delete from o1 where id = old.o1_id;
);
create rule v_del2 as on delete to v do instead (
delete from o2 where id = old.o2_id;
);

Sorry, after thinking some time about this problem now i may be understand what going on there... %)

When first rule was exec - no OLD row anymore in "v" view, nothing will be joined and in second rule
OLD value is empty... so my question is changed to: is this expected behavior or a bug ? %)

---

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Sergey Burladyan (#2)
Re: can't delete record from second table in rules of view with join select

Sergey Burladyan <eshkinkot@gmail.com> writes:

Sorry, after thinking some time about this problem now i may be understand what going on there... %)

When first rule was exec - no OLD row anymore in "v" view, nothing will be joined and in second rule
OLD value is empty... so my question is changed to: is this expected behavior or a bug ? %)

Yup, it's expected.

regards, tom lane