launchpad-reviewers team mailing list archive

[Colin Watson <cjwatson@xxxxxxxxxxxxx>]

Colin Watson has proposed merging lp:~cjwatson/launchpad/db-process-accepted-bugs-job into lp:launchpad/db-devel.

Requested reviews:
  Stuart Bishop (stub): db
  Robert Collins (lifeless): db
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #745799 in Launchpad itself: "DistroSeries:+queue Timeout accepting packages (bug structural subscriptions)"
  https://bugs.launchpad.net/launchpad/+bug/745799

For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/db-process-accepted-bugs-job/+merge/119320

== Summary ==

Bug 745799: Some uploads can't be accepted within the timeout, principally because they have to close lots of bugs and dealing with structural subscriptions is very slow.

== Proposed fix ==

I describe my proposed fix in detail in https://bugs.launchpad.net/launchpad/+bug/745799/comments/20.  We aren't likely to be able to solve the structural subscription problem any time soon, and uploads can close arbitrarily large numbers of bugs, so in order to get rid of the queue script in Launchpad (the only remaining purpose of which is to work around timeouts with processing some uploads) we need to move bug processing to run asynchronously.

This is the DB part of this change.

== Pre-implementation notes ==

http://irclogs.ubuntu.com/2012/08/04/%23launchpad-dev.html#t01:49

== LOC Rationale ==

+46, depending on how you want to count DB changes.  Even with the associated code changes, this will still be dwarfed by removing the queue script (-1956).
-- 
https://code.launchpad.net/~cjwatson/launchpad/db-process-accepted-bugs-job/+merge/119320
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~cjwatson/launchpad/db-process-accepted-bugs-job into lp:launchpad/db-devel.

=== modified file 'database/sampledata/current-dev.sql'
--- database/sampledata/current-dev.sql	2012-08-03 19:21:19 +0000
+++ database/sampledata/current-dev.sql	2012-08-13 10:12:24 +0000
@@ -762,9 +762,6 @@
 
 
 
-
-
-
 SET SESSION AUTHORIZATION DEFAULT;
 
 ALTER TABLE account DISABLE TRIGGER ALL;
@@ -3535,6 +3532,13 @@
 ALTER TABLE bugsubscriptionfilterimportance ENABLE TRIGGER ALL;
 
 
+ALTER TABLE bugsubscriptionfilterinformationtype DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE bugsubscriptionfilterinformationtype ENABLE TRIGGER ALL;
+
+
 ALTER TABLE bugsubscriptionfiltermute DISABLE TRIGGER ALL;
 
 
@@ -9013,6 +9017,13 @@
 ALTER TABLE potranslation ENABLE TRIGGER ALL;
 
 
+ALTER TABLE processacceptedbugsjob DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE processacceptedbugsjob ENABLE TRIGGER ALL;
+
+
 ALTER TABLE productjob DISABLE TRIGGER ALL;
 
 

=== modified file 'database/sampledata/current.sql'
--- database/sampledata/current.sql	2012-08-03 19:21:19 +0000
+++ database/sampledata/current.sql	2012-08-13 10:12:24 +0000
@@ -762,9 +762,6 @@
 
 
 
-
-
-
 SET SESSION AUTHORIZATION DEFAULT;
 
 ALTER TABLE account DISABLE TRIGGER ALL;
@@ -3469,6 +3466,13 @@
 ALTER TABLE bugsubscriptionfilterimportance ENABLE TRIGGER ALL;
 
 
+ALTER TABLE bugsubscriptionfilterinformationtype DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE bugsubscriptionfilterinformationtype ENABLE TRIGGER ALL;
+
+
 ALTER TABLE bugsubscriptionfiltermute DISABLE TRIGGER ALL;
 
 
@@ -8936,6 +8940,13 @@
 ALTER TABLE potranslation ENABLE TRIGGER ALL;
 
 
+ALTER TABLE processacceptedbugsjob DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE processacceptedbugsjob ENABLE TRIGGER ALL;
+
+
 ALTER TABLE productjob DISABLE TRIGGER ALL;
 
 

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2012-07-31 20:35:05 +0000
+++ database/schema/comments.sql	2012-08-13 10:12:24 +0000
@@ -715,6 +715,14 @@
 COMMENT ON COLUMN PreviewDiff.target_revision_id IS 'The target branch revision_id used to generate this diff.';
 
 
+-- ProcessAcceptedBugsJob
+COMMENT ON TABLE ProcessAcceptedBugsJob IS 'Contains references to jobs for modifying bugs in response to accepting package uploads.';
+COMMENT ON COLUMN ProcessAcceptedBugsJob.job IS 'The Job related to this ProcessAcceptedBugsJob.';
+COMMENT ON COLUMN ProcessAcceptedBugsJob.distroseries IS 'The DistroSeries of the accepted upload.';
+COMMENT ON COLUMN ProcessAcceptedBugsJob.sourcepackagerelease IS 'The SourcePackageRelease of the accepted upload.';
+COMMENT ON COLUMN ProcessAcceptedBugsJob.json_data IS 'A JSON struct containing data for the job.';
+
+
 -- Product
 COMMENT ON TABLE Product IS 'Product: a DOAP Product. This table stores core information about an open source product. In Launchpad, anything that can be shipped as a tarball would be a product, and in some cases there might be products for things that never actually ship, depending on the project. For example, most projects will have a ''website'' product, because that allows you to file a Malone bug against the project website. Note that these are not actual product releases, which are stored in the ProductRelease table.';
 COMMENT ON COLUMN Product.owner IS 'The Product owner would typically be the person who created this product in Launchpad. But we will encourage the upstream maintainer of a product to become the owner in Launchpad. The Product owner can edit any aspect of the Product, as well as appointing people to specific roles with regard to the Product. Also, the owner can add a new ProductRelease and also edit Rosetta POTemplates associated with this product.';

=== added file 'database/schema/patch-2209-27-2.sql'
--- database/schema/patch-2209-27-2.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-27-2.sql	2012-08-13 10:12:24 +0000
@@ -0,0 +1,14 @@
+-- Copyright 2012 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+CREATE TABLE ProcessAcceptedBugsJob (
+    id serial PRIMARY KEY,
+    job integer REFERENCES Job ON DELETE CASCADE UNIQUE NOT NULL,
+    distroseries integer REFERENCES DistroSeries NOT NULL,
+    sourcepackagerelease integer REFERENCES SourcePackageRelease NOT NULL,
+    json_data text
+);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 27, 2);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2012-08-06 03:47:42 +0000
+++ database/schema/security.cfg	2012-08-13 10:12:24 +0000
@@ -266,6 +266,7 @@
 public.polloption                       = SELECT, INSERT, UPDATE, DELETE
 public.potexport                        = SELECT
 public.previewdiff                      = SELECT, INSERT, UPDATE, DELETE
+public.processacceptedbugsjob           = SELECT, INSERT, UPDATE, DELETE
 public.productjob                       = SELECT, INSERT, UPDATE, DELETE
 public.productrelease                   = SELECT, INSERT, UPDATE, DELETE
 public.productreleasefile               = SELECT, INSERT, DELETE
@@ -1521,6 +1522,7 @@
 [process_accepted]
 type=user
 groups=queued
+public.processacceptedbugsjob           = SELECT, INSERT
 
 [session]
 type=user