Merge lp:~stub/launchpad/bugsummary into lp:launchpad/db-devel

[Stuart Bishop]

[r=stub][bug=810104] Speed up BugSummaryJournal rollup

= Summary =

Rolling up the BugSummaryJournal entries into the BugSummary table is slow.

== Proposed fix ==

Rewrite the logic in constructs that PG optimizes better in this case.

Move removal of 'count 0' rows to once per rollup, rather than once per decrement.

== Pre-implementation notes ==

== Implementation details ==

The old code is in patch-2208-75-0.sql

== Tests ==

== Demo and Q/A ==

= Launchpad lint =

Checking for conflicts and issues in changed files.

Linting changed files:
  database/schema/patch-2208-76-4.sql

[Stuart Bishop (stub)]

review: Approve (db)

[lp:~stub/launchpad/bugsummary]

10695. By Stuart Bishop on 2011-07-28

Add a batchsize for future garbo job

=== added file 'database/schema/patch-2208-76-4.sql'
--- database/schema/patch-2208-76-4.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-76-4.sql	2011-07-28 13:00:58 +0000
@@ -0,0 +1,159 @@
+-- Copyright 2011 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+DROP FUNCTION bugsummary_rollup_journal();
+
+CREATE OR REPLACE FUNCTION bugsummary_rollup_journal(batchsize integer=NULL)
+RETURNS VOID
+LANGUAGE plpgsql VOLATILE
+CALLED ON NULL INPUT
+SECURITY DEFINER SET search_path TO public AS
+$$
+DECLARE
+    d bugsummary%ROWTYPE;
+    max_id integer;
+BEGIN
+    -- Lock so we don't content with other invokations of this
+    -- function. We can happily lock the BugSummary table for writes
+    -- as this function is the only thing that updates that table.
+    -- BugSummaryJournal remains unlocked so nothing should be blocked.
+    LOCK TABLE BugSummary IN ROW EXCLUSIVE MODE;
+
+    IF batchsize IS NULL THEN
+        SELECT MAX(id) INTO max_id FROM BugSummaryJournal;
+    ELSE
+        SELECT MAX(id) INTO max_id FROM (
+            SELECT id FROM BugSummaryJournal ORDER BY id LIMIT batchsize
+            ) AS Whatever;
+    END IF;
+
+    FOR d IN
+        SELECT
+            NULL as id,
+            SUM(count),
+            product,
+            productseries,
+            distribution,
+            distroseries,
+            sourcepackagename,
+            viewed_by,
+            tag,
+            status,
+            milestone,
+            importance,
+            has_patch,
+            fixed_upstream
+        FROM BugSummaryJournal
+        WHERE id <= max_id
+        GROUP BY
+            product, productseries, distribution, distroseries,
+            sourcepackagename, viewed_by, tag, status, milestone,
+            importance, has_patch, fixed_upstream
+        HAVING sum(count) <> 0
+    LOOP
+        IF d.count < 0 THEN
+            PERFORM bug_summary_dec(d);
+        ELSIF d.count > 0 THEN
+            PERFORM bug_summary_inc(d);
+        END IF;
+    END LOOP;
+
+    -- Clean out any counts we reduced to 0.
+    DELETE FROM BugSummary WHERE count=0;
+    -- Clean out the journal entries we have handled.
+    DELETE FROM BugSummaryJournal WHERE id <= max_id;
+END;
+$$;
+
+COMMENT ON FUNCTION bugsummary_rollup_journal(integer) IS
+'Collate and migrate rows from BugSummaryJournal to BugSummary';
+
+
+CREATE OR REPLACE FUNCTION bug_summary_dec(bugsummary) RETURNS VOID
+LANGUAGE SQL AS
+$$
+    -- We own the row reference, so in the absence of bugs this cannot
+    -- fail - just decrement the row.
+    UPDATE BugSummary SET count = count + $1.count
+    WHERE
+        ((product IS NULL AND $1.product IS NULL)
+            OR product = $1.product)
+        AND ((productseries IS NULL AND $1.productseries IS NULL)
+            OR productseries = $1.productseries)
+        AND ((distribution IS NULL AND $1.distribution IS NULL)
+            OR distribution = $1.distribution)
+        AND ((distroseries IS NULL AND $1.distroseries IS NULL)
+            OR distroseries = $1.distroseries)
+        AND ((sourcepackagename IS NULL AND $1.sourcepackagename IS NULL)
+            OR sourcepackagename = $1.sourcepackagename)
+        AND ((viewed_by IS NULL AND $1.viewed_by IS NULL)
+            OR viewed_by = $1.viewed_by)
+        AND ((tag IS NULL AND $1.tag IS NULL)
+            OR tag = $1.tag)
+        AND status = $1.status
+        AND ((milestone IS NULL AND $1.milestone IS NULL)
+            OR milestone = $1.milestone)
+        AND importance = $1.importance
+        AND has_patch = $1.has_patch
+        AND fixed_upstream = $1.fixed_upstream;
+$$;
+
+CREATE OR REPLACE FUNCTION bug_summary_inc(d bugsummary) RETURNS VOID
+LANGUAGE plpgsql AS
+$$
+BEGIN
+    -- Shameless adaption from postgresql manual
+    LOOP
+        -- first try to update the row
+        UPDATE BugSummary SET count = count + d.count
+        WHERE
+            ((product IS NULL AND $1.product IS NULL)
+                OR product = $1.product)
+            AND ((productseries IS NULL AND $1.productseries IS NULL)
+                OR productseries = $1.productseries)
+            AND ((distribution IS NULL AND $1.distribution IS NULL)
+                OR distribution = $1.distribution)
+            AND ((distroseries IS NULL AND $1.distroseries IS NULL)
+                OR distroseries = $1.distroseries)
+            AND ((sourcepackagename IS NULL AND $1.sourcepackagename IS NULL)
+                OR sourcepackagename = $1.sourcepackagename)
+            AND ((viewed_by IS NULL AND $1.viewed_by IS NULL)
+                OR viewed_by = $1.viewed_by)
+            AND ((tag IS NULL AND $1.tag IS NULL)
+                OR tag = $1.tag)
+            AND status = $1.status
+            AND ((milestone IS NULL AND $1.milestone IS NULL)
+                OR milestone = $1.milestone)
+            AND importance = $1.importance
+            AND has_patch = $1.has_patch
+            AND fixed_upstream = $1.fixed_upstream;
+        IF found THEN
+            RETURN;
+        END IF;
+        -- not there, so try to insert the key
+        -- if someone else inserts the same key concurrently,
+        -- we could get a unique-key failure
+        BEGIN
+            INSERT INTO BugSummary(
+                count, product, productseries, distribution,
+                distroseries, sourcepackagename, viewed_by, tag,
+                status, milestone,
+                importance, has_patch, fixed_upstream)
+            VALUES (
+                d.count, d.product, d.productseries, d.distribution,
+                d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
+                d.status, d.milestone,
+                d.importance, d.has_patch, d.fixed_upstream);
+            RETURN;
+        EXCEPTION WHEN unique_violation THEN
+            -- do nothing, and loop to try the UPDATE again
+        END;
+    END LOOP;
+END;
+$$;
+
+
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 76, 4);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2011-07-25 08:48:47 +0000
+++ database/schema/security.cfg	2011-07-28 13:00:58 +0000
@@ -143,7 +143,7 @@
 public.bugnotificationrecipientarchive  = SELECT, UPDATE
 public.bugsummary                       = SELECT
 public.bugsummaryjournal                = SELECT
-public.bugsummary_rollup_journal()      = EXECUTE
+public.bugsummary_rollup_journal(integer) = EXECUTE
 public.bugtag                           = SELECT, INSERT, DELETE
 public.bugtrackercomponent              = SELECT, INSERT, UPDATE
 public.bugtrackercomponentgroup         = SELECT, INSERT, UPDATE