Message-ID: <3D6677A2.9080205@openratings.com>
Date: Fri, 23 Aug 2002 13:57:54 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.7+) Gecko/20020123
MIME-Version: 1.0
To: Jan Wieck <JanWieck@Yahoo.com>
Cc: pgsql-bugs@postgresql.org, pgsql-general@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: [GENERAL] 'on delete' rule: bug or feature...
References: <3D666C40.3060908@openratings.com> <3D667787.6AF317F5@Yahoo.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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 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@postgresql.org so that your
>>message can get through to the mailing list cleanly
>>
>