From 8f324450421997cb5619d7ba00e38d423ad466d8 Mon Sep 17 00:00:00 2001 From: Thomas Berezansky Date: Mon, 18 Jun 2012 13:07:56 -0400 Subject: [PATCH] Purge Circulations: Better selection logic This attempts two things: 1 - Keep the number of circs the library wants based on the most recent circulations, regardless of user prefs and such. This is accomplished by changing the circ fetching query to use a window function in a subquery so we can filter on it easily. This also allowed removal of the purge position counter. 2 - Allow removal of circs that are between kept circs if they meet all other removal criteria. This is accomplished by using CONTINUE instead of EXIT in the loop. Signed-off-by: Thomas Berezansky Signed-off-by: Jason Stephenson --- Open-ILS/src/sql/Pg/090.schema.action.sql | 25 +++++++++++------------ 1 file changed, 12 insertions(+), 13 deletions(-) diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 506724257c..573b1e73af 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -986,7 +986,6 @@ DECLARE circ_chain_head action.circulation%ROWTYPE; circ_chain_tail action.circulation%ROWTYPE; - purge_position INT; count_purged INT; num_incomplete INT; @@ -1014,22 +1013,23 @@ BEGIN GROUP BY target_copy HAVING COUNT(*) > org_keep_count LOOP - purge_position := 0; -- And, for those, select circs that are finished and older than keep_age FOR circ_chain_head IN - SELECT * - FROM action.circulation - WHERE target_copy = target_acp.target_copy - AND parent_circ IS NULL - ORDER BY xact_start + -- For reference, the subquery uses a window function to order the circs newest to oldest and number them + -- The outer query then uses that information to skip the most recent set the library wants to keep + -- End result is we don't care what order they come out in, as they are all potentials for deletion. + SELECT ac.* FROM action.circulation ac JOIN ( + SELECT rank() OVER (ORDER BY xact_start DESC), ac.id + FROM action.circulation ac + WHERE ac.target_copy = target_acp.target_copy + AND ac.parent_circ IS NULL + ORDER BY ac.xact_start ) ranked USING (id) + WHERE ranked.rank > org_keep_count LOOP - -- Stop once we've purged enough circs to hit org_keep_count - EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count; - SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id); - EXIT WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0; + CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0; IF NOT org_use_last THEN last_finished := circ_chain_tail.xact_finish; @@ -1054,7 +1054,7 @@ BEGIN keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL ); END IF; - EXIT WHEN AGE(NOW(), last_finished) < keep_age; + CONTINUE WHEN AGE(NOW(), last_finished) < keep_age; -- We've passed the purging tests, purge the circ chain starting at the end DELETE FROM action.circulation WHERE id = circ_chain_tail.id; @@ -1064,7 +1064,6 @@ BEGIN END LOOP; count_purged := count_purged + 1; - purge_position := purge_position + 1; END LOOP; END LOOP; -- 2.43.2