Forum OpenACS Development: Problem when i tried to delete a user from db (nuke user)
Somebody knows if I can 'nuke' oacs users ?, I mean, really delete the users from db and not only change the 'member_state' to 'delete' state.
I had a problem with the foreign keys :
ges2dev=> delete from users where user_id = 3995920;
ERROR: update or delete on "users" violates foreign key constraint "acs_objects_creation_user_fk" on "acs_objects"
DETAIL: Key (user_id)=(3995920) is still referenced from table "acs_objects".
ges2dev=> delete from acs_objects where object_id = 3995920;
ERROR: update or delete on "acs_objects" violates foreign key constraint "parties_party_id_fk" on "parties"
DETAIL: Key (object_id)=(3995920) is still referenced from table "parties".
ges2dev=> delete from parties where party_id = 3995920;
ERROR: update or delete on "parties" violates foreign key constraint "persons_person_id_fk" on "persons"
DETAIL: Key (party_id)=(3995920) is still referenced from table "persons".
ges2dev=> delete from persons where person_id = 3995920;
ERROR: update or delete on "persons" violates foreign key constraint "users_user_id_fk" on "users"
DETAIL: Key (person_id)=(3995920) is still referenced from table "users".
And then start again with the table: users, ......
I can delete the fk constraint, but i wish to know if exists a better solution ...
I would not delete the fk constraint! This would cause your data to be very inconsistent (since it would have creation_users that don't exist). Why do you need to Nuke a user? Why isn't delete enough? One way of doing this if your goal is to "free up" their email address as a username or something like that is to change their email address to a bogus one... like this firstname.lastname@example.org that way you allow the email address or username to be used by a new legitimate user. If you goal is that they not show up in user searches (a problem i had in the past - i don't know if this has been fixed) a better way is to edit the search to not show deleted users.
If you need to nuke a user and want to do this the right way it will be very tricky. You will need to delete all any object created by that user or within the security context of that user, any object that is a child of the object created by that user, and child of that child... Note that his may mean, if for exampe a user you are trying to delete responded on a forum that you are deleting posts made by others on that forum since they were responding to the deleted user object.
Your solution is better, instead of delete and recreate the constraint.
I needed to 'nuke' a user because I need to "free up" the email address as you said,
But finally I found a better way for me, I really wanted to nuke the user, so i deleted all the rows in many tables that references acs_objects,users,parties & persons.
I'll write a script to 'nuke' a user, if someone else wants to do that.
I found other one (a little more complicated but it should works); it is to use the pg_constraint, pg_class and some other system tables of the pg_catalog to find all the columns of all the tables that has a contype='f' (constraint type is foreign key), using the 'conrelid' and 'confrelid' (http://www.signal42.com/pgsql/catalog-pg-constraint.html) fields to get the rows that i have to delete.
what do you think about it ?
But what happens to a user's forum posts, shared files...?
I mean that if you want to 'nuke' a user, some tables have their constraints with "on delete cascade" but others don't have it, for example : parties, persons, users created in (...../packages/acs-kernel/sql/postgresql/community-core-create.sql), These tables and others like forums_forums and etc don't have "on delete cascade", so in this case "on delete cascade" option doesnt' work, that is the reason I need to use the pg_catalog.
But I think the general solution is to add "on delete cascade" to most FK constraints in OpenACS... as it stands, if you do want to delete from the DB an object that may have had any of OACS's "object generic" services used on it (comments, relations, whatever) you need to manually find everything that's got an FK reference to the object in question and delete them first. In my opinion that's work that the database should be doing (hell, it's *why* "on delete cascade" exists in the SQL spec)...
And ignoring that can screw up your site.
'nuke' user is an extreme action when you are really sure that you don't need that user anymore (normally this users were created with errors or duplicated) and we just want to clean the db and "free up" the username/email address).
The only solution I found if you want to keep the user content and "free up" the username, is the solution that Matthew posted above, wich is 'delete' the user and change his username.