Forum OpenACS Development: Re: OpenACS 5.8.1 - PL/pgSQL functions application_group__delete and acs_group__delete
set group_id [application_group::new -group_name foo]
application_group::delete -group_id $group_id
Database operation "0or1row" failed
(exception ERROR, "FEHLER: Aktualisieren oder Löschen in Tabelle »groups« verletzt Fremdschlüssel-Constraint »application_groups_group_id_fk« von Tabelle »application_groups«
DETAIL: Auf Schlüssel (group_id)=(706) wird noch aus Tabelle »application_groups« verwiesen.
CONTEXT: SQL-Anweisung »delete from groups where group_id = $1 «
PL/pgSQL function "acs_group__delete" line 24 at SQL-Anweisung
When I have a look into the involved functions and PL/pgSQL procedures it seems reasonable to me that the operation fails (as described in my first posting).
I can only explain the successful test run in you system with a difference between your OpenACS installation and mine:
- Does the delete operation within the PL/pgSQL function "acs_group__delete" exist in your OpenACS instance?
--Lets clear the groups table first
delete from groups
where group_id = delete__group_id;
- If so, is there a ON DELETE CASCADE on the foreign key "application_groups_group_id_fk" on table application_groups?
- Last but not least, there could be a Postgres rule or trigger on the groups table (but that would suprise me)
I remember that I have stumbled upon the same issue some time ago. IIRC the solution was to remove (comment) the two lines
--delete from groups
--where group_id = delete__group_id;
The group will still be deleted by some other/later function/mechanism (AFAIR).
- Database: PostgreSQL 9.4.0 on x86_64-apple-darwin14.0.0, compiled by Apple LLVM version 6.0 (clang-600.0.56) (based on LLVM 3.5svn), 64-bit
- acs-kernel 5.8.1
- openacs + DotLRN
- Database: Database: PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
- acs-kernel 5.8.1
Both installations have the identical acs_group__delete and application_groups definitions listed below. What version of postgres do you use?
@Michael: when the deletion after " --Lets clear the groups table first" is removed, then there is an error, when subgroups are involved. Probably, the the subgroups issue could be solved differently, but nevertheless, this does not explain the different behavior on the create/delete sequence.
oacs-5-8=# \df+ acs_group__delete List of functions -[ RECORD 1 ]-------+---------------------------------------------------------------------------------- Schema | public Name | acs_group__delete Result data type | integer Argument data types | delete__group_id integer Type | normal Security | invoker Volatility | volatile Owner | nsadmin Language | plpgsql Source code | | DECLARE | row record; | BEGIN | | -- Delete all the relations of any type to this group | for row in select r.rel_id, t.package_name | from acs_rels r, acs_object_types t | where r.rel_type = t.object_type | and (r.object_id_one = delete__group_id | or r.object_id_two = delete__group_id) | LOOP | execute 'select ' || row.package_name || '__delete(' || row.rel_id || ')'; | end loop; | | -- Delete all segments defined for this group | for row in select segment_id | from rel_segments | where group_id = delete__group_id | LOOP | PERFORM rel_segment__delete(row.segment_id); | end loop; | | --Lets clear the groups table first | delete from groups | where group_id = delete__group_id; | | PERFORM party__delete(delete__group_id); | | return 0; | END; | Description | oacs-5-8=# \d+ application_groups Table "public.application_groups" Column | Type | Modifiers | Storage | Stats target | Description ------------+---------+-----------+---------+--------------+------------- group_id | integer | not null | plain | | package_id | integer | | plain | | Indexes: "application_groups_group_id_pk" PRIMARY KEY, btree (group_id) "application_groups_package_id_un" UNIQUE CONSTRAINT, btree (package_id) Foreign-key constraints: "application_groups_group_id_fk" FOREIGN KEY (group_id) REFERENCES groups(group_id) ON DELETE CASCADE "application_groups_package_id_fk" FOREIGN KEY (package_id) REFERENCES apm_packages(package_id)
As I can see in your table defintion "\d+ application_groups" you have a ON DELETE CASCADE on the foreign key on table groups:
"application_groups_group_id_fk" FOREIGN KEY (group_id) REFERENCES groups(group_id) ON DELETE CASCADE
I think that is the reason why it works with your installation and not in mine.
So the problem is in file:
In my OpenACS 5.8.1 version (downloaded yesterday from https://openacs.org/projects/openacs/download/download/openacs-5.8.1.tar.gz?revision_id=4197803) the file has the CVS-Version:
@cvs-id $Id: application-groups-create.sql,v 1.13 2013/03/30 18:04:20 gustafn Exp $
with no ON DELETE CASCASE in the table definition
But may the issue is already solved in newer versions, because I found the required ON DELETE CASCADE in OpenACS 5.8.1b
@cvs-id $Id: application-groups-create.sql,v 18.104.22.168 2015/01/02 09:00:13 gustafn Exp $
If so, I would just add the ON DELETE CASCADE manually, since the issue should be solved in newer versions.
So, if you have a installation from tar, you can browse to http://YOURINSTALLATION/acs-admin/install/ and do an "upgrade from repository"
all the best
Since we have in OpenACS already many places of "manual" delete operations where cascade operations would be better,
I've revisited the issue. Also after remove the "Lets clear the groups table" i was not able to reproduce bug #3119 and committed the simpler version.
While testing, i fixed as well two issues in the seldomly used groups administration pages.
after the next nightly update, these fixes are available via "upgrade from repository".