Multi::Maintenance: Only run update_vncache() on VNs released within the past 5 days

And run only run a full update_vncache() monthly.

This was the last daily cron that took quite a while to run, the
complete daily cron time has now been reduced from about 90 seconds to
about 5 seconds.

1 files changed, 25 insertions, 8 deletions

diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 1434fa73..864239dd 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -17,13 +17,13 @@ sub spawn {
     package_states => [
       $p => [qw|
         _start shutdown set_daily daily set_monthly monthly log_stats
-        vncache tagcache vnpopularity vnrating cleangraphs
-        usercache statscache logrotate
+        vncache_inc tagcache vnpopularity vnrating cleangraphs
+        vncache_full usercache statscache logrotate
       |],
     ],
     heap => {
-      daily => [qw|vncache tagcache vnpopularity vnrating cleangraphs|],
-      monthly => [qw|usercache statscache logrotate|],
+      daily => [qw|vncache_inc tagcache vnpopularity vnrating cleangraphs|],
+      monthly => [qw|vncache_full usercache statscache logrotate|],
       @_,
     },
   );
@@ -98,10 +98,20 @@ sub log_stats { # num, res, action, time
 #
 
 
-sub vncache {
-  # this takes about 50s to complete. We really need to search for an alternative
-  # method of keeping the c_* columns in the vn table up-to-date.
-  $_[KERNEL]->post(pg => do => 'SELECT update_vncache(id) FROM vn', undef, 'log_stats', 'vncache');
+sub vncache_inc {
+  # takes about 50ms to 1s to complete, depending on how many
+  # releases have been released within the past 5 days
+  $_[KERNEL]->post(pg => do => q|
+    SELECT update_vncache(id)
+      FROM (
+        SELECT DISTINCT rv.vid
+          FROM releases r
+          JOIN releases_rev rr ON rr.id = r.latest
+          JOIN releases_vn rv ON rv.rid = r.latest
+         WHERE rr.released  > TO_CHAR(NOW() - '5 days'::interval, 'YYYYMMDD')::integer
+           AND rr.released <= TO_CHAR(NOW(), 'YYYYMMDD')::integer
+     ) AS r(id)
+  |, undef, 'log_stats', 'vncache_inc');
 }
 
 
@@ -146,6 +156,13 @@ sub cleangraphs {
 #
 
 
+sub vncache_full {
+  # this takes more than a minute to complete, and should only be necessary in the
+  # event that the daily vncache_inc cron hasn't been running for 5 subsequent days.
+  $_[KERNEL]->post(pg => do => 'SELECT update_vncache(id) FROM vn', undef, 'log_stats', 'vncache_full');
+}
+
+
 sub usercache {
   # Shouldn't really be necessary, except c_changes could be slightly off when hiding/unhiding DB items
   # Currently takes about 25 seconds to complete.