Skip site navigation (1) Skip section navigation (2)

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

From: Dmitry Tkach <dmitry(at)openratings(dot)com>
To: Jan Wieck <JanWieck(at)Yahoo(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org,pgsql-sql(at)postgresql(dot)org
Subject: Re: [GENERAL] 'on delete' rule: bug or feature...
Date: 2002-08-23 17:57:54
Message-ID: 3D6677A2.9080205@openratings.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-generalpgsql-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 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
>>
>




In response to

pgsql-sql by date

Next:From: Wei WengDate: 2002-08-23 19:16:45
Subject: question on UPDATE TABLE
Previous:From: Jan WieckDate: 2002-08-23 17:57:27
Subject: Re: [GENERAL] 'on delete' rule: bug or feature...

pgsql-bugs by date

Next:From: Prashanth RDate: 2002-08-25 11:55:54
Subject: Error in Connecting java-postgresql
Previous:From: Jan WieckDate: 2002-08-23 17:57:27
Subject: Re: [GENERAL] 'on delete' rule: bug or feature...

pgsql-general by date

Next:From: Orr, SteveDate: 2002-08-23 18:28:04
Subject: Re: OSS RDBMS Features Compared
Previous:From: Jan WieckDate: 2002-08-23 17:57:27
Subject: Re: [GENERAL] 'on delete' rule: bug or feature...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group