Re: [GENERAL] 'on delete' rule: bug or feature...

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 in psql):
>
> -- 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(at)postgresql(dot)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(at)Yahoo(dot)com #