Forum OpenACS Development: Re: OpenACS 5.8.1 - PL/pgSQL functions application_group__delete and acs_group__delete

• 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