1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
|
Database Patch Numbers
======================
The following database patch numbers have been allocated for use. These
patches may not have been deployed or landed yet.
Comment should be meaningful enough for the DBA to determine if a .sql
script claiming to be a particular patch really is a particular patch.
Feel free to reuse numbers marked as moved. Patches normally only get
renumbered if they are to be applied live.
Notes about patches
-------------------
- Do not include BEGIN or COMMIT. They might be useful when
you are testing, but don't let them land with these statements.
- COMMENTs go in comments.sql.
- You need COMMENTs for all new tables and columns. Fix COMMENTs
when you are changing the meaning of a column or table.
- Don't use TRUNCATE. It breaks replication.
- Don't use DROP TABLE. It breaks replication. Instead, remove
any FOREIGN KEY constraints and do `ALTER TABLE Foo SET SCHEMA todrop;`.
The table will be removed from replication and dropped later by our
update scripts.
- Some database patches may be applied live. This needs to be determined
on a case-by-case basis by the DBA. If your patch needs to be applied
live, things it cannot do include:
- ALTER TABLE of any kind.
- Creating a new table that REFERENCES a busy table (such as Person).
Notes about patch numbers
-------------------------
- Unapplied patches are applied in sequence. Patches can only depend
on earlier numbered patches.
- We refer to the three fields in the patch number as major, minor
and patch.
- 'major' changes when we create a new database baseline in the tree.
The baseline is launchpad-$major-00-0.sql, and is regenerated by the
DBA from a production database dump to remove any skew between
production and development. Any db patches not yet deployed need
to be renumbered to match the new baseline, and deployed patches
removed or archived.
- 'minor' and 'patch' are unique to your database patch.
- Minor number can be reused from any of the previously allocated
patch numbers (as long as the dependency graph is satisfied).
- A 'patch' or 0 means the patch can only be deployed as part of a
full update (database and code). On startup, our systems assert
that the set of -0 database patches in the local source tree and
the set of -0 patches that have been applied to the database are
the same. This is a safety net to ensure systems do not attempt
to run against an unexpected database schema.
- /!\ UPDATE: With fastdowntime deploys, we will rarely if ever
use -0 patches.
- A non-zero 'patch' means the database patch can be applied at
any time without affecting any production code. The runtime
checks used for -0 patches are not applied. It is your
responsibility to ensure your patch does not break deployed code
or trunk.
- A non-zero 'patch' *may* be deployable live by the DBA. They need
to be analyzed on a case by case basis, potentially causing them
to be split into a number of smaller patches.
Allocated Database Patch Numbers
--------------------------------
========= ============= =====================================================
Patch # Who Allocated Comment
========= ============= =====================================================
2209-00-0 stub Dummy patch sacrified to patch application machinery
2209-00-1 jtv NOT NULL on [BS]PPH.{binary,source}packagename.
2209-00-2 jelmer ???
2209-00-3 frankban Add milestonetag table.
2209-00-4 gmb Add constraints to Person and EmailAddress.
2209-00-5 stub PG 9.1 compatibility fixes
2209-00-6 wgrant Index ProductReleaseFile.productrelease and Packaging.productseries
2209-00-7 stub Explicit statistics sample size on TeamMembership
2209-00-8 wgrant Include DEBUG in archive__distribution__purpose__key
2209-00-9 wgrant scriptactivity and bugbranch indices
2209-01-0 wgrant Make ValidPerson(OrTeam)Cache use EmailAddress.person
2209-01-1 wgrant Drop EmailAddress.account
2209-02-0 Laney SPPH.sponsor
2209-04-0 Ursinha Specification.{date_last_changed,last_changed_by}
2209-05-1 StevenK New index on BPPH.
2209-06-1 salgado Add SpecificationWorkItem and SpecificationWorkItemChange tables
2209-07-0 wgrant Drop BugTask.fti
2209-07-1 wgrant Fix calculate_bug_heat to not age.
2209-08-1 wallyworld Drop private_bugs_need_contact constraint on product table.
2209-09-0 StevenK Destroy Entitlement
2209-10-0 StevenK Wide-scale index cleanup.
2209-11-0 wgrant Drop old access policy person and branch foreign keys.
2209-11-1 wgrant Remodel access policy schema
2209-11-2 wgrant Drop foreign keys between the old schema so it can drop. Oops.
2209-11-3 wgrant Triggers to mirror bug legacy access to sharing.
2209-11-4 wgrant Rework triggers from 2209-11-3 to use information_type.
2209-11-5 wgrant Add person and branch foreign keys to new access policy schema.
2209-12-0 StevenK Add information visibility policy to bug/branch.
2209-12-1 StevenK Index for bug.information_type.
2209-12-2 StevenK Make bug.information_type NOT NULL.
2209-12-3 StevenK Fiddle bugsummary triggers to use information_type.
2209-12-4 StevenK Index for branch.information_type.
2209-12-5 StevenK Make branch.information_type NOT NULL.
2209-13-0 lifeless New emailjob table.
2209-14-0 lifeless Drop bugjob.
2209-15-0 StevenK Add SPPH.packageupload.
2209-15-1 sinzui Add productjob
2209-15-2 stub Bug #920926 and misc cleanup
2209-15-3 StevenK Index for SPPH.packageupload
2209-16-0 wgrant BugTaskFlat
2209-16-1 wgrant More BugTaskFlat indices
2209-16-2 wgrant SourcePackagePublishingHistory index for bug component search
2209-16-3 wgrant date_closed and latest_patch_uploaded onto BugTaskFlat
2209-16-4 wgrant date_closed and latest_patch_uploaded indices for BugTaskFlat
2209-16-5 wgrant Drop obsolete Bug and BugTask columns
2209-16-6 StevenK Transition Branch to new access model, dropping the old one.
2209-16-7 wgrant Drop the bug legacy access triggers.
2209-16-8 StevenK Delete obsolete branch function.
2209-17-0 wallyworld Sharing jobs
2209-17-1 jtv Drop POFileTranslator.latest_message.
2209-18-0 cjwatson Add packageset.score.
2209-18-1 cjwatson Add ArchivePermission.pocket.
2209-18-2 StevenK Builder.description DROP NOT NULL.
2209-18-3 StevenK Drop Builder.description.
2209-18-4 cjwatson Rename packageset.score to relative_build_score.
2209-19-0 wgrant BugSummary v2
2209-19-1 wgrant BugSummary v2 -- new search indices
2209-19-2 wgrant BugSummary v2 -- new unique index
2209-19-3 wgrant BugSummary v2 -- set access_policy, drop old columns and indices
2209-20-0 stub Drop SpecificationFeedback foreign keys (person merge)
2209-20-1 danilo Drop SpecificationFeedback table
2209-21-0 stub Create GIN index on BugTaskFlat.fti
2209-21-1 stub Drop GiST index on BugTaskFlat.fti
2209-21-2 stub Create remaining GIN indexes to replace GiST indexes.
2209-21-3 stub Drop remaining GiST indexes.
2209-21-4 stub update_disk_utilization fix for Bug #1007333
2209-22-0 jml Archive.suppress_subscription_notifications lp:#1006295
2209-22-1 jml New distribution col - who can create private PPAs
2209-23-0 wgrant DistributionSourcePackageCache indices
2209-23-1 wgrant BinaryPackageName and SourcePackageName indices
2209-23-2 wgrant AccessArtifactGrant and AccessPolicyGrant indices
2209-23-3 wgrant BinaryPackagePublishingHistory index
2209-23-4 wgrant Specification(milestone) index
2209-23-5 wgrant BuildFarmJob(status, id) index
2209-24-1 adeuring Fix stored procedures ftq() and _ftq()
2209-24-2 stub Drop obsolete and shadowed fti helpers in the ts2 schema
2209-24-3 adeuring another fix of ftq() and _ftq()
2209-25-1 Laney Update -propsed to not be automatic lp:#1016776
2209-26-0 wgrant Product columns to replace BranchVisibilityPolicy
2209-26-1 wgrant Drop BranchVisibilityPolicy
2209-26-2 StevenK Drop BugSubscriptionFilter{Status,Importance}.id
2209-26-3 StevenK BugSubscriptionFilterInformationType
2209-26-4 StevenK Drop {Product,Distribution}.security_contact
2209-26-5 StevenK Drop Product.private_bugs
2209-27-1 cjwatson support implementation of per-pocket queue admin permissions
2209-27-2 cjwatson ProcessAcceptedBugsJob
2209-27-3 StevenK New index on BuildFarmJob.
2209-28-1 abentley Add information_type to Specification
2209-28-2 abentley Add index on Specification.information_type
2209-28-3 abentley Set NOT NULL on Specification.information_type
2209-28-4 adeuring New column AccessArtifact.specification
2209-28-5 adeuring Index for AccessArtifact.specification
2209-28-6 StevenK Create Specification.access_{policy,grants}
2209-29-0 wallyworld Update functions to account for new embargoed information type
2209-30-1 wallyworld Alter AccessPolicy table to add person column and associated contraints and index
2209-31-1 deryck Add specification_sharing_policy to Product.
2209-31-2 deryck Set default for Product.specification_sharing_policy.
2209-31-3 deryck Make Product.specification_sharing_policy NOT NULL.
2209-32-0 wgrant Fix bug_summary_dec typo to handle access policies correctly (bug #1046713)
2209-33-1 abentley Drop (unused) Specification.private
2209-34-1 jcsackett Drop (now unused) Product.date_next_suggest_packaging
2209-35-1 deryck Add information_type to Product.
2209-35-2 deryck Add index for Product.information_type.
2209-35-3 deryck Make Product.information_type NOT NULL.
2209-36-0 cjwatson Flag to redirect Ubuntu release uploads to another pocket
2209-36-1 cjwatson BinaryPackagePublishingHistory.phased_update_percentage
2209-37-0 StevenK Drop potmsgset.potemplate.
2209-38-0 wallyworld Add new LatestPersonSourcePackageReleaseCache table.
2209-38-1 wallyworld Add creator and maintainer person FK's to LatestPersonSourcePackageReleaseCache table
2209-38-2 wallyworld Add (archive, distroseries, sourcepackagename) index to LPSPRC table.
2209-38-3 wgrant LatestPersonSourcePackageReleaseCache indices to satisfy page queries
2209-39-0 stub Convert contrib to extensions
2209-39-1 stub Drop LibraryFileAlias.last_accessed
2209-40-0 StevenK New searchable_names column on PackageUpload
2209-40-1 StevenK New searchable_versions column on PackageUpload
2209-40-2 StevenK Indices for PackageUpload.searchable_{name,version}s
2209-40-3 StevenK Set PackageUpload.searchable_{name,version}s NOT NULL.
2209-41-0 wgrant BuildFarmJob flattening -- new columns
2209-41-1 wgrant BuildFarmJob flattening -- LFA indices on new columns
2209-41-2 wgrant BuildFarmJob flattening -- fix TTB defaults
2209-41-3 wgrant BuildFarmJob flattening -- search indices
2209-41-4 wgrant BuildFarmJob flattening -- more search indices
2209-41-5 wgrant BuildFarmJob flattening -- cleanup
2209-42-0 wgrant Fix LaunchpadDatabaseRevision.end_time to default to clock_timestamp
2209-43-0 StevenK Trigram indices for Branch.{,unique_}name.
2209-44-0 StevenK New columns, PreviewDiff.{branch_merge_proposal,date_created}.
2209-44-1 StevenK Index for PreviewDiff.{branch_merge_proposal,date_created}.
2209-44-2 StevenK DROP COLUMN BranchMergeProposal.merge_diff.
2209-44-3 StevenK PreviewDiff.{branch_merge_proposal,date_created} NOT NULL.
2209-44-4 StevenK Indices for {Incremental,Preview}Diff.diff.
2209-45-0 wgrant Archive.publish_debug_symbols
2209-45-1 wgrant Archive.publish_debug_symbols NOT NULL
2209-46-0 StevenK Archive.permit_obsolete_series_uploads
2209-47-0 StevenK Job.json_data
2209-47-1 StevenK PackageDiff.requester DROP NOT NULL
2209-48-0 cjwatson Distribution.development_series_alias
2209-49-0 StevenK ArchiveArch.processor, DistroArchSeries.processor, Processor.restricted
2209-49-1 StevenK Columns in 49-0 NOT NULL
2209-49-2 StevenK DROP TABLE ProcessorFamily
2209-50-0 cjwatson Builder.version
2209-51-0 wgrant BuildFarmJobOld destruction -- new BuildQueue columns
2209-51-1 wgrant BuildFarmJobOld destruction -- drop BQ/BPJ/SPRBJ->Job FKs
2209-51-2 wgrant BuildFarmJobOld destruction -- remove old columns and tables
2209-52-0 wgrant Introduce BuilderProcessor
2209-52-1 wgrant Drop Builder.processor
2209-53-0 wgrant Index SPPH (datecreated, id) for Distribution:+ppas
2209-53-1 wgrant Replay of new 2209-00-5 for PostgreSQL 9.3 support in activity()
2209-53-2 wgrant Add Builder.satellite
2209-53-3 wgrant SPPH.packageupload and BPPH.supersededby FKs for architecture cleaning performance
2209-53-4 wgrant Drop alllocks and exclusivelocks views; their use of procpid crashes pg_upgrade to 9.3
2209-53-5 wgrant xPPH(archive, Xpackagename, status) indices for guessPublishedSourcePackageName.
2209-53-6 wgrant Index BuildQueue(status, virtualized, processor, lastscore DESC, id) for quick candidate queries
2209-53-7 wgrant Distribution.official_soyuz, enable_ppas, enable_mirrors
2209-53-8 cjwatson Index xPPH(archive, status) WHERE scheduleddeletiondate IS NULL
2209-53-9 cjwatson Index BPPH(archive, datecreated, id) for Archive.getAllPublishedBinaries
2209-54-0 cprov Add CodeReviewInlineComment[,Draft]
2209-55-0 cprov Update valid_cve() to cope with CVE format changes
2209-56-0 cjwatson Add LiveFS, LiveFSBuild, and LiveFSFile
2209-56-1 cjwatson Add NULLS LAST to LiveFS.*builds index
2209-56-2 cjwatson LiveFSBuild.version
2209-56-3 cjwatson LiveFS.relative_build_score
2209-57-0 wgrant Virtual builder reset protocol 2.0
2209-58-0 stub 64bit LFC.id - new LFC._id column + sync trigger
2209-58-1 stub 64bit LFC.id - Unique LFC._id
2209-58-2 stub 64bit LFC.id - new LFA._content column + sync trigger
2209-58-3 stub 64bit LFC.id - Index on LFA._content
2209-58-4 stub 64bit LFC.id - Finalization
2209-59-0 wgrant BinaryPackageBuild.arch_indep
2209-59-1 wgrant BinaryPackageBuild.arch_indep population (done by garbo on prod)
2209-59-2 wgrant BinaryPackageBuild.arch_indep NOT NULL
2209-60-0 cjwatson Sprint.is_physical
2209-61-0 cjwatson GitRepository, GitRef
2209-61-1 cjwatson Drop GitRepository.target_implies_owner constraint
2209-61-2 cjwatson GitRepository.description, flesh out GitRef, GitJob
2209-61-3 blr GitRepository.reviewer
2209-61-4 cjwatson GitSubscription
2209-61-5 cjwatson Add Git columns to BranchMergeProposal
2209-61-6 cjwatson GitJob.repository DROP NOT NULL
2209-61-7 cjwatson GitRepository.default_branch
2209-61-8 wgrant GitRepository indices
2209-61-9 cjwatson GitSubscription.paths
2209-62-0 wgrant BuildQueue(status, lastscore DESC, id) for _findBuildCandidate
2209-62-1 wgrant Translation search and suggestion denormalisation
2209-63-0 stub Database stat monitoring, dbr schema.
2209-64-0 wgrant Processor.{supports_{non,}virtualized,build_by_default}
2209-64-1 wgrant Backfill implicit ArchiveArch rows
2209-64-2 wgrant Drop DistroArchSeries.supports_virtualized
2209-65-0 blr Product.vcs_default
2209-66-0 wgrant Webhook model basics
2209-66-1 cjwatson Webhook.branch
2209-67-0 wgrant Drop a dozen unused tables
2209-67-1 wgrant Add Product.access_policies and index AccessPolicyGrantFlat(grantee, policy)
2209-67-2 wgrant Backfill Product.access_policies
2209-67-3 wgrant Index latest Questions, Specifications and Products
2209-68-0 cjwatson PersonSettings.expanded_notification_footers
2209-68-1 cjwatson PersonSettings.expanded_notification_footers NOT NULL
2209-68-2 cjwatson PersonSettings.require_strong_email_authentication
2209-69-0 cjwatson Add Snap, SnapBuild, and SnapFile
2209-69-1 cprov Add Snap.private
2209-69-2 cjwatson Webhook.snap
2209-69-3 cjwatson Snap store uploading
2209-69-4 cjwatson Snap auto-building
2209-69-5 cjwatson Snap.store_channels
2209-69-6 cjwatson SnappyDistroSeries.preferred
2209-69-7 cjwatson Snap.git_repository_url
2209-69-8 cjwatson SnapBuild.revision_id
2209-70-0 wgrant XRef
2209-71-0 cjwatson DistroSeries.publishing_options
2209-71-1 cjwatson Archive.dirty_suites
2209-72-0 cjwatson BinaryPackageBuild.external_dependencies
2209-73-0 cjwatson Git recipes
2209-73-1 cjwatson Add NULLS LAST to SourcePackageRecipe.*builds indexes
2209-74-0 cjwatson ArchiveFile
2209-74-1 cjwatson ArchiveFile.date_created, ArchiveFile.date_superseded
2209-75-0 wgrant GPGKey foreign key removal new columns
2209-75-1 wgrant GPGKey foreign key removal new indexes
2209-75-2 wgrant GPGKey foreign key removal
2209-76-0 cjwatson OAuth*Token support for macaroons, caveats, and nullable consumer keys
2209-77-0 wgrant Index LibraryFileAlias expiration queries
2209-77-1 wgrant GitRepository ID sort indexes and fixed *_default indexes
2209-77-2 wgrant Uniquely index PackageDiff(from_source, to_source)
2209-77-3 wgrant Drop disused tables, views and functions
2209-78-0 cjwatson Drop DistributionSourcePackageCache.changelog from fti
2209-78-1 cjwatson DistributionSourcePackageCache.archive DROP NOT NULL
2209-78-2 cjwatson Index DistributionSourcePackageCache(SPN, archive), DistributionSourcePackageCache(binpkgnames)
2209-79-0 maxiberta Add ArchiveAuthToken.name; make ArchiveAuthToken.person nullable
2209-80-0 cjwatson Git-targeted code imports
2209-80-1 cjwatson Per-target-type CodeImport(url) indexes
2209-80-2 cjwatson GitRepositoryType.repository_type NOT NULL
2209-81-0 wgrant Function compatibility for PostgreSQL 9.5 and 9.6
2209-82-0 cjwatson SourcePackageRelease.buildinfo and BinaryPackageBuild.buildinfo
2209-82-1 cjwatson SourcePackageRelease.buildinfo and BinaryPackageBuild.buildinfo indexes
2209-83-0 cjwatson Snap.allow_internet
2209-83-1 cjwatson Snap.auto_build_channels, SnapBuild.channels
2209-83-2 cjwatson Snap.build_source_tarball
2209-83-3 cjwatson SnapJob
2209-83-4 cjwatson SnapBuild.build_request
2209-83-5 twom Add SnapBuild metadata
2209-84-0 wgrant tsearch2 removal
2209-84-1 wgrant PostgreSQL 10 compatibility
2209-85-0 cjwatson GitRule, GitGrant
2209-85-1 cjwatson GitActivity
|