Merge lp:~adeuring/launchpad/fix-broken-initialisation-of-bug-latest-patch-uploaded into lp:launchpad/db-devel

[Abel Deuring]

[Abel Deuring (adeuring)]

The original query to initialise the new column bug.latest_patch_uploaded contained a "teensy weensy little mistake", to quote jtv.

The naively nested SELECTs let the query run for a ridiculously long time. The new version should be better. There is no need to access the tbale Bug at all in the SELECT statement -- all we need are the IDs of bugs that need to be updated.

[Abel Deuring (adeuring)]

I added an index for bugattachment.bug.

jtv noticed that we don't have such an index at present. Without it, the inital update query for the column bug.latest_patch_updated takes quite a long time; finding bug attachments for a given bug will benefit as well from this index.

[Abel Deuring (adeuring)]

I added an index for bugattachment.bug.

jtv noticed that we don't have such an index at present. Without it, the inital update query for the column bug.latest_patch_updated takes quite a long time; finding bug attachments for a given bug will benefit as well from this index.

[Stuart Bishop (stub)]

I think the following is better to populate the new column (it avoids the noise from the SELECT output, and will be faster as there is no stored procedure call overhead):

UPDATE Bug
SET latest_patch_uploaded = LatestPatch.datecreated
FROM (
    SELECT BugAttachment.bug, max(Message.datecreated) AS datecreated
    FROM BugAttachment, Message
    WHERE BugAttachment.message = Message.id
        AND BugAttachment.type = 1
    GROUP BY BugAttachment.bug
    ) AS LatestPatch
WHERE LatestPatch.bug = Bug.id;

The index is good.

[Jonathan Lange (jml)]

Fine by me.

review: Approve (database)

[Stuart Bishop (stub)]

review: Approve (db)

=== modified file 'database/schema/patch-2207-29-0.sql'
--- database/schema/patch-2207-29-0.sql	2010-02-02 11:29:58 +0000
+++ database/schema/patch-2207-29-0.sql	2010-02-15 09:34:17 +0000
@@ -18,9 +18,17 @@
 AFTER DELETE ON bugattachment
 FOR EACH ROW EXECUTE PROCEDURE bug_update_latest_patch_uploaded_on_delete();
 
-SELECT bug_update_latest_patch_uploaded(bug.id)
-    FROM bug WHERE EXISTS (
-        SELECT bugattachment.id FROM bugattachment, bug
-            WHERE bugattachment.bug=bug.id AND bugattachment.type=1);
+CREATE INDEX bugattachment__bug__idx ON BugAttachment(bug);
+
+UPDATE Bug
+SET latest_patch_uploaded = LatestPatch.datecreated
+FROM (
+    SELECT BugAttachment.bug, max(Message.datecreated) AS datecreated
+    FROM BugAttachment, Message
+    WHERE BugAttachment.message = Message.id
+        AND BugAttachment.type = 1
+    GROUP BY BugAttachment.bug
+    ) AS LatestPatch
+WHERE LatestPatch.bug = Bug.id;
 
 INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 29, 0);