Proposal for a Comprehensive Fix to Shared Address Issues in Evergreen
Dear Evergreen Developers, While executing a patron purge initiative at Bibliomation, I encountered an issue related to shared addresses that prevented some deletions from completing. I was able to work around the issue, but I believe a more permanent fix should be implemented to prevent similar problems in the future. I decided to share this as an email because it's a bit long and is easier to read with some shiny formatting that isn't possible on Launchpad. The root cause, as outlined in Bug #885270 <https://bugs.launchpad.net/evergreen/+bug/885270>, is that cloned patrons historically shared the same address row in the database. When attempting to delete a patron whose address is still referenced by another user, the process fails. Over the years, multiple solutions have been introduced to mitigate this issue, but they remain somewhat fragmented and the bug ticket remains open. *Summary of Fixes Implemented So Far* 1. *Library Setting: "Cloned patrons get address copy"* - When enabled, this setting ensures newly cloned patrons receive a separate address entry rather than sharing the original. - However, this only prevents new instances of the issue and does not fix pre-existing shared addresses. 2. *fix_addresses.sql <https://bugs.launchpad.net/evergreen/+bug/885270/+attachment/4112365/+files/fix_addresses.sql> Script (shared by Jason Boyer)* - This script untangles shared addresses by duplicating them, ensuring each patron has a unique address entry. - It serves as a one-time fix for existing shared addresses but does not enforce consistent behavior moving forward. 3. *Address Deletion Failure* - The actor.usr_purge_data function currently attempts to delete all addresses linked to a user, which fails if the address is still referenced elsewhere. - A fail-safe could be added to prevent deletion if the address is still associated with another patron. *Proposed Next Steps**1. Make Cloning Behavior Consistent* - Remove the "Cloned patrons get address copy" setting and enforce the creation of separate address entries for all cloned patrons by default. - No use case seems to require shared addresses, and maintaining this setting prolongs potential inconsistencies. - Can anyone think of a use case where shared addresses would be helpful? - Does anyone have that setting set to *false*? *2. Database Migration Fix* - The fix_addresses.sql script should be incorporated into a future database upgrade process to automatically separate any remaining shared addresses. *3. Modify actor.usr_purge_data to Handle Shared Addresses Gracefully* - Instead of failing outright when trying to delete a shared address, modify the function to ensure the address is not deleted if still referenced. A log message can be added to report any cases where the issue persists. - SELECT COUNT(*) INTO shared_address_count FROM actor.usr u WHERE (u.mailing_address = actor.usr_address.id OR u.billing_address = actor.usr_address.id) AND u.id != src_usr; IF shared_address_count > 0 THEN RAISE NOTICE 'Address shared by another patron, not deleting address for user %', src_usr; ELSE DELETE FROM actor.usr_address WHERE usr = src_usr; END IF; - This adjustment ensures the deletion process continues rather than throwing an error while preserving shared addresses when necessary. *Next Steps & Feedback* Would the community support moving forward with this approach? If there’s consensus, I’d be happy to contribute toward implementing these updates. Looking forward to your thoughts! -- Best regards, *Ian Skelskey* *Evergreen Systems Specialist* *Bibliomation Inc.* 203-577-4070 <+12035774070> ext. 108
Ian, Nice write up! I've been bitten by this bug a few times. I am fine with removing the shared address feature like you're proposing. Though, I wonder why it was introduced in the first place? (before my time) - My guess is that it makes it so that the staff only need to edit the address in one place (for the parent account for example) and the associated addresses for (children) are also updated? Is that feature expected by staff out there? If it were removed, would it surprise everyone? -Blake- Conducting Magic Will consume any data format MOBIUS On 2/28/2025 9:38 AM, Ian Skelskey via Evergreen-dev wrote:
Dear Evergreen Developers,
While executing a patron purge initiative at Bibliomation, I encountered an issue related to shared addresses that prevented some deletions from completing. I was able to work around the issue, but I believe a more permanent fix should be implemented to prevent similar problems in the future.
I decided to share this as an email because it's a bit long and is easier to read with some shiny formatting that isn't possible on Launchpad. The root cause, as outlined in Bug #885270 <https://bugs.launchpad.net/evergreen/+bug/885270>, is that cloned patrons historically shared the same address row in the database. When attempting to delete a patron whose address is still referenced by another user, the process fails. Over the years, multiple solutions have been introduced to mitigate this issue, but they remain somewhat fragmented and the bug ticket remains open.
*Summary of Fixes Implemented So Far*
1.
*Library Setting: "Cloned patrons get address copy"*
* When enabled, this setting ensures newly cloned patrons receive a separate address entry rather than sharing the original. * However, this only prevents new instances of the issue and does not fix pre-existing shared addresses. 2.
*|fix_addresses.sql <https://bugs.launchpad.net/evergreen/+bug/885270/+attachment/4112365/+files/fix_addresses.sql>| Script (shared by Jason Boyer)*
* This script untangles shared addresses by duplicating them, ensuring each patron has a unique address entry. * It serves as a one-time fix for existing shared addresses but does not enforce consistent behavior moving forward. 3.
*Address Deletion Failure*
* The |actor.usr_purge_data| function currently attempts to delete all addresses linked to a user, which fails if the address is still referenced elsewhere. * A fail-safe could be added to prevent deletion if the address is still associated with another patron.
*Proposed Next Steps*
*1. Make Cloning Behavior Consistent*
* Remove the "Cloned patrons get address copy" setting and enforce the creation of separate address entries for all cloned patrons by default. * No use case seems to require shared addresses, and maintaining this setting prolongs potential inconsistencies. o Can anyone think of a use case where shared addresses would be helpful? o Does anyone have that setting set to *false*?
*2. Database Migration Fix*
* The |fix_addresses.sql| script should be incorporated into a future database upgrade process to automatically separate any remaining shared addresses.
*3. Modify |actor.usr_purge_data| to Handle Shared Addresses Gracefully*
* Instead of failing outright when trying to delete a shared address, modify the function to ensure the address is not deleted if still referenced. A log message can be added to report any cases where the issue persists. * SELECTCOUNT(*) INTOshared_address_count FROMactor.usr u WHERE(u.mailing_address = actor.usr_address.id <http://actor.usr_address.id> ORu.billing_address = actor.usr_address.id <http://actor.usr_address.id>) ANDu.id <http://u.id> != src_usr;
IF shared_address_count > 0 THEN RAISE NOTICE 'Address shared by another patron, not deleting address for user %', src_usr; ELSE DELETEFROMactor.usr_address WHEREusr = src_usr; ENDIF;
* This adjustment ensures the deletion process continues rather than throwing an error while preserving shared addresses when necessary.
*Next Steps & Feedback*
Would the community support moving forward with this approach? If there’s consensus, I’d be happy to contribute toward implementing these updates.
Looking forward to your thoughts!
-- Best regards,
*Ian Skelskey* /Evergreen Systems Specialist/ *Bibliomation Inc.* 203-577-4070 <tel:+12035774070> ext. 108
_______________________________________________ Evergreen-dev mailing list --evergreen-dev@list.evergreen-ils.org To unsubscribe send an email toevergreen-dev-leave@list.evergreen-ils.org
Hi, On Fri, Feb 28, 2025 at 4:00 PM Ian Skelskey via Evergreen-dev < evergreen-dev@list.evergreen-ils.org> wrote:
*1. Make Cloning Behavior Consistent*
- Remove the "Cloned patrons get address copy" setting and enforce the creation of separate address entries for all cloned patrons by default. - No use case seems to require shared addresses, and maintaining this setting prolongs potential inconsistencies. - Can anyone think of a use case where shared addresses would be helpful?
The intention is to allow members of a group of linked patrons to have an address update be done in one fell swoop by editing the address through the lead record in the group (or as is currently the case with the Angular patron registration form, any member of the group). It would also help ensure that the address of a child's account doesn't diverge from the parent/guardian's address.
Reducing data entry work and ensuring consistent addresses among family members is a valid use case. Now, the feature as it exists has caused problems (and the "Patron Registration: Cloned patrons get address copy" setting exists for a reason). I think there are alternative approaches that could serve the purpose (e.g., having the patron editor offer to apply an address update to other members of a group), so I'm no partisan of the current design. However, just dropping it might come as a surprise to users. The evergreen-general mailing list will get at more people who can advise whether they use shared addresses.
- Does anyone have that setting set to *false*?
It's not common, but at least one of our customer systems has a library
system with that setting set to false.
*3. Modify actor.usr_purge_data to Handle Shared Addresses Gracefully*
I think the intent of this is fine - and it could be done even if address sharing is left as is - but there's a detail to be worked out (and this may have contributed to this bug languishing for so long): actor.usr_address.usr links back to the patron that is intended to be the lead of the group. Consequently, if actor.usr_purge_data() is handling such a lead patron, some provision is needed to reassign actor.usr_address.usr to another member of the group (or to give up and - usefully - ask a human operator to sort out the grouped patrons). Regards, Galen -- Galen Charlton Implementation and IT Manager Equinox Open Library Initiative gmc@equinoxOLI.org https://www.equinoxOLI.org phone: 877-OPEN-ILS (673-6457) direct: 770-709-5581 <http://evergreen-ils.org>
participants (3)
-
Blake Graham-Henderson -
Galen Charlton -
Ian Skelskey