LP#1526858: speed up queries for shelf-expired holds during checkin user/gmcharlt/lp1526858_query_alhr_faster
authorGalen Charlton <gmc@esilibrary.com>
Wed, 16 Dec 2015 16:03:42 +0000 (11:03 -0500)
committerGalen Charlton <gmc@esilibrary.com>
Wed, 16 Dec 2015 16:03:42 +0000 (11:03 -0500)
commit5f4407a450ff3e72f75f3de4bd568b8f43847499
tree4ae6f5a9d6229fb8358eb2a258499dc9f3774675
parent616dc3a884a4f894d0aa38ba4b701b4f2108a447
LP#1526858: speed up queries for shelf-expired holds during checkin

This patch adds indexes on action.hold_request and tweaks
the definition of the alhr IDL view to more efficiently
identify holds that can be canceled if the "Clear Holds Shelf"
checkin modifier is in use.  This can prevent a problem
where such checkins can time out in very large databases.

The tweaks were created by Jason Etheridge and have been in
production at a large consortium for some time.

To test
-------
[1] With a suitably large database, set up an item that
    is on the holds shelf but has passed its shelf-expiration
    time, then check it in using the "Clear Holds Shelf" modifier.
[2] The checkin may fail with a "network failure" message due
    the query on the alhr timing out.
[3] Apply the patch and repeat step 1. This time, the checkin
    should succeed.

Signed-off-by: Galen Charlton <gmc@esilibrary.com>
Open-ILS/examples/fm_IDL.xml
Open-ILS/src/sql/Pg/090.schema.action.sql
Open-ILS/src/sql/Pg/upgrade/XXXX.schema.indexes_for_alhr_queries.sql [new file with mode: 0644]