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

Hi, thanks for your reply. I just downloaded OpenACS 5.8.1 from the download section, created a new database, set up a new OpenACS instance and tested your minimal case:

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

Hi Patrick,

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).

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