Forum OpenACS Development: Problem when i tried to delete a user from db (nuke user)

1: Problem when i tried to delete a user from db (nuke user)

Posted by Enrique Catalan on 11/08/04 08:53 PM

Hi everyone,

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

Regards,
Enrique

2: Re: Problem when i tried to delete a user from db (nuke user) (response to 1)

Posted by Matthew Geddert on 11/08/04 11:51 PM

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 deleteduser12345@hostname.tdl 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.

3: Re: Problem when i tried to delete a user from db (nuke user) (response to 1)

Posted by Enrique Catalan on 11/09/04 01:55 AM

Thanks Mattew.

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.

Regards,
Enrique.

4: Re: Problem when i tried to delete a user from db (nuke user) (response to 3)

Posted by russ m on 11/11/04 04:04 AM

Surely the correct way to fix this is for the constraints to be defined with "on delete cascade" so that any referencing objects are automatically deleted when the referenced object is deleted. I'd assume the data model wasn't written this way because on delete cascade wasn't supported in postgres when OACS4 was being written, but it's been available since at least PG 7.2.

5: Re: Problem when i tried to delete a user from db (nuke user) (response to 1)

Posted by Enrique Catalan on 11/11/04 04:52 AM

Yes, that is a better way. but in that way we have to modify the constraints on some tables, adding the "on delete cascade" option.

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 ?

6: Re: Problem when i tried to delete a user from db (nuke user) (response to 1)

Posted by Nima Mazloumi on 11/11/04 05:14 PM

What's wrong with adding the "on delete cascade" option. This would result in a clean nuke process.

But what happens to a user's forum posts, shared files...?

7: Re: Problem when i tried to delete a user from db (nuke user) (response to 1)

Posted by Enrique Catalan on 11/11/04 10:50 PM

Adding "on delete cascade" is clean and it works, but the problem i have is because not all the columns with "references" constraint have "on delete cascade".

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.

11: Re: Problem when i tried to delete a user from db (nuke user) (response to 7)

Posted by russ m on 11/12/04 01:55 AM

Enrique - I realise that just saying we should use "on delete cascade" doesn't solve your immediate problem... crawling through the various pg_ tables to essentially do the cascade yourself is the way to do what you're trying to do right now.

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

8: Re: Problem when i tried to delete a user from db (nuke user) (response to 1)

Posted by Jade Rubick on 11/12/04 12:01 AM

I think Nima's question is very relevant. There isn't a really easy answer to this, in my opinion, because on delete cascade can delete their content as well.

And ignoring that can screw up your site.

9: Re: Problem when i tried to delete a user from db (nuke user) (response to 8)

Posted by russ m on 11/12/04 01:45 AM

well that's what "nuke user" has always been about... removing a user and all their content... it's not an everyday admin tool...

10: Re: Problem when i tried to delete a user from db (nuke user) (response to 1)

Posted by Enrique Catalan on 11/12/04 02:10 AM

Yes, Nima's question is relevant, if forum posts, shared files and other content of this user are important and we need to keep it, I think we have to use the 'delete' user option.

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