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
---
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 |