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

Patrick, this has some "magic". Below is the version information from my notebook and from openacs.org. Also on openacs.org, the small test runs without exception.
  • 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)

Hi Gustaf, Hi Michael,

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:
packages/acs-subsite/sql/application_groups-create.sql

In my OpenACS 5.8.1 version (downloaded yesterday from http://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 1.13.2.1 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.

Many thanks.

Patrick

Patrick, great! I'll try to figure out, why the "on delete cascade" is missing in fresh installations and will provide an update script for OpenACS 5.8.1

-g

Patrick, now everything is clear. It seems that I've made the change adding the "on delete cascade" AFTER the release of OpenACS 5.8.1.

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

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
-g

Now it works.

Many thanks.
Patrick

With respect to subgroups: I thought acs_object__delete would take care of deleting the objects in tables of subtypes. But I may have overlooked something of course. Anyway, the important thing is that it works now! Thank you both!
Glad, that everything works!

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