LP#1526858: speed up queries for shelf-expired holds during checkin
authorGalen Charlton <gmc@esilibrary.com>
Wed, 16 Dec 2015 16:03:42 +0000 (11:03 -0500)
committerBill Erickson <berickxx@gmail.com>
Fri, 17 Jun 2016 15:16:40 +0000 (11:16 -0400)
commite87747f9ef3f10f3ebfdefa753f04c9173023d8b
tree1ba4a320cbf1392f04a65a048d1115ceb6256760
parentc569df154318d4957c7096dd36a941d96af3830f
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>
Signed-off-by: Jeanette Lundgren <jlundgren@cwmars.org>
Signed-off-by: Kathy Lussier <klussier@masslnc.org>
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]