Andrey Fedoseev has proposed merging ~andrey-fedoseev/launchpad:db-bug-task-channel into launchpad:db-devel.
Commit message: Add `channel` field to `BugTask` and its derived tables/views Requested reviews: Launchpad code reviewers (launchpad-reviewers) For more details, see: https://code.launchpad.net/~andrey-fedoseev/launchpad/+git/launchpad/+merge/434686 -- Your team Launchpad code reviewers is requested to review the proposed merge of ~andrey-fedoseev/launchpad:db-bug-task-channel into launchpad:db-devel.
diff --git a/database/schema/patch-2211-14-0.sql b/database/schema/patch-2211-14-0.sql new file mode 100644 index 0000000..719d4dd --- /dev/null +++ b/database/schema/patch-2211-14-0.sql @@ -0,0 +1,542 @@ +-- 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; + +ALTER TABLE BugTask + ADD COLUMN channel jsonb; + +ALTER INDEX bugtask_distinct_sourcepackage_assignment + RENAME TO old__bugtask_distinct_sourcepackage_assignment; + +ALTER TABLE BugTaskFlat + ADD COLUMN channel jsonb; + +ALTER TABLE BugSummary + ADD COLUMN channel jsonb, + DROP CONSTRAINT bugtask_assignment_checks, + ADD CONSTRAINT bugtask_assignment_checks CHECK ( + CASE + WHEN product IS NOT NULL THEN + productseries IS NULL + AND distribution IS NULL + AND distroseries IS NULL + AND sourcepackagename IS NULL + WHEN productseries IS NOT NULL THEN + distribution IS NULL + AND distroseries IS NULL + AND sourcepackagename IS NULL + AND ociproject IS NULL + AND ociprojectseries IS NULL + WHEN distribution IS NOT NULL THEN + distroseries IS NULL + WHEN distroseries IS NOT NULL THEN + ociproject IS NULL + AND ociprojectseries IS NULL + WHEN ociproject IS NOT NULL THEN + ociprojectseries IS NULL + AND (distribution IS NOT NULL OR product IS NOT NULL) + AND sourcepackagename IS NULL + WHEN ociprojectseries IS NOT NULL THEN + ociproject IS NULL + AND (distribution IS NOT NULL OR product IS NOT NULL) + AND sourcepackagename IS NULL + WHEN channel IS NOT NULL THEN + distroseries IS NOT NULL + AND sourcepackagename IS NOT NULL + ELSE false + END) NOT VALID; + +ALTER INDEX bugsummary__unique + RENAME TO old__bugsummary__unique; + + +ALTER TABLE BugSummaryJournal ADD COLUMN channel jsonb; + +ALTER INDEX bugsummaryjournal__full__idx + RENAME TO old__bugsummaryjournal__full__idx; + + +-- Functions + +CREATE OR REPLACE FUNCTION bugtask_flatten(task_id integer, check_only boolean) + RETURNS boolean + SECURITY DEFINER + SET search_path = public + LANGUAGE plpgsql + AS $$ +DECLARE + bug_row Bug%ROWTYPE; + task_row BugTask%ROWTYPE; + old_flat_row BugTaskFlat%ROWTYPE; + new_flat_row BugTaskFlat%ROWTYPE; + _product_active boolean; + _access_policies integer[]; + _access_grants integer[]; +BEGIN + -- This is the master function to update BugTaskFlat, but there are + -- maintenance triggers and jobs on the involved tables that update + -- it directly. Any changes here probably require a corresponding + -- change in other trigger functions. + + SELECT * INTO task_row FROM BugTask WHERE id = task_id; + SELECT * INTO old_flat_row FROM BugTaskFlat WHERE bugtask = task_id; + + -- If the task doesn't exist, ensure that there's no flat row. + IF task_row.id IS NULL THEN + IF old_flat_row.bugtask IS NOT NULL THEN + IF NOT check_only THEN + DELETE FROM BugTaskFlat WHERE bugtask = task_id; + END IF; + RETURN FALSE; + ELSE + RETURN TRUE; + END IF; + END IF; + + SELECT * INTO bug_row FROM bug WHERE id = task_row.bug; + + -- If it's a product(series) task, we must consult the active flag. + IF task_row.product IS NOT NULL THEN + SELECT product.active INTO _product_active + FROM product WHERE product.id = task_row.product LIMIT 1; + ELSIF task_row.productseries IS NOT NULL THEN + SELECT product.active INTO _product_active + FROM + product + JOIN productseries ON productseries.product = product.id + WHERE productseries.id = task_row.productseries LIMIT 1; + END IF; + + SELECT policies, grants + INTO _access_policies, _access_grants + FROM bug_build_access_cache(bug_row.id, bug_row.information_type) + AS (policies integer[], grants integer[]); + + -- Compile the new flat row. + SELECT task_row.id, bug_row.id, task_row.datecreated, + bug_row.duplicateof, bug_row.owner, bug_row.fti, + bug_row.information_type, bug_row.date_last_updated, + bug_row.heat, task_row.product, task_row.productseries, + task_row.distribution, task_row.distroseries, + task_row.sourcepackagename, task_row.status, + task_row.importance, task_row.assignee, + task_row.milestone, task_row.owner, + COALESCE(_product_active, TRUE), + _access_policies, + _access_grants, + bug_row.latest_patch_uploaded, task_row.date_closed, + task_row.ociproject, task_row.ociprojectseries, + task_row.channel + INTO new_flat_row; + + -- Calculate the necessary updates. + IF old_flat_row.bugtask IS NULL THEN + IF NOT check_only THEN + INSERT INTO BugTaskFlat VALUES (new_flat_row.*); + END IF; + RETURN FALSE; + ELSIF new_flat_row != old_flat_row THEN + IF NOT check_only THEN + UPDATE BugTaskFlat SET + bug = new_flat_row.bug, + datecreated = new_flat_row.datecreated, + duplicateof = new_flat_row.duplicateof, + bug_owner = new_flat_row.bug_owner, + fti = new_flat_row.fti, + information_type = new_flat_row.information_type, + date_last_updated = new_flat_row.date_last_updated, + heat = new_flat_row.heat, + product = new_flat_row.product, + productseries = new_flat_row.productseries, + distribution = new_flat_row.distribution, + distroseries = new_flat_row.distroseries, + sourcepackagename = new_flat_row.sourcepackagename, + status = new_flat_row.status, + importance = new_flat_row.importance, + assignee = new_flat_row.assignee, + milestone = new_flat_row.milestone, + owner = new_flat_row.owner, + active = new_flat_row.active, + access_policies = new_flat_row.access_policies, + access_grants = new_flat_row.access_grants, + date_closed = new_flat_row.date_closed, + latest_patch_uploaded = new_flat_row.latest_patch_uploaded, + ociproject = new_flat_row.ociproject, + ociprojectseries = new_flat_row.ociprojectseries, + channel = new_flat_row.channel + WHERE bugtask = new_flat_row.bugtask; + END IF; + RETURN FALSE; + ELSE + RETURN TRUE; + END IF; +END; +$$; + +CREATE OR REPLACE FUNCTION bug_summary_inc(d bugsummary) + RETURNS VOID + LANGUAGE plpgsql + AS $$ +BEGIN + -- Shameless adaption from postgresql manual + LOOP + -- first try to update the row + UPDATE BugSummary SET count = count + d.count + WHERE + product IS NOT DISTINCT FROM $1.product + AND productseries IS NOT DISTINCT FROM $1.productseries + AND distribution IS NOT DISTINCT FROM $1.distribution + AND distroseries IS NOT DISTINCT FROM $1.distroseries + AND sourcepackagename IS NOT DISTINCT FROM $1.sourcepackagename + AND ociproject IS NOT DISTINCT FROM $1.ociproject + AND ociprojectseries IS NOT DISTINCT FROM $1.ociprojectseries + AND channel IS NOT DISTINCT FROM $1.channel + AND viewed_by IS NOT DISTINCT FROM $1.viewed_by + AND tag IS NOT DISTINCT FROM $1.tag + AND status = $1.status + AND ((milestone IS NULL AND $1.milestone IS NULL) + OR milestone = $1.milestone) + AND importance = $1.importance + AND has_patch = $1.has_patch + AND access_policy IS NOT DISTINCT FROM $1.access_policy; + IF found THEN + RETURN; + END IF; + -- not there, so try to insert the key + -- if someone else inserts the same key concurrently, + -- we could get a unique-key failure + BEGIN + INSERT INTO BugSummary( + count, product, productseries, distribution, + distroseries, sourcepackagename, + ociproject, ociprojectseries, channel, + viewed_by, tag, + status, milestone, importance, has_patch, access_policy) + VALUES ( + d.count, d.product, d.productseries, d.distribution, + d.distroseries, d.sourcepackagename, + d.ociproject, d.ociprojectseries, d.channel, + d.viewed_by, d.tag, + d.status, d.milestone, d.importance, d.has_patch, + d.access_policy); + RETURN; + EXCEPTION WHEN unique_violation THEN + -- do nothing, and loop to try the UPDATE again + END; + END LOOP; +END; +$$; + +CREATE OR REPLACE FUNCTION bug_summary_dec(bugsummary) + RETURNS VOID + LANGUAGE sql + AS $$ + -- We own the row reference, so in the absence of bugs this cannot + -- fail - just decrement the row. + UPDATE BugSummary SET count = count + $1.count + WHERE + ((product IS NULL AND $1.product IS NULL) + OR product = $1.product) + AND ((productseries IS NULL AND $1.productseries IS NULL) + OR productseries = $1.productseries) + AND ((distribution IS NULL AND $1.distribution IS NULL) + OR distribution = $1.distribution) + AND ((distroseries IS NULL AND $1.distroseries IS NULL) + OR distroseries = $1.distroseries) + AND ((sourcepackagename IS NULL AND $1.sourcepackagename IS NULL) + OR sourcepackagename = $1.sourcepackagename) + AND ((ociproject IS NULL AND $1.ociproject IS NULL) + OR ociproject = $1.ociproject) + AND ((ociprojectseries IS NULL AND $1.ociprojectseries IS NULL) + OR ociprojectseries = $1.ociprojectseries) + AND ((channel IS NULL AND $1.channel IS NULL) + OR channel = $1.channel) + AND ((viewed_by IS NULL AND $1.viewed_by IS NULL) + OR viewed_by = $1.viewed_by) + AND ((tag IS NULL AND $1.tag IS NULL) + OR tag = $1.tag) + AND status = $1.status + AND ((milestone IS NULL AND $1.milestone IS NULL) + OR milestone = $1.milestone) + AND importance = $1.importance + AND has_patch = $1.has_patch + AND access_policy IS NOT DISTINCT FROM $1.access_policy; +$$; + +CREATE OR REPLACE FUNCTION bugsummary_rollup_journal(batchsize integer DEFAULT NULL::integer) + RETURNS VOID + SECURITY DEFINER + SET search_path = public + LANGUAGE plpgsql + AS $$ +DECLARE + d bugsummary%ROWTYPE; + max_id integer; +BEGIN + -- Lock so we don't content with other invocations of this + -- function. We can happily lock the BugSummary table for writes + -- as this function is the only thing that updates that table. + -- BugSummaryJournal remains unlocked so nothing should be blocked. + LOCK TABLE BugSummary IN ROW EXCLUSIVE MODE; + + IF batchsize IS NULL THEN + SELECT MAX(id) INTO max_id FROM BugSummaryJournal; + ELSE + SELECT MAX(id) INTO max_id FROM ( + SELECT id FROM BugSummaryJournal ORDER BY id LIMIT batchsize + ) AS Whatever; + END IF; + + FOR d IN + SELECT + NULL as id, + SUM(count), + product, + productseries, + distribution, + distroseries, + sourcepackagename, + viewed_by, + tag, + status, + milestone, + importance, + has_patch, + access_policy, + ociproject, + ociprojectseries, + channel + FROM BugSummaryJournal + WHERE id <= max_id + GROUP BY + product, productseries, distribution, distroseries, + sourcepackagename, ociproject, ociprojectseries, channel, + viewed_by, tag, status, milestone, + importance, has_patch, access_policy + HAVING sum(count) <> 0 + LOOP + IF d.count < 0 THEN + PERFORM bug_summary_dec(d); + ELSIF d.count > 0 THEN + PERFORM bug_summary_inc(d); + END IF; + END LOOP; + + -- Clean out any counts we reduced to 0. + DELETE FROM BugSummary WHERE count=0; + -- Clean out the journal entries we have handled. + DELETE FROM BugSummaryJournal WHERE id <= max_id; +END; +$$; + +CREATE OR REPLACE FUNCTION bugtask_maintain_bugtaskflat_trig() + RETURNS TRIGGER + SECURITY DEFINER + SET search_path = public + LANGUAGE plpgsql + AS $$ +BEGIN + IF TG_OP = 'INSERT' THEN + PERFORM bugtask_flatten(NEW.id, FALSE); + ELSIF TG_OP = 'UPDATE' THEN + IF NEW.bug != OLD.bug THEN + RAISE EXCEPTION 'cannot move bugtask to a different bug'; + ELSIF (NEW.product IS DISTINCT FROM OLD.product + OR NEW.productseries IS DISTINCT FROM OLD.productseries) THEN + -- product.active may differ. Do a full update. + PERFORM bugtask_flatten(NEW.id, FALSE); + ELSIF ( + NEW.datecreated IS DISTINCT FROM OLD.datecreated + OR NEW.product IS DISTINCT FROM OLD.product + OR NEW.productseries IS DISTINCT FROM OLD.productseries + OR NEW.distribution IS DISTINCT FROM OLD.distribution + OR NEW.distroseries IS DISTINCT FROM OLD.distroseries + OR NEW.sourcepackagename IS DISTINCT FROM OLD.sourcepackagename + OR NEW.ociproject IS DISTINCT FROM OLD.ociproject + OR NEW.ociprojectseries IS DISTINCT FROM OLD.ociprojectseries + OR NEW.channel IS DISTINCT FROM OLD.channel + OR NEW.status IS DISTINCT FROM OLD.status + OR NEW.importance IS DISTINCT FROM OLD.importance + OR NEW.assignee IS DISTINCT FROM OLD.assignee + OR NEW.milestone IS DISTINCT FROM OLD.milestone + OR NEW.owner IS DISTINCT FROM OLD.owner + OR NEW.date_closed IS DISTINCT FROM OLD.date_closed) THEN + -- Otherwise just update the columns from bugtask. + -- Access policies and grants may have changed due to target + -- transitions, but an earlier trigger will already have + -- mirrored them to all relevant flat tasks. + UPDATE BugTaskFlat SET + datecreated = NEW.datecreated, + product = NEW.product, + productseries = NEW.productseries, + distribution = NEW.distribution, + distroseries = NEW.distroseries, + sourcepackagename = NEW.sourcepackagename, + ociproject = NEW.ociproject, + ociprojectseries = NEW.ociprojectseries, + channel = NEW.channel, + status = NEW.status, + importance = NEW.importance, + assignee = NEW.assignee, + milestone = NEW.milestone, + owner = NEW.owner, + date_closed = NEW.date_closed + WHERE bugtask = NEW.id; + END IF; + ELSIF TG_OP = 'DELETE' THEN + PERFORM bugtask_flatten(OLD.id, FALSE); + END IF; + RETURN NULL; +END; +$$; + +DROP FUNCTION bugsummary_targets; +CREATE FUNCTION bugsummary_targets(btf_row bugtaskflat) + RETURNS TABLE( + product integer, + productseries integer, + distribution integer, + distroseries integer, + sourcepackagename integer, + ociproject integer, + ociprojectseries integer, + channel jsonb + ) + IMMUTABLE + LANGUAGE sql + AS $$ + -- Include a sourcepackagename-free/ociproject(series)-free task if this + -- one has a sourcepackagename/ociproject(series), so package tasks are + -- also counted in their distro/series. + SELECT + $1.product, $1.productseries, $1.distribution, + $1.distroseries, $1.sourcepackagename, + $1.ociproject, $1.ociprojectseries, $1.channel + UNION -- Implicit DISTINCT + SELECT + $1.product, $1.productseries, $1.distribution, + $1.distroseries, NULL, NULL, NULL, NULL; +$$; + +CREATE OR REPLACE FUNCTION bugsummary_locations(btf_row bugtaskflat, tags text[]) + RETURNS SETOF bugsummaryjournal + LANGUAGE plpgsql + AS $$ +BEGIN + IF btf_row.duplicateof IS NOT NULL THEN + RETURN; + END IF; + RETURN QUERY + SELECT + CAST(NULL AS integer) AS id, + CAST(1 AS integer) AS count, + bug_targets.product, bug_targets.productseries, + bug_targets.distribution, bug_targets.distroseries, + bug_targets.sourcepackagename, + bug_viewers.viewed_by, bug_tags.tag, btf_row.status, + btf_row.milestone, btf_row.importance, + btf_row.latest_patch_uploaded IS NOT NULL AS has_patch, + bug_viewers.access_policy, + bug_targets.ociproject, bug_targets.ociprojectseries, + bug_targets.channel + FROM + bugsummary_targets(btf_row) AS bug_targets, + unnest(tags) AS bug_tags (tag), + bugsummary_viewers(btf_row) AS bug_viewers; +END; +$$; + +CREATE OR REPLACE FUNCTION bugsummary_insert_journals(journals bugsummaryjournal[]) + RETURNS VOID + LANGUAGE sql + AS $$ + -- We sum the rows here to minimise the number of inserts into the + -- journal, as in the case of UPDATE statement we may have -1s and +1s + -- cancelling each other out. + INSERT INTO BugSummaryJournal( + count, product, productseries, distribution, distroseries, + sourcepackagename, ociproject, ociprojectseries, channel, + viewed_by, tag, status, milestone, importance, has_patch, + access_policy) + SELECT + SUM(count), product, productseries, distribution, distroseries, + sourcepackagename, ociproject, ociprojectseries, channel, + viewed_by, tag, status, milestone, importance, has_patch, + access_policy + FROM unnest(journals) + GROUP BY + product, productseries, distribution, distroseries, + sourcepackagename, ociproject, ociprojectseries, channel, + viewed_by, tag, status, milestone, importance, has_patch, + access_policy + HAVING SUM(count) != 0; +$$; + +-- Views + +-- Combined view so we don't have to manually collate rows from both tables. +-- Note that we flip the sign of the id column of BugSummaryJournal to avoid +-- clashes. This is enough to keep Storm happy as it never needs to update +-- this table, and there are no other suitable primary keys. +-- We don't SUM() rows here to ensure PostgreSQL has the most hope of +-- generating good query plans when we query this view. +CREATE OR REPLACE VIEW CombinedBugSummary ( + id, + count, + product, + productseries, + distribution, + distroseries, + sourcepackagename, + viewed_by, + tag, + status, + milestone, + importance, + has_patch, + access_policy, + ociproject, + ociprojectseries, + channel +) +AS +SELECT id, + count, + product, + productseries, + distribution, + distroseries, + sourcepackagename, + viewed_by, + tag, + status, + milestone, + importance, + has_patch, + access_policy, + ociproject, + ociprojectseries, + channel +FROM bugsummary +UNION ALL +SELECT -id AS id, + count, + product, + productseries, + distribution, + distroseries, + sourcepackagename, + viewed_by, + tag, + status, + milestone, + importance, + has_patch, + access_policy, + ociproject, + ociprojectseries, + channel +FROM bugsummaryjournal; + +INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 14, 0);
_______________________________________________ Mailing list: https://launchpad.net/~launchpad-reviewers Post to : launchpad-reviewers@lists.launchpad.net Unsubscribe : https://launchpad.net/~launchpad-reviewers More help : https://help.launchpad.net/ListHelp
