Merge ~cjwatson/launchpad:db-split-bpph-creator-index into launchpad:db-devel

[Colin Watson]

Split BPPH(creator) index into a separate DB patch

This works because the index is only needed in a few places (person
merging, team visibility changes, closing accounts, and PersonPruner in
garbo), so if it's absent for a short time while the index is created
with CONCURRENTLY then nothing goes too badly wrong.

Changing an already-committed patch shouldn't be taken as precedent. We
can get away with it in this case, but we'll need to fix things up
manually on staging.

On production, the index will have to be created separately with CONCURRENTLY, since BinaryPackagePublishingHistory is a large table.

[William Grant (wgrant)]

Do note in the commit message that this is highly evil and not precedent. But otherwise looks good.

review: Approve (db)

diff --git a/database/schema/patch-2210-11-1.sql b/database/schema/patch-2210-11-1.sql
index 9791451..57e77e9 100644
--- a/database/schema/patch-2210-11-1.sql
+++ b/database/schema/patch-2210-11-1.sql
@@ -7,9 +7,6 @@ SET client_min_messages=ERROR;
 ALTER TABLE binarypackagepublishinghistory
     ADD COLUMN creator INTEGER REFERENCES person;
 
-CREATE INDEX binarypackagepublishinghistory__creator__idx ON
-    binarypackagepublishinghistory(creator) WHERE creator IS NOT NULL;
-
 
 ALTER TABLE binarypackagepublishinghistory
     ADD COLUMN copied_from_archive INTEGER REFERENCES archive;
@@ -20,4 +17,3 @@ ALTER TABLE sourcepackagepublishinghistory
 
 
 INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 11, 1);
-
diff --git a/database/schema/patch-2210-11-2.sql b/database/schema/patch-2210-11-2.sql
 new file mode 100644
index 0000000..0fc12bc
--- /dev/null
+++ b/database/schema/patch-2210-11-2.sql
@@ -0,0 +1,9 @@
+-- Copyright 2020 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+CREATE INDEX binarypackagepublishinghistory__creator__idx ON
+    binarypackagepublishinghistory(creator) WHERE creator IS NOT NULL;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 11, 2);