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

Same error:
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)

Many thanks.

Patrick

• notebook:
  • 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
• openacs.org:
  • 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
  • openacs

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)

-g

Many thanks.
Patrick

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.

http://cvs.openacs.org/changelog/OpenACS?cs=oacs-5-8%3Agustafn%3A20150206095107

after the next nightly update, these fixes are available via "upgrade from repository".

-g