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;
);

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.

I tried that, but I get a "...violates foreign-key constraint" error.

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"