launchpad-reviewers team mailing list archive

[Colin Watson <mp+414812@xxxxxxxxxxxxxxxxxx>]

Colin Watson has proposed merging ~cjwatson/launchpad:db-karmacache-id-bigint-2 into launchpad:master.

Commit message:
64-bit KarmaCache.id: unique _id

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/414812

Backfill the new _id column and then create a unique index on it.  We should be able to do both of these live.
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:db-karmacache-id-bigint-2 into launchpad:master.

diff --git a/database/schema/patch-2210-39-1.sql b/database/schema/patch-2210-39-1.sql
new file mode 100644
index 0000000..56e3113
--- /dev/null
+++ b/database/schema/patch-2210-39-1.sql
@@ -0,0 +1,14 @@
+-- Copyright 2022 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+-- STEP 2, HOT
+-- Backfill KarmaCache._id.
+UPDATE KarmaCache SET _id=id WHERE _id IS NULL;
+
+-- STEP 3, HOT
+-- To be done CONCURRENTLY, create the UNIQUE index on KarmaCache._id.
+CREATE UNIQUE INDEX karmacache_id_key ON KarmaCache(_id);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 39, 1);