launchpad-reviewers team mailing list archive

[Enrique Sánchez <mp+488266@xxxxxxxxxxxxxxxxxx>]

Enrique Sánchez has proposed merging ~enriqueesanchz/launchpad:db-bugtask-packagetype-channel-repositories-hot into launchpad:master with ~enriqueesanchz/launchpad:db-bugtask-packagetype-channel-repositories as a prerequisite.

Commit message:
Drop bugtask indexes and create new ones
    
This is a follow-up patch for patch-2211-43-0.sql
Creating indexes for bugtask, bugsummary and bugsummaryjournal

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~enriqueesanchz/launchpad/+git/launchpad/+merge/488266
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~enriqueesanchz/launchpad:db-bugtask-packagetype-channel-repositories-hot into launchpad:master.

diff --git a/database/schema/patch-2211-43-1.sql b/database/schema/patch-2211-43-1.sql
new file mode 100644
index 0000000..253e94c
--- /dev/null
+++ b/database/schema/patch-2211-43-1.sql
@@ -0,0 +1,64 @@
+-- Copyright 2025 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+-- Replacing previously renamed indexes.
+CREATE UNIQUE INDEX bugtask_distinct_sourcepackage_assignment
+    ON bugtask (
+        bug,
+        COALESCE(sourcepackagename, '-1'::integer),
+        COALESCE(distroseries, '-1'::integer),
+        COALESCE(distribution, '-1'::integer),
+        COALESCE(packagetype, '-1'::integer),
+        COALESCE(channel, '{}'::jsonb)
+    )
+    WHERE (
+        product IS NULL
+        AND productseries IS NULL
+        AND ociproject IS NULL
+        AND ociprojectseries IS NULL
+    );
+DROP INDEX old__bugtask_distinct_sourcepackage_assignment;
+
+
+CREATE UNIQUE INDEX bugsummary__unique
+    ON bugsummary (
+        COALESCE(product, '-1'::integer),
+        COALESCE(productseries, '-1'::integer),
+        COALESCE(distribution, '-1'::integer),
+        COALESCE(distroseries, '-1'::integer),
+        COALESCE(sourcepackagename, '-1'::integer),
+        COALESCE(ociproject, '-1'::integer),
+        COALESCE(ociprojectseries, '-1'::integer),
+        COALESCE(packagetype, '-1'::integer),
+        COALESCE(channel, '{}'::jsonb),
+        status,
+        importance,
+        has_patch,
+        COALESCE(tag, ''::text),
+        COALESCE(milestone, '-1'::integer),
+        COALESCE(viewed_by, '-1'::integer),
+        COALESCE(access_policy, '-1'::integer)
+);
+DROP INDEX old__bugsummary__unique;
+
+CREATE INDEX bugsummaryjournal__full__idx
+    ON bugsummaryjournal (
+        status,
+        product,
+        productseries,
+        distribution,
+        distroseries,
+        sourcepackagename,
+        ociproject,
+        ociprojectseries,
+        packagetype,
+        channel,
+        viewed_by,
+        milestone,
+        tag
+    );
+DROP INDEX old__bugsummaryjournal__full__idx;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 43, 1);