Re: [GENERAL] 'on delete' rule: bug or feature... - Mailing list pgsql-sql

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