Launchpad itself

Code review comment for ~pappacena/launchpad:db-patch-ociproject-project-pillar

Revision history for this message

Colin Watson (cjwatson) wrote on 2020-05-13:

I would move this patch to 2210-08-8; it might as well be part of the overall OCI series, as long as we haven't yet reached 9 and had to overflow to another minor number.

There are several other things we need to add:

* Rename OCIProject.product to OCIProject.project. While we still use the term "product" in anything old (partly because there's a lot of code to change, and partly because renaming DB columns/tables is awkward), everything new uses the term "project" instead. Compare GitRepository.project.

* Add something like this to the `ALTER TABLE OCIProject` statement so that it's actually possible to create an OCIProject row based on a project:
ALTER COLUMN distribution DROP NOT NULL,
ADD CONSTRAINT one_container CHECK ((project IS NULL) != (distribution IS NULL))

* Add a unique index on `OCIProject (project, ociprojectname) WHERE project IS NOT NULL`.

* Change the GitRepository one_container constraint to allow `(project IS NOT NULL AND distribution IS NULL AND sourcepackagename IS NULL AND ociprojectname IS NOT NULL)`.

* Alter most of the GitRepository project indexes involving (project) and (owner, project) to add a `WHERE ociprojectname IS NULL` condition, and add parallel indexes for the `ociprojectname IS NOT NULL` case. This is similar to 2210-08-1 and 2210-08-2, which between them do a similar sort of operation to add (distribution, ociprojectname) alongside (distribution, sourcepackagename).
* Note that there are complexities here because creating indexes on non-trivial tables is generally too slow to be done during a fastdowntime deployment. You'll need the same kind of structure that you see in 2210-08-1 and 2210-08-2: the initial cold patch should rename the relevant existing indexes to add an `old__` prefix; the follow-up hot patch should create new indexes and drop the old ones. These must not be in the same MP; the hot patch should be proposed against master from a branch that doesn't include the cold patch. We'll need to apply the hot patch manually to production with `CONCURRENTLY` shortly after the fastdowntime, and sync up master later.

I would move this patch to 2210-08-8; it might as well be part of the overall OCI series, as long as we haven't yet reached 9 and had to overflow to another minor number.

There are several other things we need to add:

* Rename OCIProject.product to OCIProject.project.  While we still use the term "product" in anything old (partly because there's a lot of code to change, and partly because renaming DB columns/tables is awkward), everything new uses the term "project" instead.  Compare GitRepository.project.

* Add something like this to the `ALTER TABLE OCIProject` statement so that it's actually possible to create an OCIProject row based on a project:
   ALTER COLUMN distribution DROP NOT NULL,
   ADD CONSTRAINT one_container CHECK ((project IS NULL) != (distribution IS NULL))

* Add a unique index on `OCIProject (project, ociprojectname) WHERE project IS NOT NULL`.

* Change the GitRepository one_container constraint to allow `(project IS NOT NULL AND distribution IS NULL AND sourcepackagename IS NULL AND ociprojectname IS NOT NULL)`.

* Alter most of the GitRepository project indexes involving (project) and (owner, project) to add a `WHERE ociprojectname IS NULL` condition, and add parallel indexes for the `ociprojectname IS NOT NULL` case.  This is similar to 2210-08-1 and 2210-08-2, which between them do a similar sort of operation to add (distribution, ociprojectname) alongside (distribution, sourcepackagename).
  * Note that there are complexities here because creating indexes on non-trivial tables is generally too slow to be done during a fastdowntime deployment.  You'll need the same kind of structure that you see in 2210-08-1 and 2210-08-2: the initial cold patch should rename the relevant existing indexes to add an `old__` prefix; the follow-up hot patch should create new indexes and drop the old ones.  These must not be in the same MP; the hot patch should be proposed against master from a branch that doesn't include the cold patch.  We'll need to apply the hot patch manually to production with `CONCURRENTLY` shortly after the fastdowntime, and sync up master later.

review: Needs Fixing

« Back to merge proposal