launchpad-reviewers team mailing list archive

Thread
Date

[Merge] lp:~stub/launchpad/drop-branchrevision-id into lp:launchpad/db-devel

To: mp+55480@xxxxxxxxxxxxxxxxxx
From: Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx>
Date: Mon, 23 May 2011 11:35:51 -0000
Reply-to: mp+55480@xxxxxxxxxxxxxxxxxx
Sender: bounces@xxxxxxxxxxxxx

Stuart Bishop has proposed merging lp:~stub/launchpad/drop-branchrevision-id into lp:launchpad/db-devel.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #552701 in Launchpad itself: "BranchRevision.id is going to explode"
  https://bugs.launchpad.net/launchpad/+bug/552701

For more details, see:
https://code.launchpad.net/~stub/launchpad/drop-branchrevision-id/+merge/55480

Branch to drop the id column from the BranchRevision table, replacing the primary key with an existing UNIQUE column. This is tricky, as the only supported mechanism with PostgreSQL 8.4 to change primary key requires building a new index with no CONCURRENTLY option which will take several hours.
-- 
https://code.launchpad.net/~stub/launchpad/drop-branchrevision-id/+merge/55480
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~stub/launchpad/drop-branchrevision-id into lp:launchpad/db-devel.

=== modified file 'database/sampledata/current-dev.sql'
--- database/sampledata/current-dev.sql	2011-05-11 10:32:15 +0000
+++ database/sampledata/current-dev.sql	2011-05-23 11:27:47 +0000
@@ -846,9 +846,6 @@
 
 
 
-
-
-
 SET SESSION AUTHORIZATION DEFAULT;
 
 ALTER TABLE account DISABLE TRIGGER ALL;
@@ -3170,25 +3167,25 @@
 
 ALTER TABLE branchrevision DISABLE TRIGGER ALL;
 
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (1, 1, 10, 1);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (2, 1, 11, 2);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (3, 1, 12, 3);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (4, 1, 20, 4);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (5, 2, 20, 5);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (6, 3, 20, 6);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (7, 4, 20, 7);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (8, 5, 20, 8);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (9, 6, 20, 9);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (10, 1, 21, 4);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (11, 2, 21, 5);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (12, 3, 21, 10);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (13, 4, 21, 11);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (14, 5, 21, 8);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (15, 6, 21, 9);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (16, NULL, 20, 10);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (17, NULL, 20, 11);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (18, NULL, 21, 6);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (19, NULL, 21, 7);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 10, 1);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 11, 2);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 12, 3);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 20, 4);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 21, 4);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (2, 20, 5);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (2, 21, 5);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (3, 20, 6);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (3, 21, 10);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (4, 20, 7);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (4, 21, 11);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (5, 20, 8);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (5, 21, 8);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (6, 20, 9);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (6, 21, 9);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 20, 10);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 20, 11);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 21, 6);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 21, 7);
 
 
 ALTER TABLE branchrevision ENABLE TRIGGER ALL;
@@ -3452,6 +3449,13 @@
 ALTER TABLE bugmessage ENABLE TRIGGER ALL;
 
 
+ALTER TABLE bugmute DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE bugmute ENABLE TRIGGER ALL;
+
+
 ALTER TABLE productseries DISABLE TRIGGER ALL;
 
 INSERT INTO productseries (id, product, name, summary, releasefileglob, releaseverstyle, datecreated, driver, owner, status, translations_autoimport_mode, branch, translations_branch) VALUES (1, 4, 'trunk', 'The "trunk" series represents the primary line of development rather than a stable release branch. This is sometimes also called MAIN or HEAD.', NULL, NULL, '2005-06-06 08:59:51.895136', NULL, 12, 2, 1, NULL, NULL);

=== modified file 'database/sampledata/current.sql'
--- database/sampledata/current.sql	2011-05-11 10:32:15 +0000
+++ database/sampledata/current.sql	2011-05-23 11:27:47 +0000
@@ -846,9 +846,6 @@
 
 
 
-
-
-
 SET SESSION AUTHORIZATION DEFAULT;
 
 ALTER TABLE account DISABLE TRIGGER ALL;
@@ -3106,25 +3103,25 @@
 
 ALTER TABLE branchrevision DISABLE TRIGGER ALL;
 
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (1, 1, 10, 1);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (2, 1, 11, 2);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (3, 1, 12, 3);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (4, 1, 20, 4);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (5, 2, 20, 5);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (6, 3, 20, 6);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (7, 4, 20, 7);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (8, 5, 20, 8);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (9, 6, 20, 9);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (10, 1, 21, 4);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (11, 2, 21, 5);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (12, 3, 21, 10);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (13, 4, 21, 11);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (14, 5, 21, 8);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (15, 6, 21, 9);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (16, NULL, 20, 10);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (17, NULL, 20, 11);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (18, NULL, 21, 6);
-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (19, NULL, 21, 7);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 10, 1);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 11, 2);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 12, 3);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 20, 4);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 21, 4);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (2, 20, 5);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (2, 21, 5);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (3, 20, 6);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (3, 21, 10);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (4, 20, 7);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (4, 21, 11);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (5, 20, 8);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (5, 21, 8);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (6, 20, 9);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (6, 21, 9);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 20, 10);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 20, 11);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 21, 6);
+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 21, 7);
 
 
 ALTER TABLE branchrevision ENABLE TRIGGER ALL;
@@ -3388,6 +3385,13 @@
 ALTER TABLE bugmessage ENABLE TRIGGER ALL;
 
 
+ALTER TABLE bugmute DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE bugmute ENABLE TRIGGER ALL;
+
+
 ALTER TABLE productseries DISABLE TRIGGER ALL;
 
 INSERT INTO productseries (id, product, name, summary, releasefileglob, releaseverstyle, datecreated, driver, owner, status, translations_autoimport_mode, branch, translations_branch) VALUES (1, 4, 'trunk', 'The "trunk" series represents the primary line of development rather than a stable release branch. This is sometimes also called MAIN or HEAD.', NULL, NULL, '2005-06-06 08:59:51.895136', NULL, 12, 2, 1, NULL, NULL);

=== added file 'database/schema/patch-2208-62-0.sql'
--- database/schema/patch-2208-62-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-62-0.sql	2011-05-23 11:27:47 +0000
@@ -0,0 +1,33 @@
+-- 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;
+
+-- Make the existing primary key index think it is not the primary key.
+UPDATE pg_index SET indisprimary = FALSE
+WHERE pg_index.indexrelid = 'revisionnumber_pkey'::regclass;
+
+UPDATE pg_constraint SET contype = 'u'
+WHERE
+    conrelid='branchrevision'::regclass
+    AND conname='revisionnumber_pkey';
+
+
+-- Make an existing index think it is the primary key.
+UPDATE pg_index SET indisprimary = TRUE
+WHERE pg_index.indexrelid = 'revision__revision__branch__key'::regclass;
+
+UPDATE pg_constraint SET contype='p'
+WHERE
+    conrelid='branchrevision'::regclass
+    AND conname='revision__revision__branch__key';
+
+
+-- This view is no longer used - no need to recreate it.
+DROP VIEW RevisionNumber;
+
+ALTER TABLE BranchRevision
+    DROP COLUMN id,
+    DROP CONSTRAINT revision__branch__revision__key;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 62, 0);
+

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2011-05-23 09:13:32 +0000
+++ database/schema/security.cfg	2011-05-23 11:27:47 +0000
@@ -256,7 +256,6 @@
 public.revision                         = SELECT, INSERT, UPDATE
 public.revisionauthor                   = SELECT, INSERT, UPDATE
 public.revisioncache                    = SELECT, INSERT, UPDATE, DELETE
-public.revisionnumber                   = SELECT, INSERT
 public.revisionparent                   = SELECT, INSERT
 public.scriptactivity                   = SELECT
 public.seriessourcepackagebranch        = SELECT, INSERT, UPDATE, DELETE