launchpad-reviewers team mailing list archive

[Deryck Hodge <deryck.hodge@xxxxxxxxxxxxx>]

Deryck Hodge has proposed merging lp:~deryck/launchpad/bug-heat-wonky-db-patch-688364 into lp:launchpad/db-devel.

Requested reviews:
  Robert Collins (lifeless): db
  Stuart Bishop (stub): db
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #688364 in Launchpad itself: "Bug heat is wonky"
  https://bugs.launchpad.net/launchpad/+bug/688364

For more details, see:
https://code.launchpad.net/~deryck/launchpad/bug-heat-wonky-db-patch-688364/+merge/68894

This change to trusted.sql changes the calculate_bug_heat function ensure that max_heat of all bugs for a distribution source package is based off the DSP and not just the distro.  This means that a list of source package bugs will no longer have overly inflated heat numbers due to recent activity.  It also means that bugs filed solely on a distribution will no longer have any points added for recent activity.

This is not a perfect fix but it does make the common case better.  More thought needs to be given to this "points from recent activity" aspect of heat before more changes are made.

Also, I had asked Robert about if I needed to change just trusted.sql or if I needed a db patch too and his response implied I needed both.  But this seems at odds with the info at https://dev.launchpad.net/Database/LivePatching, which reads:

"trusted.sql can be run on all nodes inside a transaction at any time to create new or modify existing stored procedures."

So I thought I'd take the lazy route, put it up for review, and find out what I needed to do in terms of the new process.  :-)  So...

How does this look?  Do I need a patch file in addition to this change?  If not, do I need a patch id?  Anything else I need to know in the new world view of db changes?

Sorry to be dumb about this, but hey, I'll know after this landing. :-)

(And FWIW, I have a non-db branch that adds a bunch of test coverage for this change that can land once this is in.)

Cheers,
deryck
-- 
https://code.launchpad.net/~deryck/launchpad/bug-heat-wonky-db-patch-688364/+merge/68894
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~deryck/launchpad/bug-heat-wonky-db-patch-688364 into lp:launchpad/db-devel.

=== modified file 'database/schema/trusted.sql'
--- database/schema/trusted.sql	2011-06-13 08:39:28 +0000
+++ database/schema/trusted.sql	2011-07-22 18:37:30 +0000
@@ -1854,11 +1854,11 @@
         AFFECTED_USER = 4
         SUBSCRIBER = 2
 
-
     def get_max_heat_for_bug(bug_id):
         results = plpy.execute("""
             SELECT MAX(
-                GREATEST(Product.max_bug_heat, Distribution.max_bug_heat))
+                GREATEST(Product.max_bug_heat,
+                         DistributionSourcePackage.max_bug_heat))
                     AS max_heat
             FROM BugTask
             LEFT OUTER JOIN ProductSeries ON
@@ -1871,6 +1871,9 @@
             LEFT OUTER JOIN Distribution ON (
                 BugTask.distribution = Distribution.id
                 OR DistroSeries.distribution = Distribution.id)
+            LEFT OUTER JOIN DistributionSourcePackage ON (
+                BugTask.sourcepackagename =
+                    DistributionSourcePackage.sourcepackagename)
             WHERE
                 BugTask.bug = %s""" % bug_id)