Обсуждение: can't delete record from second table in rules of view with join select

Hello, all

=46rom 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 =3D old.o1_id;
       delete from o2 where id =3D 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 =3D old.o1_id;
);
create rule v_del2 as on delete to v do instead (
       delete from o2 where id =3D 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, o=
2.val as o2_val from o1, o2 where o1.id=3Do2.id;

create rule v_del as on delete to v do instead (
       delete from o1 where id =3D old.o1_id;
       delete from o2 where id =3D old.o2_id;
);
-- create rule v_del1 as on delete to v do instead (
--        delete from o1 where id =3D old.o1_id;
-- );
-- create rule v_del2 as on delete to v do instead (
--        delete from o2 where id =3D 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 =3D 1;
explain analyze delete from v where o2_id =3D 2;

select * from v;

select * from o1;
select * from o2;

rollback;

=3D=3D=3D output =3D=3D=3D
                                         version=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
-------------
 PostgreSQL 8.3.0 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Deb=
ian 4.2.3-1)

--- select * from v;
  o1_id | o1_val | o2_id | o2_val=20
-------+--------+-------+--------
     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 =3D 1;
seb=3D> DELETE 0
--- explain analyze delete from v where o2_id =3D 2;
                                                   QUERY PLAN=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
------------------------------------
 Nested Loop  (cost=3D50.76..81.18 rows=3D216 width=3D6) (actual time=3D0.0=
40..0.050 rows=3D1 loops=3D1)
   ->  Nested Loop  (cost=3D25.38..51.48 rows=3D36 width=3D14) (actual time=
=3D0.030..0.034 rows=3D1 loops=3D1)
         ->  Seq Scan on o1  (cost=3D0.00..25.38 rows=3D6 width=3D10) (actu=
al time=3D0.014..0.015 rows=3D1 loops=3D1)
               Filter: (id =3D 2)
         ->  Materialize  (cost=3D25.38..25.44 rows=3D6 width=3D4) (actual =
time=3D0.012..0.014 rows=3D1 loops=3D1)
               ->  Seq Scan on o2  (cost=3D0.00..25.38 rows=3D6 width=3D4) =
(actual time=3D0.007..0.008 rows=3D1 loops=3D1)
                     Filter: (id =3D 2)
   ->  Materialize  (cost=3D25.38..25.44 rows=3D6 width=3D4) (actual time=
=3D0.007..0.010 rows=3D1 loops=3D1)
         ->  Seq Scan on o1  (cost=3D0.00..25.38 rows=3D6 width=3D4) (actua=
l time=3D0.005..0.008 rows=3D1 loops=3D1)
               Filter: (id =3D 2)
 Total runtime: 0.135 ms
=20
 Nested Loop  (cost=3D50.76..81.18 rows=3D216 width=3D6) (actual time=3D0.0=
34..0.034 rows=3D0 loops=3D1)
   ->  Nested Loop  (cost=3D25.38..51.48 rows=3D36 width=3D10) (actual time=
=3D0.019..0.023 rows=3D1 loops=3D1)
         ->  Seq Scan on o2  (cost=3D0.00..25.38 rows=3D6 width=3D10) (actu=
al time=3D0.008..0.009 rows=3D1 loops=3D1)
               Filter: (id =3D 2)
         ->  Materialize  (cost=3D25.38..25.44 rows=3D6 width=3D4) (actual =
time=3D0.009..0.011 rows=3D1 loops=3D1)
               ->  Seq Scan on o2  (cost=3D0.00..25.38 rows=3D6 width=3D4) =
(actual time=3D0.006..0.007 rows=3D1 loops=3D1)
                     Filter: (id =3D 2)
   ->  Materialize  (cost=3D25.38..25.44 rows=3D6 width=3D4) (actual time=
=3D0.008..0.008 rows=3D0 loops=3D1)
         ->  Seq Scan on o1  (cost=3D0.00..25.38 rows=3D6 width=3D4) (actua=
l time=3D0.007..0.007 rows=3D0 loops=3D1)
               Filter: (id =3D 2)
 Total runtime: 0.083 ms
(23 rows)

--- select * from v;
  o1_id | o1_val | o2_id | o2_val=20
-------+--------+-------+--------
     3 | o1 3   |     3 | o2 3
(1 =D0=B7=D0=B0=D0=BF=D0=B8=D1=81=D1=8C)

--- select * from o1; (all correctly deleted)
  id | val=20=20
----+------
  3 | o1 3
(1 =D0=B7=D0=B0=D0=BF=D0=B8=D1=81=D1=8C)

--- select * from o2; (no one deleted)
  id | val=20=20
----+------
  1 | o2 1
  2 | o2 2
  3 | o2 3
(3 rows)

seb=3D> ROLLBACK

---

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 ? %)

---

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