Forum OpenACS Q&A: Cannot delete from a view

Collapse
1: Cannot delete from a view
Posted by Chris Hardy on 03/26/01 10:04 PM
In the midst of fixing problems with my openacs installation, and I've
started getting the following Error.. Any ideas?

[25/Mar/2001:08:00:00]

    Error: dbinit: error(localhost::acsmission,ERROR:  Cannot delete
from a view without
an appropriate rule
    ):
'delete from deleted_todo_items where (deletion_date+
timespan_days(2))<sysdate()'

Collapse
2: Response to Cannot delete from a view (response to 1)
Posted by Vinod Kurup on 03/26/01 10:32 PM
Yeah, I noticed this too. Did you recently update to postgres 7.1?

I noticed that insert, update or delete's on views are no longer allowed in pg 7.1. See the release history (under bugfixes)

I did a little testing:

  • in PG 7.03, deleting from a view deletes rows in the view, but not in the underlying table
  • in PG 7.1b6, deleting from a view is not allowed
  • in Oracle 8.1.6 deleting from a view deletes rows from the view and from the underlying table
Anyone know why disallowing dml statements on a view is a bugfix? (asking sincerely, cuz I don't know)
Collapse
3: Response to Cannot delete from a view (response to 1)
Posted by Roberto Mello on 03/26/01 11:08 PM
It's a bugfix because deleting from the view and not deleting from the table is not what you want. You want it to be deleted from the table as well. This should come back in 7.1.1 (with the right behaviour).

I just commited lots of fixes to the CVS tree for soon-to-be-released 3.2.5. It fixes this bug.

Collapse
4: Response to Cannot delete from a view (response to 1)
Posted by Michael A. Cleverly on 03/26/01 11:22 PM
Oracle doesn't let you delete from a view unless it's simply a view on a single table. Consider:
Connected to:
Oracle8i Release 8.1.6.1.0 - Production
JServer Release 8.1.6.0.0 - Production

SQL> create table foo ( x integer primary key );

Table created.

SQL> create table bar ( y integer primary key );

Table created.

SQL> create view foobar as select x, y from foo, bar;

View created.

SQL> create view foofoo as select x from foo;

View created.

SQL> insert into foo values (1);

1 row created.

SQL> insert into bar values (2);

1 row created.

SQL> delete from foobar;
delete from foobar

            *

ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table

SQL> delete from foofoo;

1 row deleted.

You can, however (since Oracle 8), define a instead-of trigger to handle the delete. I'd imagine you can do something simillar with Postgres's create rule.
create trigger foobar_trigger
instead of delete on foobar
for each row
begin
delete from foo where x = :old.x;
delete from bar where y = :old.y;
end foobar_trigger;
Now, let's see it work:
SQL> insert into foo values (3);

1 row created.

SQL> delete from foobar;

1 row deleted.