Delete User Aborts on Shared Address

[Robert J Jackson]

Evergreen 2.0.4
PostgreSQL 9.0 and PostgreSQL 8.4.7
Debian Squeeze and Debian Lenny

When deleting a patron that has an address that is shared by another patron the delete aborts returning {"ilsevent":-1,"textcode":"Server or method error"}

Traced the error to the following log entry:

2011-11-01 11:25:19 192.168.80.12 open-ils.cstore: [ERR :9525:oils_sql.c:1082:1320160975440734] open-ils.cstore: Error committing transaction: 0 ERROR: update or delete on table "usr_address" violates foreign key constraint "actor_usr_billining_address_fkey" on table "usr" DETAIL: Key (id)=(442945) is still referenced from table "usr".
2011-11-01 11:25:19 192.168.80.12 open-ils.cstore: [INFO:9525:osrf_app_session.c:991:1320160975440734] [open-ils.cstore] sent 189 bytes of data to <email address hidden>/open-ils.actor_drone_at_xenb1app2.evergreen.lib.in.us_13474

In the example error above the patron's actor.usr_address key of 442945 was shared by another patron resulting in this error. From what I can tell the definition of actor.usr_delete calls the function actor.usr_purge_data and it attempts to delete from actor.usr_address.

[Thomas Berezansky (tsbere)]

I would argue that this is an issue of bad data. Patrons should not be sharing address entries as each address has a *single* usr field to identify which patron it belongs to. By having any other patron's record refer to that address the behavior would not be defined in a number of places.

[Robert J Jackson (rjackson-deactivatedaccount)]

The clone user allows for shared addresses. Here is a line from oour Add User documentation:

•Save and clone user button: the contact info is copied into the next record. Records created using this method are automatically grouped together with the original record and share the same address, which can only be edited in the original record.

[Thomas Berezansky (tsbere)]

If it is an intended feature (and based on comments in IRC, I guess it is) then I argue it is a *broken* feature.

My recommendation would be to re-implement it such that each user gets their own copy of the address and each address gets a pointer to the "parent" address that it is linked to. Said pointer would be a foreign key with an auto-null on delete, to automatically break the link if the parent is deleted. Otherwise a trigger would cascade the updates to the parent address into the children. One could require that editing the "child" address requires breaking the link between the two, which would be accomplished by setting the parent link to null (and could be a single button in the patron editor, setting the null and enabling the edit boxes).

Benefits to this method include inactive addresses being able to be linked between users, which would be very handy if automatically rotating out addresses. Otherwise rotating the addresses out will break the links.

Slightly related, if you expose the address IDs to staff you would also be able to trivially add a linked address to another patron. Clone the address, set the parent. Thus allowing you to add new addresses to one account and then go add them to another by manually linking sometime after the cloning was originally done. Or to a non-cloned record, in the case of marriage for example you could link the address entries together on previously unrelated accounts.

[Michael Peters (mrpeters)]

This is, at least, somewhat related to https://bugs.launchpad.net/bugs/965374 which deals with merges of patrons with shared aua entries.

[Ian Bays (ian-bays)]

This came to light on the SEDAR project currently at version 2.2.2 where trying to delete a patron resulted in "Network or server failure:

method=open-ils.actor.user.delete
params=["111586e44c7b33150edd2d736268fc91",478628]
THROWN:
{"payload":[],"debug":"osrfMethodException : *** Call to
[open-ils.actor.user.delete] failed for session
[1358422045.214206.135842204525442], thread trace [1]:\nException:
OpenSRF::DomainObject::oilsMethodException 2013-01-17T11:27:25
OpenSRF::Application /usr/local/share/perl/5.10.1//OpenSRF/Application.pm:211
<500> Error committing transaction\n\n","status":500}

We had not set the Library Setting for "Patron Registration: cloned patrons get address copy" and as above eventually tracked it down to this issue. The only way we found to fix it was to find the other patron record and to put that other patron's key into the address record. Only then could we delete the original patron from the workstation.

There are too many use-cases in the system for address data to be shared between users and for that address data to be "owned" by just one of them with no means of transferring "ownership".

We fixed it for this one case, but I now discover there are 976 groups of shared addresses waiting to bite us. Needless to say we are setting the Library Setting (cloned patrons get address copy) to true.

If anyone else thinks this is a bug and is able to come up with a script or SQL to find and clone these addresses and set the pointers correctly we will be very appreciative ;-)

[Victoria Lewis (vlewis-q)]

When deleting a patron that has an address that was cloned from another patron the delete succeeds, but when deleting a
'parent' patron whose address has been cloned to a 'child' patron, the delete fails.

When I updated the actor.usr_address table, setting the usr foreign key to the 'child's' usr id, I was able to delete the
'parent' patron.

Ian suggests writing a script or SQL to find and clone these addresses and set the pointers correctly. Does this mean
making a new record in the usr_address table for every 'child' patron who shares the address of the 'parent' patron and setting the usr foreign key to the 'child's' usr id?

[Victoria Lewis (vlewis-q)]

In order to prevent an abort on deletion of a user with a shared address the attached sql clones the shared addresses, inserts the addresses into the usr_address table and sets the usr table foreign keys, usr.billing_address and usr.mailing_address, to correctly point to the newly created records. Then the original patron can be deleted.

This assumes that the original and cloned addresses belong to patrons(profile != 1).

This is meant to be run once. To ensure all cloned addresses going forward have their own record in the usr_address table set the 'Cloned patrons get address copy' to true in the Library Setting Editor.

[Jason Boyer (jboyer)]

We recently untangled these in Indiana using a couple different methods. I've cleaned them up and put together a script that can be run through psql. As it is the whole thing is run through in a single transaction (perhaps after hours if you have a lot of them). If you have too many shared addresses to do in one shot a limit parameter could be added to the fix_addresses function and the applicable loops, and the drop function at the bottom removed.

This will tell you how many user accounts will be affected:
SELECT count(au.id) FROM actor.usr au LEFT JOIN actor.usr_address auam ON (au.mailing_address = auam.id) LEFT JOIN actor.usr_address auab ON (au.billing_address = auab.id) WHERE (au.id != auam.usr) OR (au.id != auab.usr);

After (well, before is certainly fine) running this, set the "Patron Registration: Cloned patrons get address copy" OU Setting to True at the top level of your OU tree, and feel relief wash over you as an entire class of end-user tickets is eliminated.

[Jason Stephenson (jstephenson)]

Rogan,

Are you still looking into this issue?

If not, could you remove your assignment from this bug?

Thanks,
Jason