Launchpad itself

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

  1. drop-branchrevision-id
  2. Merge into db-devel
Proposed by Stuart Bishop
Status: Merged
Approved by: Stuart Bishop
Approved revision: no longer in the source branch.
Merged at revision: 10599
Proposed branch: lp:~stub/launchpad/drop-branchrevision-id
Merge into: lp:launchpad/db-devel
Diff against target: 195 lines (+85/-45)
4 files modified
database/sampledata/current-dev.sql (+26/-22)
database/sampledata/current.sql (+26/-22)
database/schema/patch-2208-62-0.sql (+33/-0)
database/schema/security.cfg (+0/-1)
To merge this branch: bzr merge lp:~stub/launchpad/drop-branchrevision-id
Reviewer Review Type Date Requested Status
Jeroen T. Vermeulen (community) Approve
Review via email: mp+55480@code.launchpad.net

Commit message

[r=jtv][bug=552701] Drop BranchRevision.id

Description of the change

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.

To post a comment you must log in.
Revision history for this message
Jeroen T. Vermeulen (jtv) wrote :

We just discussed this IRL. Fantastic how much cleaner and simpler this is than what I documented originally. Having a unique constraint (as opposed to a mere unique index) in place avoids a lot of the risk. The cast-name-to-relation trick is good to be aware of too.

We may no longer need the server restart before dropping the column, now that the dependency dance is so much simpler. Having to do that would make things much more difficult. Let's land it as-is, deploy it on staging, and see how it works on a real database. We'll still want to restart the database very soon after the change, just to be sure.

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'database/sampledata/current-dev.sql'
2--- database/sampledata/current-dev.sql 2011-05-11 10:32:15 +0000
3+++ database/sampledata/current-dev.sql 2011-05-23 11:27:47 +0000
4@@ -846,9 +846,6 @@
5
6
7
8-
9-
10-
11 SET SESSION AUTHORIZATION DEFAULT;
12
13 ALTER TABLE account DISABLE TRIGGER ALL;
14@@ -3170,25 +3167,25 @@
15
16 ALTER TABLE branchrevision DISABLE TRIGGER ALL;
17
18-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (1, 1, 10, 1);
19-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (2, 1, 11, 2);
20-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (3, 1, 12, 3);
21-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (4, 1, 20, 4);
22-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (5, 2, 20, 5);
23-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (6, 3, 20, 6);
24-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (7, 4, 20, 7);
25-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (8, 5, 20, 8);
26-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (9, 6, 20, 9);
27-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (10, 1, 21, 4);
28-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (11, 2, 21, 5);
29-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (12, 3, 21, 10);
30-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (13, 4, 21, 11);
31-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (14, 5, 21, 8);
32-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (15, 6, 21, 9);
33-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (16, NULL, 20, 10);
34-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (17, NULL, 20, 11);
35-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (18, NULL, 21, 6);
36-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (19, NULL, 21, 7);
37+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 10, 1);
38+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 11, 2);
39+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 12, 3);
40+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 20, 4);
41+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 21, 4);
42+INSERT INTO branchrevision (sequence, branch, revision) VALUES (2, 20, 5);
43+INSERT INTO branchrevision (sequence, branch, revision) VALUES (2, 21, 5);
44+INSERT INTO branchrevision (sequence, branch, revision) VALUES (3, 20, 6);
45+INSERT INTO branchrevision (sequence, branch, revision) VALUES (3, 21, 10);
46+INSERT INTO branchrevision (sequence, branch, revision) VALUES (4, 20, 7);
47+INSERT INTO branchrevision (sequence, branch, revision) VALUES (4, 21, 11);
48+INSERT INTO branchrevision (sequence, branch, revision) VALUES (5, 20, 8);
49+INSERT INTO branchrevision (sequence, branch, revision) VALUES (5, 21, 8);
50+INSERT INTO branchrevision (sequence, branch, revision) VALUES (6, 20, 9);
51+INSERT INTO branchrevision (sequence, branch, revision) VALUES (6, 21, 9);
52+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 20, 10);
53+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 20, 11);
54+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 21, 6);
55+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 21, 7);
56
57
58 ALTER TABLE branchrevision ENABLE TRIGGER ALL;
59@@ -3452,6 +3449,13 @@
60 ALTER TABLE bugmessage ENABLE TRIGGER ALL;
61
62
63+ALTER TABLE bugmute DISABLE TRIGGER ALL;
64+
65+
66+
67+ALTER TABLE bugmute ENABLE TRIGGER ALL;
68+
69+
70 ALTER TABLE productseries DISABLE TRIGGER ALL;
71
72 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);
73
74=== modified file 'database/sampledata/current.sql'
75--- database/sampledata/current.sql 2011-05-11 10:32:15 +0000
76+++ database/sampledata/current.sql 2011-05-23 11:27:47 +0000
77@@ -846,9 +846,6 @@
78
79
80
81-
82-
83-
84 SET SESSION AUTHORIZATION DEFAULT;
85
86 ALTER TABLE account DISABLE TRIGGER ALL;
87@@ -3106,25 +3103,25 @@
88
89 ALTER TABLE branchrevision DISABLE TRIGGER ALL;
90
91-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (1, 1, 10, 1);
92-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (2, 1, 11, 2);
93-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (3, 1, 12, 3);
94-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (4, 1, 20, 4);
95-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (5, 2, 20, 5);
96-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (6, 3, 20, 6);
97-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (7, 4, 20, 7);
98-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (8, 5, 20, 8);
99-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (9, 6, 20, 9);
100-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (10, 1, 21, 4);
101-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (11, 2, 21, 5);
102-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (12, 3, 21, 10);
103-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (13, 4, 21, 11);
104-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (14, 5, 21, 8);
105-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (15, 6, 21, 9);
106-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (16, NULL, 20, 10);
107-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (17, NULL, 20, 11);
108-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (18, NULL, 21, 6);
109-INSERT INTO branchrevision (id, sequence, branch, revision) VALUES (19, NULL, 21, 7);
110+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 10, 1);
111+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 11, 2);
112+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 12, 3);
113+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 20, 4);
114+INSERT INTO branchrevision (sequence, branch, revision) VALUES (1, 21, 4);
115+INSERT INTO branchrevision (sequence, branch, revision) VALUES (2, 20, 5);
116+INSERT INTO branchrevision (sequence, branch, revision) VALUES (2, 21, 5);
117+INSERT INTO branchrevision (sequence, branch, revision) VALUES (3, 20, 6);
118+INSERT INTO branchrevision (sequence, branch, revision) VALUES (3, 21, 10);
119+INSERT INTO branchrevision (sequence, branch, revision) VALUES (4, 20, 7);
120+INSERT INTO branchrevision (sequence, branch, revision) VALUES (4, 21, 11);
121+INSERT INTO branchrevision (sequence, branch, revision) VALUES (5, 20, 8);
122+INSERT INTO branchrevision (sequence, branch, revision) VALUES (5, 21, 8);
123+INSERT INTO branchrevision (sequence, branch, revision) VALUES (6, 20, 9);
124+INSERT INTO branchrevision (sequence, branch, revision) VALUES (6, 21, 9);
125+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 20, 10);
126+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 20, 11);
127+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 21, 6);
128+INSERT INTO branchrevision (sequence, branch, revision) VALUES (NULL, 21, 7);
129
130
131 ALTER TABLE branchrevision ENABLE TRIGGER ALL;
132@@ -3388,6 +3385,13 @@
133 ALTER TABLE bugmessage ENABLE TRIGGER ALL;
134
135
136+ALTER TABLE bugmute DISABLE TRIGGER ALL;
137+
138+
139+
140+ALTER TABLE bugmute ENABLE TRIGGER ALL;
141+
142+
143 ALTER TABLE productseries DISABLE TRIGGER ALL;
144
145 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);
146
147=== added file 'database/schema/patch-2208-62-0.sql'
148--- database/schema/patch-2208-62-0.sql 1970-01-01 00:00:00 +0000
149+++ database/schema/patch-2208-62-0.sql 2011-05-23 11:27:47 +0000
150@@ -0,0 +1,33 @@
151+-- Copyright 2011 Canonical Ltd. This software is licensed under the
152+-- GNU Affero General Public License version 3 (see the file LICENSE).
153+SET client_min_messages=ERROR;
154+
155+-- Make the existing primary key index think it is not the primary key.
156+UPDATE pg_index SET indisprimary = FALSE
157+WHERE pg_index.indexrelid = 'revisionnumber_pkey'::regclass;
158+
159+UPDATE pg_constraint SET contype = 'u'
160+WHERE
161+ conrelid='branchrevision'::regclass
162+ AND conname='revisionnumber_pkey';
163+
164+
165+-- Make an existing index think it is the primary key.
166+UPDATE pg_index SET indisprimary = TRUE
167+WHERE pg_index.indexrelid = 'revision__revision__branch__key'::regclass;
168+
169+UPDATE pg_constraint SET contype='p'
170+WHERE
171+ conrelid='branchrevision'::regclass
172+ AND conname='revision__revision__branch__key';
173+
174+
175+-- This view is no longer used - no need to recreate it.
176+DROP VIEW RevisionNumber;
177+
178+ALTER TABLE BranchRevision
179+ DROP COLUMN id,
180+ DROP CONSTRAINT revision__branch__revision__key;
181+
182+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 62, 0);
183+
184
185=== modified file 'database/schema/security.cfg'
186--- database/schema/security.cfg 2011-05-23 09:13:32 +0000
187+++ database/schema/security.cfg 2011-05-23 11:27:47 +0000
188@@ -256,7 +256,6 @@
189 public.revision = SELECT, INSERT, UPDATE
190 public.revisionauthor = SELECT, INSERT, UPDATE
191 public.revisioncache = SELECT, INSERT, UPDATE, DELETE
192-public.revisionnumber = SELECT, INSERT
193 public.revisionparent = SELECT, INSERT
194 public.scriptactivity = SELECT
195 public.seriessourcepackagebranch = SELECT, INSERT, UPDATE, DELETE

Subscribers

People subscribed via source and target branches

to status/vote changes: