Launchpad itself

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

  1. fix-broken-initialisation-of-bug-latest-patch-uploaded
  2. Merge into db-devel
Proposed by Abel Deuring
Status: Merged
Merged at revision: not available
Proposed branch: lp:~adeuring/launchpad/fix-broken-initialisation-of-bug-latest-patch-uploaded
Merge into: lp:launchpad/db-devel
Diff against target: 25 lines (+12/-4)
1 file modified
database/schema/patch-2207-29-0.sql (+12/-4)
To merge this branch: bzr merge lp:~adeuring/launchpad/fix-broken-initialisation-of-bug-latest-patch-uploaded
Reviewer Review Type Date Requested Status
Stuart Bishop (community) db Approve
Jonathan Lange (community) database Approve
Review via email: mp+18610@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Abel Deuring (adeuring) wrote :

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.

Revision history for this message
Abel Deuring (adeuring) wrote :

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.

Revision history for this message
Abel Deuring (adeuring) wrote :

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.

Revision history for this message
Stuart Bishop (stub) wrote :

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.

Revision history for this message
Jonathan Lange (jml) wrote :

Fine by me.

review: Approve (database)
Revision history for this message
Stuart Bishop (stub) :
review: Approve (db)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'database/schema/patch-2207-29-0.sql'
2--- database/schema/patch-2207-29-0.sql 2010-02-02 11:29:58 +0000
3+++ database/schema/patch-2207-29-0.sql 2010-02-15 09:34:17 +0000
4@@ -18,9 +18,17 @@
5 AFTER DELETE ON bugattachment
6 FOR EACH ROW EXECUTE PROCEDURE bug_update_latest_patch_uploaded_on_delete();
7
8-SELECT bug_update_latest_patch_uploaded(bug.id)
9- FROM bug WHERE EXISTS (
10- SELECT bugattachment.id FROM bugattachment, bug
11- WHERE bugattachment.bug=bug.id AND bugattachment.type=1);
12+CREATE INDEX bugattachment__bug__idx ON BugAttachment(bug);
13+
14+UPDATE Bug
15+SET latest_patch_uploaded = LatestPatch.datecreated
16+FROM (
17+ SELECT BugAttachment.bug, max(Message.datecreated) AS datecreated
18+ FROM BugAttachment, Message
19+ WHERE BugAttachment.message = Message.id
20+ AND BugAttachment.type = 1
21+ GROUP BY BugAttachment.bug
22+ ) AS LatestPatch
23+WHERE LatestPatch.bug = Bug.id;
24
25 INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 29, 0);

Subscribers

People subscribed via source and target branches

to status/vote changes: