Merge lp:~cjwatson/launchpad/bmp-job-pruner into lp:launchpad

[Colin Watson]

Prune old completed BranchMergeProposalJobs.

This accounts for about 3 million out of the 17 million Job rows on production, so it seems worth the effort of pruning.

[Jürgen Gmach (jugmac00)]

review: Approve

[Guruprasad (lgp171188)]

Colin, can this be merged?

[Colin Watson (cjwatson)]

Superseded by https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/444874.

Unmerged revisions

18866. By Colin Watson on 2019-01-30

Prune old completed BranchMergeProposalJobs.

=== modified file 'lib/lp/scripts/garbo.py'
--- lib/lp/scripts/garbo.py	2018-12-17 14:49:53 +0000
+++ lib/lp/scripts/garbo.py	2019-01-30 14:10:40 +0000
@@ -1,4 +1,4 @@
-# Copyright 2009-2018 Canonical Ltd.  This software is licensed under the
+# Copyright 2009-2019 Canonical Ltd.  This software is licensed under the
 # GNU Affero General Public License version 3 (see the file LICENSE).
 
 """Database garbage collection."""
@@ -1238,6 +1238,24 @@
         """
 
 
+class BranchMergeProposalJobPruner(BulkPruner):
+    """Prune `BranchMergeProposalJob`s that are in a final state and more
+    than a month old.
+
+    When a BranchMergeProposalJob is completed, it gets set to a final
+    state. These jobs should be pruned from the database after a month.
+    """
+    target_table_class = Job
+    ids_to_prune_query = """
+        SELECT DISTINCT Job.id
+        FROM Job, BranchMergeProposalJob
+        WHERE
+            Job.id = BranchMergeProposalJob.job
+            AND Job.date_finished < CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
+                - CAST('30 days' AS interval)
+        """
+
+
 class SnapBuildJobPruner(BulkPruner):
     """Prune `SnapBuildJob`s that are in a final state and more than a month
     old.
@@ -1927,6 +1945,7 @@
     tunable_loops = [
         AnswerContactPruner,
         BranchJobPruner,
+        BranchMergeProposalJobPruner,
         BugNotificationPruner,
         BugWatchActivityPruner,
         CodeImportEventPruner,

=== modified file 'lib/lp/scripts/tests/test_garbo.py'
--- lib/lp/scripts/tests/test_garbo.py	2018-12-17 14:49:53 +0000
+++ lib/lp/scripts/tests/test_garbo.py	2019-01-30 14:10:40 +0000
@@ -1,4 +1,4 @@
-# Copyright 2009-2018 Canonical Ltd.  This software is licensed under the
+# Copyright 2009-2019 Canonical Ltd.  This software is licensed under the
 # GNU Affero General Public License version 3 (see the file LICENSE).
 
 """Test the database garbage collector."""
@@ -61,6 +61,7 @@
     BranchJob,
     BranchUpgradeJob,
     )
+from lp.code.model.branchmergeproposaljob import BranchMergeProposalJob
 from lp.code.model.codeimportevent import CodeImportEvent
 from lp.code.model.codeimportresult import CodeImportResult
 from lp.code.model.diff import Diff
@@ -957,6 +958,50 @@
         switch_dbuser('testadmin')
         self.assertEqual(store.find(BranchJob).count(), 1)
 
+    def test_BranchMergeProposalJobPruner(self):
+        # Garbo should remove jobs completed over 30 days ago.
+        switch_dbuser('testadmin')
+        store = IMasterStore(Job)
+
+        bmp = self.factory.makeBranchMergeProposal()
+        bmp_job = removeSecurityProxy(bmp.next_preview_diff_job)
+        bmp_job.job.date_finished = THIRTY_DAYS_AGO
+
+        self.assertEqual(
+            store.find(
+                BranchMergeProposalJob,
+                BranchMergeProposalJob.branch_merge_proposal ==
+                    bmp.id).count(),
+                1)
+
+        self.runDaily()
+
+        switch_dbuser('testadmin')
+        self.assertEqual(
+            store.find(
+                BranchMergeProposalJob,
+                BranchMergeProposalJob.branch_merge_proposal ==
+                    bmp.id).count(),
+                0)
+
+    def test_BranchMergeProposalJobPruner_doesnt_prune_recent_jobs(self):
+        # Check to make sure the garbo doesn't remove jobs that aren't more
+        # than thirty days old.
+        switch_dbuser('testadmin')
+        store = IMasterStore(Job)
+
+        bmp = self.factory.makeBranchMergeProposal()
+        bmp_job = removeSecurityProxy(bmp.next_preview_diff_job)
+        bmp_job.job.date_finished = THIRTY_DAYS_AGO
+
+        bmp2 = self.factory.makeBranchMergeProposal()
+        self.assertIsNotNone(bmp2.next_preview_diff_job)
+
+        self.runDaily()
+
+        switch_dbuser('testadmin')
+        self.assertEqual(store.find(BranchMergeProposalJob).count(), 1)
+
     def test_GitJobPruner(self):
         # Garbo should remove jobs completed over 30 days ago.
         switch_dbuser('testadmin')