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-56-4 wgrant Index LiveFSFile(libraryfile) 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-69-9 cjwatson Index SnapFile(libraryfile) 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 SnapBuild.store_upload_json_data 2209-84-0 wgrant tsearch2 removal 2209-84-1 wgrant PostgreSQL 10 compatibility 2209-85-0 cjwatson GitRule, GitGrant 2209-85-1 cjwatson GitActivity 2209-86-0 cjwatson PocketChroot.image_type 2209-87-0 abrody DistributionMirror.https_base_url