launchpad-dev team mailing list archive

[Abel Deuring <abel.deuring@xxxxxxxxxxxxx>]

Hi Stuart, hi Jono,

Tom and myself started to work on sorting bugs/bugtasks by the age of
the youngest patch attachment (see
https://bugs.edge.launchpad.net/malone/+bug/506018 ,
https://bugs.edge.launchpad.net/malone/+bug/512500 ). Basically, this
means to add an option "sort by creation time of youngest patch
attachment" to IHasBugs.searchTasks().

We are a bit concerned that a regulary query, where we sort by the
youngest patch via something like

    ORDER BY (SELECT max(message.datecreated)
                FROM message, bugattachment
                WHERE bugattachment.message=message.id AND
                    bugattachment.bug=<bug.id from main query> AND
                    bugattachment.type=1)

in a query like "SELECT BugTask.whatever FROM BugTask,... WHERE...",
might easily lead to timeouts when this sorting is used for Ubuntu
bugs... (Tom wanted to write such a query for a test on staging, so we
might soon have some data.)

So we also considered to add a column date_created_youngest_patch to the
table Bug, where this column would be updated only by triggers for
changes of the table BugAttachment, like so:

=== added file 'database/schema/patch-2207-99-0.sql'
--- database/schema/patch-2207-99-0.sql 1970-01-01 00:00:00 +0000
+++ database/schema/patch-2207-99-0.sql 2010-01-28 17:00:26 +0000
@@ -0,0 +1,22 @@
+-- Copyright 2010 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 bug
+    ADD COLUMN date_created_youngest_patch timestamp without time zone
+        DEFAULT NULL;
+
+CREATE INDEX bug__date_created_youngest_patch__idx
+    ON bug(date_created_youngest_patch);
+
+CREATE TRIGGER bug_update_patch_age_on_insert_update_t
+AFTER INSERT OR UPDATE ON bugattachment
+FOR EACH ROW EXECUTE PROCEDURE bug_update_patch_age_on_insert_update();
+
+CREATE TRIGGER bug_update_patch_age_on_delete_t
+AFTER DELETE ON bugattachment
+FOR EACH ROW EXECUTE PROCEDURE bug_update_patch_age_on_delete();
+
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 99, 0);

=== modified file 'database/schema/trusted.sql'
--- database/schema/trusted.sql 2009-11-30 11:48:01 +0000
+++ database/schema/trusted.sql 2010-01-28 16:55:05 +0000
@@ -1423,3 +1423,36 @@
 $$;


+CREATE OR REPLACE FUNCTION bug_update_patch_age(integer) RETURNS VOID
+    SECURITY DEFINER LANGUAGE plpgsql AS
+    $$
+    BEGIN
+        UPDATE bug SET date_created_youngest_patch =
+            (SELECT max(message.datecreated)
+                FROM message, bugattachment
+                WHERE bugattachment.message=message.id AND
+                    bugattachment.bug=$1 AND
+                    bugattachment.type=1)
+            WHERE bug.id=$1;
+    END;
+    $$;
+
+
+CREATE OR REPLACE FUNCTION bug_update_patch_age_on_insert_update()
RETURNS trigger
+    SECURITY DEFINER LANGUAGE plpgsql AS
+    $$
+    BEGIN
+        PERFORM bug_update_patch_age(NEW.bug);
+        RETURN NULL; -- Ignored - this is an AFTER trigger
+    END;
+    $$;
+
+
+CREATE OR REPLACE FUNCTION bug_update_patch_age_on_delete() RETURNS trigger
+    SECURITY DEFINER LANGUAGE plpgsql AS
+    $$
+    BEGIN
+        PERFORM bug_update_patch_age(OLD.bug);
+        RETURN NULL; -- Ignored - this is an AFTER trigger
+    END;
+    $$;

In other words, a deliberate denormalisation. What do you think, should
we start with a plain query without such a denormalsation, or do you
think that it is better to add the "cache column"
date_created_youngest_patch to the Bug table?

Abel