From: | Dmitry Tkach <dmitry(at)openratings(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org |
Subject: | 'on delete' rule: bug or feature... |
Date: | 2002-08-23 17:09:20 |
Message-ID: | 3D666C40.3060908@openratings.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-general pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2002-08-23 17:57:27 | Re: [GENERAL] 'on delete' rule: bug or feature... |
Previous Message | pgsql-bugs | 2002-08-23 17:04:56 | Bug #745: PL/Perl function fails if not called first, under solaris |
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitry Tkach | 2002-08-23 17:20:05 | Re: alter table ad primary key |
Previous Message | Sebastian Hetze | 2002-08-23 16:53:23 | OSS RDBMS Features Compared |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2002-08-23 17:21:00 | Re: speeding up \d commands. |
Previous Message | Rajesh Kumar Mallah. | 2002-08-23 16:04:58 | Re: speeding up \d commands. |