Обсуждение: 'on delete' rule: bug or feature...

Hi, everybody!

I was wonderring if anyone could help me with this...
I have created two tables and a view that joins them together, then I add a rule, that is supposed to
delete an entry from both tables, when I am deleting it from the view.
That does not work for some reason - it only deletes the row from one of the tables, but not from the other one...
Looks like a bug to me... Or am I doing something wrong here?

Here is the SQL (I have removed the prompts, and commented out the responses, so that you can just cut and paste this
inpsql): 


-- First, create two tables, the view, and the 'on delete' rule:

create table x (xx int);
-- CREATE
create table y (yy int);
-- CREATE
create view xy as select * from x, y where xx=yy;
-- CREATE
create rule xy_delete as on delete to xy do instead
(
  delete from x where xx=old.xx;
  delete from y where yy=old.yy;
);
-- CREATE

-- Insert a row into each table:

insert into x values (1);
-- INSERT 812084785 1
insert into y values (1);
-- INSERT 812084786 1

-- Did it work?

select * from xy;
--  xx | yy
-- ----+----
--   1 |  1
-- (1 row)

-- Great! Now delete it:

delete from xy where xx=1;
-- DELETE 0
select * from xy;
-- (No rows)

-- Looks good so far - it's gone from the view

select * from x;
-- (No rows)

-- Gone from x too - still OK, BUT:

select * from y;
--  yy
-- ----
--   1
-- (1 row)

OOPS! How come it is still here???

Any ideas?

Your help will be greatly appreciated!

Thanks a lot!

Dima

Jan Wieck wrote:

>The problem is that the deletes in the rules expand to a join according
>to the view too. As soon as the first rule action has deleted the rows,
>the second action cannot find anything anymore. We would have to
>suppress the command counter increment between the rule actions to fix
>this, but that would break other scenarios where the effects of one
>action need to be visible in the next.
>
I suspected something like this... However, it still doesn';t make much
sense to me really - if the statement says
'delete from Y...',  WHY (and where???) does it actually expand to a join???

Or are you saying it tries to revaluate 'old.*' after every statement
(that's about the only place I could see it needing a join)? Is that the
case???
Wouldn't it be extremely slow, when you have many references to 'old' in
a rule for a complicated multitable view?
Why does it need to rerun the whole join every time?

Thanks!

Dima

>
>Dmitry Tkach wrote:
>
>>Hi, everybody!
>>
>>I was wonderring if anyone could help me with this...
>>I have created two tables and a view that joins them together, then I add a rule, that is supposed to
>>delete an entry from both tables, when I am deleting it from the view.
>>That does not work for some reason - it only deletes the row from one of the tables, but not from the other one...
>>Looks like a bug to me... Or am I doing something wrong here?
>>
>>Here is the SQL (I have removed the prompts, and commented out the responses, so that you can just cut and paste this
inpsql): 
>>
>>-- First, create two tables, the view, and the 'on delete' rule:
>>
>>create table x (xx int);
>>-- CREATE
>>create table y (yy int);
>>-- CREATE
>>create view xy as select * from x, y where xx=yy;
>>-- CREATE
>>create rule xy_delete as on delete to xy do instead
>>(
>>  delete from x where xx=old.xx;
>>  delete from y where yy=old.yy;
>>);
>>-- CREATE
>>
>>-- Insert a row into each table:
>>
>>insert into x values (1);
>>-- INSERT 812084785 1
>>insert into y values (1);
>>-- INSERT 812084786 1
>>
>>-- Did it work?
>>
>>select * from xy;
>>--  xx | yy
>>-- ----+----
>>--   1 |  1
>>-- (1 row)
>>
>>-- Great! Now delete it:
>>
>>delete from xy where xx=1;
>>-- DELETE 0
>>select * from xy;
>>-- (No rows)
>>
>>-- Looks good so far - it's gone from the view
>>
>>select * from x;
>>-- (No rows)
>>
>>-- Gone from x too - still OK, BUT:
>>
>>select * from y;
>>--  yy
>>-- ----
>>--   1
>>-- (1 row)
>>
>>OOPS! How come it is still here???
>>
>>Any ideas?
>>
>>Your help will be greatly appreciated!
>>
>>Thanks a lot!
>>
>>Dima
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo@postgresql.org so that your
>>message can get through to the mailing list cleanly
>>
>

The problem is that the deletes in the rules expand to a join according
to the view too. As soon as the first rule action has deleted the rows,
the second action cannot find anything anymore. We would have to
suppress the command counter increment between the rule actions to fix
this, but that would break other scenarios where the effects of one
action need to be visible in the next.


Jan

Dmitry Tkach wrote:
>
> Hi, everybody!
>
> I was wonderring if anyone could help me with this...
> I have created two tables and a view that joins them together, then I add a rule, that is supposed to
> delete an entry from both tables, when I am deleting it from the view.
> That does not work for some reason - it only deletes the row from one of the tables, but not from the other one...
> Looks like a bug to me... Or am I doing something wrong here?
>
> Here is the SQL (I have removed the prompts, and commented out the responses, so that you can just cut and paste this
inpsql): 
>
> -- First, create two tables, the view, and the 'on delete' rule:
>
> create table x (xx int);
> -- CREATE
> create table y (yy int);
> -- CREATE
> create view xy as select * from x, y where xx=yy;
> -- CREATE
> create rule xy_delete as on delete to xy do instead
> (
>   delete from x where xx=old.xx;
>   delete from y where yy=old.yy;
> );
> -- CREATE
>
> -- Insert a row into each table:
>
> insert into x values (1);
> -- INSERT 812084785 1
> insert into y values (1);
> -- INSERT 812084786 1
>
> -- Did it work?
>
> select * from xy;
> --  xx | yy
> -- ----+----
> --   1 |  1
> -- (1 row)
>
> -- Great! Now delete it:
>
> delete from xy where xx=1;
> -- DELETE 0
> select * from xy;
> -- (No rows)
>
> -- Looks good so far - it's gone from the view
>
> select * from x;
> -- (No rows)
>
> -- Gone from x too - still OK, BUT:
>
> select * from y;
> --  yy
> -- ----
> --   1
> -- (1 row)
>
> OOPS! How come it is still here???
>
> Any ideas?
>
> Your help will be greatly appreciated!
>
> Thanks a lot!
>
> Dima
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #