Error in Vandelay org unit matching on copy

[Jeff Davis]

On some systems, the vandelay.auto_overlay_org_unit_copies database function can be extremely slow and/or loop indefinitely. I haven't been able to reproduce the error consistently, but there are some issues with the function's while loop, and addressing those issues seemed to make the problem go away on systems where there was a problem. Fix forthcoming.

[Jeff Davis (jdavis-sitka)]

Working branch user/jeffdavis/lp1848375_auto_overlay_org_unit_copies has the fix:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jeffdavis/lp1848375_auto_overlay_org_unit_copies

If we run out of scope_args, cardinality(scope_args) is NULL rather than 0, so we need to check for that. In addition, we now exit early once we have a match, rather than preparing another iteration unnecessarily.

[Galen Charlton (gmc)]

Signed off: working/user/gmcharlt/lp1848375_signoff

The test procedure would be something like this:

- Set up a situation where a MARC record with items is staged and has at least one bib record match that itself has items
- Try importing the record with the following options:

* overlay on single match off
* overlay on exact match off
* overlay on best match off
* overlay on Use Org Unit Matching in Copy to Determine Best Match _on_

- The import should hang, and checking running Pg queries will show vandelay.auto_overlay_org_unit_copies() running indefinitely
- Cancel the query, apply the patch, and try again.
- This time the import should succeed.

Note that the key part of the patch is exiting when a candidate bib has been found that has at least one copy. The parts about checking CARDINALITY(scope_orgs) against NULL do not materially matter, since if the cardinality is NULL, the expression in the WHILE loop will evaluate to NULL and end the loop anyway.

[Galen Charlton (gmc)]

(Also, it's possible that the function might be made a bit faster by, rather than looping over the bib matches, using a single query that counts the number of copies with in the OU scope for all matches and returns the winner. For that matter, the loop on the scope_orgs could be removed by having inner query check against all of the scope OUs simultaneously. However, I doubt that such an optimization would be worth holding up this patch any further.)