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

[Colin Watson]

Prune old completed BranchMergeProposalJobs

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

This is a rebased version of https://code.launchpad.net/~cjwatson/launchpad/bmp-job-pruner/+merge/362460, which couldn't be merged directly since it was from the bzr era.

[Guruprasad (lgp171188)]

LGTM 👍

review: Approve

diff --git a/lib/lp/scripts/garbo.py b/lib/lp/scripts/garbo.py
index 92ab040..b8c425c 100644
--- a/lib/lp/scripts/garbo.py
+++ b/lib/lp/scripts/garbo.py
@@ -1,4 +1,4 @@
-# Copyright 2009-2020 Canonical Ltd.  This software is licensed under the
+# Copyright 2009-2023 Canonical Ltd.  This software is licensed under the
 # GNU Affero General Public License version 3 (see the file LICENSE).
 
 """Database garbage collection."""
@@ -1315,6 +1315,25 @@ class GitJobPruner(BulkPruner):
         """
 
 
+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.
@@ -2557,6 +2576,7 @@ class DailyDatabaseGarbageCollector(BaseDatabaseGarbageCollector):
         BinaryPackagePublishingHistoryFormatPopulator,
         BinaryPackagePublishingHistorySPNPopulator,
         BranchJobPruner,
+        BranchMergeProposalJobPruner,
         BugNotificationPruner,
         BugWatchActivityPruner,
         CodeImportEventPruner,
diff --git a/lib/lp/scripts/tests/test_garbo.py b/lib/lp/scripts/tests/test_garbo.py
index 796866f..cd06fcc 100644
--- a/lib/lp/scripts/tests/test_garbo.py
+++ b/lib/lp/scripts/tests/test_garbo.py
@@ -1,4 +1,4 @@
-# Copyright 2009-2020 Canonical Ltd.  This software is licensed under the
+# Copyright 2009-2023 Canonical Ltd.  This software is licensed under the
 # GNU Affero General Public License version 3 (see the file LICENSE).
 
 """Test the database garbage collector."""
@@ -54,6 +54,7 @@ from lp.code.interfaces.revisionstatus import (
     IRevisionStatusReportSet,
 )
 from lp.code.model.branchjob import 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
@@ -978,6 +979,52 @@ class TestGarbo(FakeAdapterMixin, TestCaseWithFactory):
         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 = IPrimaryStore(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 = IPrimaryStore(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")