Thread: Is this correct behavior for ON DELETE rule?
I have two related tables, “item” and “book”. I have defined
a view, “bookview” that contains fields from item and book.
My goal was to have all inserts, updates, and deletes performed
on bookview rather than on the tables directly. I was able
to do this with ON INSERT and ON UPDATE rules easily.
I am having trouble with the ON DELETE rule. When a row is
deleted from bookview, the appropriate row should be deleted
from item and from book. The example below only deletes the
row from book.
Is this expected behavior, and if so, what is the right way
to do this? At the moment I have defined an ON DELETE rule
on item which works. But I would prefer if this could be
done on the view.
Thanks for any help.
----------------------------------------------------------
drop table book cascade;
drop table item cascade;
-- "parent" table
create table item
(id serial primary key,
type varchar(8),
title varchar(20)
);
-- "child" table
create table book
(id integer references item primary key,
title varchar(20),
author varchar(20)
);
-- combine stuff from item and book tables
create view bookview as
select i.id, b.title, b.author from item i, book b
where i.id=b.id;
-- insert to item and book instead of bookview
create rule bookviewins as on insert to bookview do instead (
insert into item (type, title)
values ('book', new.title);
insert into book (id, title, author)
values (currval('item_id_seq'), new.title, new.author);
);
-- delete to item and book instead of bookview
create rule bookviewdel as on delete to bookview do instead (
delete from book where id=old.id;
delete from item where id=old.id;
);
-- everyone has access to bookview
grant all on bookview to public;
insert into bookview (title, author) values ('Dune','Herbert');
insert into bookview (title, author) values ('Hobbit','Tolkein');
select * from bookview;
delete from bookview where author='Tolkein';
-- "DELETE 0"
select * from bookview;
-- looks correct
select * from item;
-- shows both books
select * from book;
-- looks correct
Uh, because of your REFERENCES clause you have to delete from 'item'
first, then 'book':
> -- delete to item and book instead of bookview
> create rule bookviewdel as on delete to bookview do instead (
> delete from item where id=old.id;
> delete from book where id=old.id;
> );
And your posting is double-spaces for some reason.
---------------------------------------------------------------------------
Rick Schumeyer wrote:
>
>
> I have two related tables, "item" and "book". I have defined
>
> a view, "bookview" that contains fields from item and book.
>
> My goal was to have all inserts, updates, and deletes performed
>
> on bookview rather than on the tables directly. I was able
>
> to do this with ON INSERT and ON UPDATE rules easily.
>
>
>
> I am having trouble with the ON DELETE rule. When a row is
>
> deleted from bookview, the appropriate row should be deleted
>
> from item and from book. The example below only deletes the
>
> row from book.
>
>
>
> Is this expected behavior, and if so, what is the right way
>
> to do this? At the moment I have defined an ON DELETE rule
>
> on item which works. But I would prefer if this could be
>
> done on the view.
>
>
>
> Thanks for any help.
>
>
>
> ----------------------------------------------------------
>
> drop table book cascade;
>
> drop table item cascade;
>
>
>
> -- "parent" table
>
> create table item
>
> (id serial primary key,
>
> type varchar(8),
>
> title varchar(20)
>
> );
>
>
>
> -- "child" table
>
> create table book
>
> (id integer references item primary key,
>
> title varchar(20),
>
> author varchar(20)
>
> );
>
>
>
> -- combine stuff from item and book tables
>
> create view bookview as
>
> select i.id, b.title, b.author from item i, book b
>
> where i.id=b.id;
>
>
>
> -- insert to item and book instead of bookview
>
> create rule bookviewins as on insert to bookview do instead (
>
> insert into item (type, title)
>
> values ('book', new.title);
>
> insert into book (id, title, author)
>
> values (currval('item_id_seq'), new.title, new.author);
>
> );
>
>
>
> -- delete to item and book instead of bookview
>
> create rule bookviewdel as on delete to bookview do instead (
>
> delete from book where id=old.id;
>
> delete from item where id=old.id;
>
> );
>
>
>
> -- everyone has access to bookview
>
> grant all on bookview to public;
>
>
>
> insert into bookview (title, author) values ('Dune','Herbert');
>
> insert into bookview (title, author) values ('Hobbit','Tolkein');
>
>
>
> select * from bookview;
>
>
>
> delete from bookview where author='Tolkein';
>
> -- "DELETE 0"
>
>
>
> select * from bookview;
>
> -- looks correct
>
>
>
> select * from item;
>
> -- shows both books
>
>
>
> select * from book;
>
> -- looks correct
>
>
>
>
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
"Rick Schumeyer" <rschumeyer@ieee.org> writes:
> -- delete to item and book instead of bookview
> create rule bookviewdel as on delete to bookview do instead (
> delete from book where id=old.id;
> delete from item where id=old.id;
> );
This is an ancient gotcha: as soon as you delete the book row, there is
no longer any such entry in the bookview view ... and "old.id" is
effectively a reference to the bookview view, so the second delete
finds no matching rows.
If you can reasonably turn the view into a LEFT JOIN in one direction or
the other, then a workaround is to delete from the nullable side first.
regards, tom lane
I suspected that might be part of the answer. Would some combination of triggers work instead? I've played with those too, but without success. > > This is an ancient gotcha: as soon as you delete the book row, there is > no longer any such entry in the bookview view ... and "old.id" is > effectively a reference to the bookview view, so the second delete > finds no matching rows. >
I tried that, but I get a "...violates foreign-key constraint" error. > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Bruce Momjian > Sent: Friday, February 25, 2005 6:23 PM > To: Rick Schumeyer > Cc: 'PgSql General' > Subject: Re: [GENERAL] Is this correct behavior for ON DELETE rule? > > > Uh, because of your REFERENCES clause you have to delete from 'item' > first, then 'book': > > > -- delete to item and book instead of bookview > > create rule bookviewdel as on delete to bookview do instead ( > > delete from item where id=old.id; > > delete from book where id=old.id; > > ); >
Rick Schumeyer wrote: > I tried that, but I get a "...violates foreign-key constraint" error. Oh, sorry. --------------------------------------------------------------------------- > > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > > owner@postgresql.org] On Behalf Of Bruce Momjian > > Sent: Friday, February 25, 2005 6:23 PM > > To: Rick Schumeyer > > Cc: 'PgSql General' > > Subject: Re: [GENERAL] Is this correct behavior for ON DELETE rule? > > > > > > Uh, because of your REFERENCES clause you have to delete from 'item' > > first, then 'book': > > > > > -- delete to item and book instead of bookview > > > create rule bookviewdel as on delete to bookview do instead ( > > > delete from item where id=old.id; > > > delete from book where id=old.id; > > > ); > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
"Rick Schumeyer" <rschumeyer@ieee.org> writes:
> Would some combination of triggers work instead?
Nope, you can't put triggers on a view, sorry. In theory a BEFORE
INSERT trigger would be a workable alternative to an ON INSERT rule
for redirecting insertions. (I think we disallow it at the moment
though.) But UPDATE and DELETE triggers can't work because the view
doesn't actually contain any physical tuples and so there is nothing
for the triggers to fire on.
regards, tom lane
on 2/25/05 4:09 PM, rschumeyer@ieee.org purportedly said: > I have two related tables, ³item² and ³book². I have defined > a view, ³bookview² that contains fields from item and book. > My goal was to have all inserts, updates, and deletes performed > on bookview rather than on the tables directly. I was able > to do this with ON INSERT and ON UPDATE rules easily. You may have better luck letting foreign key constraints work for you, and specify ON DELETE CASCADE in your constraint. If, however, you don't always want to delete related "book" rows when "item" rows are deleted, you may want to re-think your relation. > -- "child" table > create table book > (id integer references item primary key, > title varchar(20), > author varchar(20) > ); Best, Keary Suska Esoteritech, Inc. "Demystifying technology for your home or business"