launchpad-reviewers team mailing list archive

[Colin Watson <mp+417195@xxxxxxxxxxxxxxxxxx>]

Colin Watson has proposed merging ~cjwatson/launchpad:db-column-specific-triggers into launchpad:db-devel.

Commit message:
Recreate many triggers to be column-specific

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

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

I happened to notice that adding columns to the `Archive` table took much longer than it should, which seems to be because it has a trigger on all `UPDATE`s even though it only cares about two of its columns.  On closer inspection quite a number of our triggers have a similar property.  PostgreSQL supports making triggers be column-specific on `UPDATE`, so do that in cases where the trigger only cares about a small number of columns.

The `mv_branch_*_update` trigger functions also check whether `id` has changed and explicitly raise an exception if so, but I ignored that because (a) it's silly and (b) any case where it might somehow happen and matter will violate foreign key constraints anyway.
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:db-column-specific-triggers into launchpad:db-devel.

diff --git a/database/schema/patch-2210-06-1.sql b/database/schema/patch-2210-06-1.sql
new file mode 100644
index 0000000..5fa71a2
--- /dev/null
+++ b/database/schema/patch-2210-06-1.sql
@@ -0,0 +1,140 @@
+-- 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;
+
+-- We have a number of triggers called on UPDATEs of relatively wide rows
+-- that only need to trigger on changes to a small number of columns.  Make
+-- those triggers column-specific.
+
+DROP TRIGGER mv_branch_distribution_update_t ON distribution;
+CREATE TRIGGER mv_branch_distribution_update_t
+    AFTER UPDATE OF name ON distribution
+    FOR EACH ROW EXECUTE PROCEDURE mv_branch_distribution_update();
+
+DROP TRIGGER mv_branch_distroseries_update_t ON distroseries;
+CREATE TRIGGER mv_branch_distroseries_update_t
+    AFTER UPDATE OF name ON distroseries
+    FOR EACH ROW EXECUTE PROCEDURE mv_branch_distroseries_update();
+
+DROP TRIGGER mv_branch_person_update_t ON person;
+CREATE TRIGGER mv_branch_person_update_t
+    AFTER UPDATE OF name ON person
+    FOR EACH ROW EXECUTE PROCEDURE mv_branch_person_update();
+
+DROP TRIGGER mv_branch_product_update_t ON product;
+CREATE TRIGGER mv_branch_product_update_t
+    AFTER UPDATE OF name ON product
+    FOR EACH ROW EXECUTE PROCEDURE mv_branch_product_update();
+
+DROP TRIGGER mv_pillarname_distribution_t ON distribution;
+CREATE TRIGGER mv_pillarname_distribution_t
+    AFTER INSERT OR UPDATE OF name ON distribution
+    FOR EACH ROW EXECUTE PROCEDURE mv_pillarname_distribution();
+
+DROP TRIGGER mv_pillarname_product_t ON product;
+CREATE TRIGGER mv_pillarname_product_t
+    AFTER INSERT OR UPDATE OF name, active ON product
+    FOR EACH ROW EXECUTE PROCEDURE mv_pillarname_product();
+
+DROP TRIGGER mv_pillarname_project_t ON project;
+CREATE TRIGGER mv_pillarname_project_t
+    AFTER INSERT OR UPDATE OF name, active ON project
+    FOR EACH ROW EXECUTE PROCEDURE mv_pillarname_project();
+
+DROP TRIGGER set_bug_number_of_duplicates_t ON bug;
+CREATE TRIGGER set_bug_number_of_duplicates_t
+    AFTER INSERT OR DELETE OR UPDATE OF duplicateof ON bug
+    FOR EACH ROW EXECUTE PROCEDURE set_bug_number_of_duplicates();
+
+DROP TRIGGER set_bugtask_date_milestone_set_t ON bugtask;
+CREATE TRIGGER set_bugtask_date_milestone_set_t
+    AFTER INSERT OR UPDATE OF milestone ON bugtask
+    FOR EACH ROW EXECUTE PROCEDURE set_bugtask_date_milestone_set();
+
+DROP TRIGGER set_date_status_set_t ON account;
+CREATE TRIGGER set_date_status_set_t
+    BEFORE UPDATE OF status ON account
+    FOR EACH ROW EXECUTE PROCEDURE set_date_status_set();
+
+DROP TRIGGER tsvectorupdate ON binarypackagerelease;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF summary, description ON binarypackagerelease
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('summary', 'b', 'description', 'c');
+
+DROP TRIGGER tsvectorupdate ON cve;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF sequence, description ON cve
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('sequence', 'a', 'description', 'b');
+
+DROP TRIGGER tsvectorupdate ON distroseriespackagecache;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF name, summaries, descriptions ON distroseriespackagecache
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('name', 'a', 'summaries', 'b', 'descriptions', 'c');
+
+DROP TRIGGER tsvectorupdate ON message;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF subject ON message
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('subject', 'b');
+
+DROP TRIGGER tsvectorupdate ON messagechunk;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF content ON messagechunk
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('content', 'c');
+
+DROP TRIGGER tsvectorupdate ON product;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF name, displayname, title, summary, description ON product
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('name', 'a', 'displayname', 'a', 'title', 'b', 'summary', 'c', 'description', 'd');
+
+DROP TRIGGER tsvectorupdate ON project;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF name, displayname, title, summary, description ON project
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('name', 'a', 'displayname', 'a', 'title', 'b', 'summary', 'c', 'description', 'd');
+
+DROP TRIGGER tsvectorupdate ON question;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF title, description, whiteboard ON question
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('title', 'a', 'description', 'b', 'whiteboard', 'b');
+
+DROP TRIGGER tsvectorupdate ON bug;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF name, title, description ON bug
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('name', 'a', 'title', 'b', 'description', 'd');
+
+DROP TRIGGER tsvectorupdate ON person;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF name, displayname ON person
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('name', 'a', 'displayname', 'a');
+
+DROP TRIGGER tsvectorupdate ON specification;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF name, title, summary, whiteboard ON specification
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('name', 'a', 'title', 'a', 'summary', 'b', 'whiteboard', 'd');
+
+DROP TRIGGER tsvectorupdate ON distribution;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF name, displayname, title, summary, description ON distribution
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('name', 'a', 'displayname', 'a', 'title', 'b', 'summary', 'c', 'description', 'd');
+
+DROP TRIGGER tsvectorupdate ON productreleasefile;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF description ON productreleasefile
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('description', 'd');
+
+DROP TRIGGER tsvectorupdate ON faq;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF title, tags, content ON faq
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('title', 'a', 'tags', 'b', 'content', 'd');
+
+DROP TRIGGER tsvectorupdate ON archive;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF description, package_description_cache ON archive
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('description', 'a', 'package_description_cache', 'b');
+
+DROP TRIGGER tsvectorupdate ON distributionsourcepackagecache;
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE OF name, binpkgnames, binpkgsummaries, binpkgdescriptions ON distributionsourcepackagecache
+    FOR EACH ROW EXECUTE PROCEDURE ftiupdate('name', 'a', 'binpkgnames', 'b', 'binpkgsummaries', 'c', 'binpkgdescriptions', 'd');
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 6, 1);