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

From: Sergey Burladyan <eshkinkot(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: can't delete record from second table in rules of view with join select
Date: 2008-03-25 11:13:07
Message-ID: 200803251413.07846.eshkinkot@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

---

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2008-03-25 11:55:00 Re: BUG: PG do not use index
Previous Message Reginald Drake 2008-03-25 11:01:44 BUG #4057: SUM returns NULL when given no rows