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