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

'on delete' rule: bug or feature...

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 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-generalpgsql-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


Responses

pgsql-sql by date

Next:From: Bruce MomjianDate: 2002-08-23 17:21:00
Subject: Re: speeding up \d commands.
Previous:From: Rajesh Kumar Mallah.Date: 2002-08-23 16:04:58
Subject: Re: speeding up \d commands.

pgsql-bugs by date

Next:From: Jan WieckDate: 2002-08-23 17:57:27
Subject: Re: [GENERAL] 'on delete' rule: bug or feature...
Previous:From: pgsql-bugsDate: 2002-08-23 17:04:56
Subject: Bug #745: PL/Perl function fails if not called first, under solaris

pgsql-general by date

Next:From: Dmitry TkachDate: 2002-08-23 17:20:05
Subject: Re: alter table ad primary key
Previous:From: Sebastian HetzeDate: 2002-08-23 16:53:23
Subject: OSS RDBMS Features Compared

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