Forum OpenACS Development: ]project-open[ new Filestorage - acs_object__delete throws acs_permissions_on_what_id_fk


We are working on a new file-storage system for ]po[ based on the Content Repository and file-storage. We create special folders per project, and then use a "DropBox" like Windows/Linux client to sync files from the local machine with the server.

This architecture came after warning about issues with WebDAV, thanks!

A first MVP works, although without conflict handling yet.

However, deleting files the file-storage gives errors, just by using the OpenACS GUI of the file-storage (see below).

I've seen similar issues with other object types already: Wouldn't it be correct to delete acs_permission dependencies right in acs_object__delete?


ERROR:  update or delete on table "acs_objects" violates foreign key constraint "acs_permissions_on_what_id_fk" on table "acs_permissions"
DETAIL:  Key (object_id)=(52646) is still referenced from table "acs_permissions".
CONTEXT:  SQL statement "delete from acs_objects where object_id =  52646"
PL/pgSQL function acs_object__delete(integer) line 37 at EXECUTE statement
SQL statement "SELECT acs_object__delete(delete__item_id)"
PL/pgSQL function content_item__del(integer) line 65 at PERFORM
SQL statement "SELECT content_item__del (delete__item_id)"
PL/pgSQL function content_item__delete(integer) line 5 at PERFORM
PL/pgSQL function file_storage__delete_file(integer) line 5 at RETURN

SQL: select file_storage__delete_file(52646);

"I've seen similar issues with other object types already: Wouldn't it be correct to delete acs_permission dependencies right in acs_object__delete?"

My vote is YES, but I remember in the past there was a long discussion about deleting ACS objects. There were arguments about deleting acs_objects impacting/breaking other dependencies on ACS core.

Dear Frank, I cannot comment about Project Open, but the "vanilla" OpenACS already defines the ON DELETE CASCADE behavior you suggest (sorry for the italian shell):
oacs-5-10=# \d acs_permissions
          Tabella "public.acs_permissions"
  Colonna   |          Tipo          | Modificatori 
 object_id  | integer                | non null
 grantee_id | integer                | non null
 privilege  | character varying(100) | non null
    "acs_permissions_pk" PRIMARY KEY, btree (object_id, grantee_id, privilege)
    "acs_permissions_grantee_idx" btree (grantee_id)
    "acs_permissions_object_id_idx" btree (object_id)
    "acs_permissions_privilege_idx" btree (privilege)
Vincoli di integrità referenziale
    "acs_permissions_grantee_id_fk" FOREIGN KEY (grantee_id) REFERENCES parties(party_id) ON DELETE CASCADE
    "acs_permissions_object_id_fk" FOREIGN KEY (object_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE
    "acs_permissions_privilege_fk" FOREIGN KEY (privilege) REFERENCES acs_privileges(privilege) ON DELETE CASCADE
Furthermore, "acs_permissions_on_what_id_fk" constraint does not exist in a default installation.

All the best
Thanks a lot!

Interesting, maybe this is because of an upgrade issue from an older version of OpenACS? I'll check, but this already helped a lot.


Using "on delete cascade" on the permissions table is not a recent change, but was introduced to OpenACS 14 years ago (2005, [1]).



The current ]po[ default database has been created in 2004 😊, I believe and since upgraded to the respective OpenACS and ]po[ version.


If you upgrade acs-core to 5.9.1, these constraints are added.