Merge lp:~wgrant/launchpad/bugtaskflat-db into lp:launchpad/db-devel

[William Grant]

Create BugTaskFlat, a flattened version of Bug, BugTask, and some other bits and pieces.

Bug filtering and sorting is split awkwardly across Bug and BugTask, causing fairly universally terrible performance. Further, access checks extend to joins on BugSubscription. Experiments have shown that putting most of the sorting and filtering criteria into a single table improves speed vastly.

This branch introduces a new table, BugTaskFlat, comprising relevant fields from Bug and BugTask, Product.active and arrays of relevant records from AccessPolicyArtifact and AccessArtifactGrant. This allows most bug searches (including access checks) to be answered from a simple scan down a sort index, filtering inline as the records are retrieved. More selective queries can be optimised by bitmap index operations once we work out what indexes are interesting.

The table is maintained by a fairly impressive array of triggers and helper functions, but there is also a master function which is the canonical definition of BugTaskFlat and can correct rows to match reality. So the table is fairly disposable and easy to regenerate if we need to for any reason. There are reasonably thorough tests of these triggers.

The triggers have one hole: Product.active. Because products can have hilariously large numbers of bugs, updating BugTaskFlat.active inside a request is infeasible. It will probably be updated by a job later.

I've omitted foreign keys from the table. This seems to be a significant performance enhancement, and is fine from an integrity perspective because the table is only updated by triggers from FKed tables, and is disposable and cheap to regenerate if things end up going wrong. I also make use of arrays which can't be FKed in current PostgreSQL releases.

[William Grant (wgrant)]

I suspect that a later iteration will include a tag array in some form, but with PostgreSQL 8.4 it gets a bit awkward, and it needs lots of testing to see which method is best (an array of strings, or an array of integers as interned strings, a fake tsvector, etc.). This is a good start.

Also, access_policies and access_grants are null if the bug is public, as they don't really make sense. When the bug is private they're always (possibly empty) arrays.

[Stuart Bishop (stub)]

I guess we need a bug files about the Product.active hole. I agree it can't be done with triggers (well... we could schedule a celary job from a trigger).

Without foreign key references to the Person table, all those references will need special casing in the people merge code. We probably want a bug tracking this too, as we will need to rebuild BugTaskFlat after adding that code. Or perhaps the job that handles Product.active will maintain this too?

=== added file 'database/schema/patch-2209-16-0.sql'

+CREATE INDEX bugtaskflat__bugtask__idx ON BugTaskFlat USING btree (bugtask);

This index is redundant - a UNIQUE index gets created on bugtask with the PRIMARY KEY.

+CREATE INDEX

Lots of other indexes for searching and reporting. I'm not going to second guess which columns should be DESC or not, and that things are structured the way they are to support the queries you need. My eyeballs don't pick up any naming errors or cut'n'paste glitches.

+ ELSIF new_flat_row != old_flat_row THEN

I think this comparison will fail if the rows are identical except for the order of the elements in the access_policies and access_grants arrays. A spurious update will be harmless here, but the return value will be incorrect. I think we need to guarantee the access cache helpers return the elements in a stable order.

=== added file 'lib/lp/bugs/tests/test_bugtaskflat_triggers.py'

I haven't reviewed the tests. I can do the code portion later if nobody beats me too it.

review: Approve (db)

[Richard Harding (rharding)]

Thanks William. From a code standpoint this looks ok. It's hard to say that all cases and corners are covered but it appears very well thought out and run through. I'd definitely expect the integration parts in the future exercising the current tests around the actual searching to help find any large holes in moving to this new flattened table setup.

review: Approve (code*)

[William Grant (wgrant)]

On 29/03/12 23:48, Stuart Bishop wrote:
> Review: Approve db
>
> I guess we need a bug files about the Product.active hole. I agree it
> can't be done with triggers (well... we could schedule a celary job
> from a trigger).
>
> Without foreign key references to the Person table, all those
> references will need special casing in the people merge code. We
> probably want a bug tracking this too, as we will need to rebuild
> BugTaskFlat after adding that code. Or perhaps the job that handles
> Product.active will maintain this too?

No need for special-casing -- person merging will update the FKs on the
base tables, and the usual triggers will update BugTaskFlat. Person
merging doesn't need to know it exists.

> === added file 'database/schema/patch-2209-16-0.sql'
>
>
> +CREATE INDEX bugtaskflat__bugtask__idx ON BugTaskFlat USING btree
> (bugtask);
>
> This index is redundant - a UNIQUE index gets created on bugtask with
> the PRIMARY KEY.

Good point.

> +CREATE INDEX
>
> Lots of other indexes for searching and reporting. I'm not going to
> second guess which columns should be DESC or not, and that things are
> structured the way they are to support the queries you need. My
> eyeballs don't pick up any naming errors or cut'n'paste glitches.
>
>
> + ELSIF new_flat_row != old_flat_row THEN
>
> I think this comparison will fail if the rows are identical except
> for the order of the elements in the access_policies and
> access_grants arrays. A spurious update will be harmless here, but
> the return value will be incorrect. I think we need to guarantee the
> access cache helpers return the elements in a stable order.

Indeed. I'll add an order.

> === added file 'lib/lp/bugs/tests/test_bugtaskflat_triggers.py'
>
> I haven't reviewed the tests. I can do the code portion later if
> nobody beats me too it.
>

[j.c.sackett (jcsackett)]

The tests seem pretty solid--everything I would worry about seems covered. This looks fine to land from my perspective.

review: Approve (code)

=== added file 'database/schema/patch-2209-16-0.sql'
--- database/schema/patch-2209-16-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-16-0.sql	2012-03-30 00:02:34 +0000
@@ -0,0 +1,546 @@
+-- Copyright 2012 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+CREATE TABLE BugTaskFlat (
+    bugtask integer PRIMARY KEY,
+    bug integer NOT NULL,
+    datecreated timestamp without time zone,
+    duplicateof integer,
+    bug_owner integer NOT NULL,
+    fti ts2.tsvector,
+    information_type integer NOT NULL,
+    date_last_updated timestamp without time zone NOT NULL,
+    heat integer NOT NULL,
+    product integer,
+    productseries integer,
+    distribution integer,
+    distroseries integer,
+    sourcepackagename integer,
+    status integer NOT NULL,
+    importance integer NOT NULL,
+    assignee integer,
+    milestone integer,
+    owner integer NOT NULL,
+    active boolean NOT NULL,
+    access_policies integer[],
+    access_grants integer[]
+);
+
+
+-- Non-target-specific filters
+CREATE INDEX bugtaskflat__bug__idx ON BugTaskFlat USING btree (bug);
+
+CREATE INDEX bugtaskflat__bug_owner__idx
+    ON BugTaskFlat USING btree (bug_owner);
+CREATE INDEX bugtaskflat__owner__idx
+    ON BugTaskFlat USING btree (owner);
+CREATE INDEX bugtaskflat__assignee__idx
+    ON BugTaskFlat USING btree (assignee);
+CREATE INDEX bugtaskflat__milestone__idx
+    ON BugTaskFlat USING btree (milestone);
+
+CREATE INDEX bugtaskflat__fti__idx ON BugTaskFlat USING gist (fti);
+
+
+-- Distribution-wide sorts
+CREATE INDEX
+    bugtaskflat__distribution__date_last_updated__idx
+    ON bugtaskflat
+    USING btree (distribution, date_last_updated)
+    WHERE distribution IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distribution__datecreated__idx
+    ON bugtaskflat
+    USING btree (distribution, datecreated)
+    WHERE distribution IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distribution__heat__bugtask__idx
+    ON bugtaskflat
+    USING btree (distribution, heat, bugtask DESC)
+    WHERE distribution IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distribution__importance__bugtask__idx
+    ON bugtaskflat
+    USING btree (distribution, importance, bugtask DESC)
+    WHERE distribution IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distribution__status__bugtask__idx
+    ON bugtaskflat
+    USING btree (distribution, status, bugtask DESC)
+    WHERE distribution IS NOT NULL;
+
+-- DSP or packageless sorts
+CREATE INDEX
+    bugtaskflat__distribution__spn__date_last_updated__idx
+    ON bugtaskflat
+    USING btree (distribution, sourcepackagename, date_last_updated)
+    WHERE distribution IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distribution__spn__datecreated__idx
+    ON bugtaskflat
+    USING btree (distribution, sourcepackagename, datecreated)
+    WHERE distribution IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distribution__spn__heat__bug__idx
+    ON bugtaskflat
+    USING btree (distribution, sourcepackagename, heat, bug DESC)
+    WHERE distribution IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distribution__spn__importance__bug__idx
+    ON bugtaskflat
+    USING btree (distribution, sourcepackagename, importance, bug DESC)
+    WHERE distribution IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distribution__spn__status__bug__idx
+    ON bugtaskflat
+    USING btree (distribution, sourcepackagename, status, bug DESC)
+    WHERE distribution IS NOT NULL;
+
+
+-- DistroSeries-wide sorts
+CREATE INDEX
+    bugtaskflat__distroseries__date_last_updated__idx
+    ON bugtaskflat
+    USING btree (distroseries, date_last_updated)
+    WHERE distroseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distroseries__datecreated__idx
+    ON bugtaskflat
+    USING btree (distroseries, datecreated)
+    WHERE distroseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distroseries__heat__bugtask__idx
+    ON bugtaskflat
+    USING btree (distroseries, heat, bugtask DESC)
+    WHERE distroseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distroseries__importance__bugtask__idx
+    ON bugtaskflat
+    USING btree (distroseries, importance, bugtask DESC)
+    WHERE distroseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distroseries__status__bugtask__idx
+    ON bugtaskflat
+    USING btree (distroseries, status, bugtask DESC)
+    WHERE distroseries IS NOT NULL;
+
+-- SP or packageless sorts
+CREATE INDEX
+    bugtaskflat__distroseries__spn__date_last_updated__idx
+    ON bugtaskflat
+    USING btree (distroseries, sourcepackagename, date_last_updated)
+    WHERE distroseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distroseries__spn__datecreated__idx
+    ON bugtaskflat
+    USING btree (distroseries, sourcepackagename, datecreated)
+    WHERE distroseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distroseries__spn__heat__bug__idx
+    ON bugtaskflat
+    USING btree (distroseries, sourcepackagename, heat, bug DESC)
+    WHERE distroseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distroseries__spn__importance__bug__idx
+    ON bugtaskflat
+    USING btree (distroseries, sourcepackagename, importance, bug DESC)
+    WHERE distroseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__distroseries__spn__status__bug__idx
+    ON bugtaskflat
+    USING btree (distroseries, sourcepackagename, status, bug DESC)
+    WHERE distroseries IS NOT NULL;
+
+
+-- Product sorts
+CREATE INDEX
+    bugtaskflat__product__date_last_updated__idx
+    ON bugtaskflat
+    USING btree (product, date_last_updated)
+    WHERE product IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__product__datecreated__idx
+    ON bugtaskflat
+    USING btree (product, datecreated)
+    WHERE product IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__product__heat__bug__idx
+    ON bugtaskflat
+    USING btree (product, heat, bug DESC)
+    WHERE product IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__product__importance__bug__idx
+    ON bugtaskflat
+    USING btree (product, importance, bug DESC)
+    WHERE product IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__product__status__bug__idx
+    ON bugtaskflat
+    USING btree (product, status, bug DESC)
+    WHERE product IS NOT NULL;
+
+-- ProductSeries sorts
+CREATE INDEX
+    bugtaskflat__productseries__date_last_updated__idx
+    ON bugtaskflat
+    USING btree (productseries, date_last_updated)
+    WHERE productseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__productseries__datecreated__idx
+    ON bugtaskflat
+    USING btree (productseries, datecreated)
+    WHERE productseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__productseries__heat__bug__idx
+    ON bugtaskflat
+    USING btree (productseries, heat, bug DESC)
+    WHERE productseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__productseries__importance__bug__idx
+    ON bugtaskflat
+    USING btree (productseries, importance, bug DESC)
+    WHERE productseries IS NOT NULL;
+CREATE INDEX
+    bugtaskflat__productseries__status__bug__idx
+    ON bugtaskflat
+    USING btree (productseries, status, bug DESC)
+    WHERE productseries IS NOT NULL;
+
+
+-- Update helpers
+
+CREATE OR REPLACE FUNCTION bug_build_access_cache(bug_id integer,
+                                                  information_type integer)
+    RETURNS record
+    LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
+    AS $$
+DECLARE
+    _access_artifact integer;
+    _access_policies integer[];
+    _access_grants integer[];
+    cache record;
+BEGIN
+    -- If the bug is private, grab the access control information.
+    -- If the bug is public, access_policies and access_grants are NULL.
+    -- 3 == EMBARGOEDSECURITY, 4 == USERDATA, 5 == PROPRIETARY
+    IF information_type IN (3, 4, 5) THEN
+        SELECT id INTO _access_artifact
+            FROM accessartifact
+            WHERE bug = bug_id;
+        -- We have to do the order in a subquery until 9.0 (8.4 doesn't
+        -- support ordering within an aggregate).
+        SELECT COALESCE(array_agg(policy), ARRAY[]::integer[])
+            INTO _access_policies
+            FROM (
+                SELECT policy FROM
+                accesspolicyartifact
+                WHERE artifact = _access_artifact
+                ORDER BY policy) AS policies;
+        SELECT COALESCE(array_agg(grantee), ARRAY[]::integer[])
+            INTO _access_grants
+            FROM (
+                SELECT grantee FROM
+                accessartifactgrant
+                WHERE artifact = _access_artifact
+                ORDER BY grantee) AS grantees;
+    END IF;
+    cache := (_access_policies, _access_grants);
+    RETURN cache;
+END;
+$$;
+
+COMMENT ON FUNCTION bug_build_access_cache(bug_id integer,
+                                           information_type integer) IS
+    'Build an access cache for the given bug. Returns '
+    '({AccessPolicyArtifact.policy}, {AccessArtifactGrant.grantee}) '
+    'for private bugs, or (NULL, NULL) for public ones.';
+
+
+CREATE OR REPLACE FUNCTION bugtask_flatten(task_id integer, check_only boolean)
+    RETURNS boolean
+    LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
+    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 * FROM bug INTO bug_row 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
+           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
+                WHERE bugtask = new_flat_row.bugtask;
+        END IF;
+        RETURN FALSE;
+    ELSE
+        RETURN TRUE;
+    END IF;
+END;
+$$;
+
+COMMENT ON FUNCTION bugtask_flatten(task_id integer, check_only boolean) IS
+    'Create or update a BugTaskFlat row from the source tables. Returns '
+    'whether the row was up to date. If check_only is true, the row is not '
+    'brought up to date.';
+
+
+CREATE OR REPLACE FUNCTION bug_flatten_access(bug_id integer)
+    RETURNS void
+    LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
+    AS $$
+DECLARE
+    _information_type integer;
+    _access_policies integer[];
+    _access_grants integer[];
+BEGIN
+    SELECT information_type FROM bug INTO _information_type WHERE id = bug_id;
+    SELECT policies, grants
+        INTO _access_policies, _access_grants
+        FROM bug_build_access_cache(bug_id, _information_type)
+            AS (policies integer[], grants integer[]);
+    UPDATE bugtaskflat
+        SET
+            access_policies = _access_policies,
+            access_grants = _access_grants
+        WHERE bug = bug_id;
+    RETURN;
+END;
+$$;
+
+COMMENT ON FUNCTION bug_flatten_access(bug_id integer) IS
+    'Recalculate the access cache on a bug''s flattened tasks.';
+
+
+CREATE OR REPLACE FUNCTION accessartifact_flatten_bug(artifact_id integer)
+    RETURNS void
+    LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
+    AS $$
+DECLARE
+    bug_id integer;
+BEGIN
+    SELECT bug INTO bug_id FROM accessartifact WHERE id = artifact_id;
+    IF bug_id IS NOT NULL THEN
+        PERFORM bug_flatten_access(bug_id);
+    END IF;
+    RETURN;
+END;
+$$;
+
+COMMENT ON FUNCTION accessartifact_flatten_bug(artifact_id integer) IS
+    'If the access artifact is a bug, update the access cache on its '
+    'flattened tasks.';
+
+
+
+-- BugTask trigger.
+
+CREATE OR REPLACE FUNCTION bugtask_maintain_bugtaskflat_trig() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
+    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.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) 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,
+                status = NEW.status,
+                importance = NEW.importance,
+                assignee = NEW.assignee,
+                milestone = NEW.milestone,
+                owner = NEW.owner
+                WHERE bugtask = NEW.id;
+        END IF;
+    ELSIF TG_OP = 'DELETE' THEN
+        PERFORM bugtask_flatten(OLD.id, FALSE);
+    END IF;
+    RETURN NULL;
+END;
+$$;
+
+-- z so they happen after fti and access updates.
+CREATE TRIGGER z_bugtask_maintain_bugtaskflat_trigger
+    AFTER INSERT OR UPDATE OR DELETE ON bugtask
+    FOR EACH ROW EXECUTE PROCEDURE bugtask_maintain_bugtaskflat_trig();
+
+
+
+-- Bug trigger. Only UPDATE, since on INSERT or DELETE there are no tasks.
+
+CREATE OR REPLACE FUNCTION bug_maintain_bugtaskflat_trig() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
+    AS $$
+BEGIN
+    IF (
+        NEW.duplicateof IS DISTINCT FROM OLD.duplicateof
+        OR NEW.owner IS DISTINCT FROM OLD.owner
+        OR NEW.fti IS DISTINCT FROM OLD.fti
+        OR NEW.information_type IS DISTINCT FROM OLD.information_type
+        OR NEW.date_last_updated IS DISTINCT FROM OLD.date_last_updated
+        OR NEW.heat IS DISTINCT FROM OLD.heat) THEN
+        UPDATE bugtaskflat
+            SET
+                duplicateof = NEW.duplicateof,
+                bug_owner = NEW.owner,
+                fti = NEW.fti,
+                information_type = NEW.information_type,
+                date_last_updated = NEW.date_last_updated,
+                heat = NEW.heat
+            WHERE bug = OLD.id;
+    END IF;
+
+    IF NEW.information_type IS DISTINCT FROM OLD.information_type THEN
+        PERFORM bug_flatten_access(OLD.id);
+    END IF;
+    RETURN NULL;
+END;
+$$;
+
+-- z so they happen after fti and access updates.
+CREATE TRIGGER z_bug_maintain_bugtaskflat_trigger
+    AFTER UPDATE ON bug
+    FOR EACH ROW EXECUTE PROCEDURE bug_maintain_bugtaskflat_trig();
+
+
+
+-- Shared AccessPolicyArtifact and AccessArtifactGrant trigger.
+
+CREATE OR REPLACE FUNCTION accessartifact_maintain_bugtaskflat_trig()
+    RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        PERFORM accessartifact_flatten_bug(NEW.artifact);
+    ELSIF TG_OP = 'UPDATE' THEN
+        PERFORM accessartifact_flatten_bug(NEW.artifact);
+        IF OLD.artifact != NEW.artifact THEN
+            PERFORM accessartifact_flatten_bug(OLD.artifact);
+        END IF;
+    ELSIF TG_OP = 'DELETE' THEN
+        PERFORM accessartifact_flatten_bug(OLD.artifact);
+    END IF;
+    RETURN NULL;
+END;
+$$;
+
+CREATE TRIGGER accesspolicyartifact_maintain_bugtaskflat_trigger
+    AFTER INSERT OR UPDATE OR DELETE ON accesspolicyartifact
+    FOR EACH ROW EXECUTE PROCEDURE accessartifact_maintain_bugtaskflat_trig();
+
+CREATE TRIGGER accessartifactgrant_maintain_bugtaskflat_trigger
+    AFTER INSERT OR UPDATE OR DELETE ON accessartifactgrant
+    FOR EACH ROW EXECUTE PROCEDURE accessartifact_maintain_bugtaskflat_trig();
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 16, 0);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2012-03-26 05:50:55 +0000
+++ database/schema/security.cfg	2012-03-30 00:02:34 +0000
@@ -11,13 +11,17 @@
 
 [public]
 type=group
+public.accessartifact_flatten_bug(integer) =
 public.activity()                          = EXECUTE
 public.add_test_openid_identifier(integer) = EXECUTE
 public.alllocks                            =
 public.assert_patch_applied(integer, integer, integer) = EXECUTE
+public.bug_build_access_cache(integer, integer) =
+public.bug_flatten_access(integer)         =
 public.bug_mirror_legacy_access(integer)   =
 public.bug_update_latest_patch_uploaded(integer) =
 public.bugnotificationarchive              =
+public.bugtask_flatten(integer, boolean)   =
 public.calculate_bug_heat(integer)         = EXECUTE
 public.cursor_fetch(refcursor, integer)    = EXECUTE
 public.databasediskutilization             =
@@ -344,6 +348,7 @@
 public.bug                              = SELECT
 public.bugaffectsperson                 = SELECT, INSERT, UPDATE, DELETE
 public.bugtask                          = SELECT
+public.bugtaskflat                      = SELECT
 public.buildfarmjob                     = SELECT
 public.distribution                     = SELECT
 public.distributionsourcepackagecache   = SELECT, INSERT, UPDATE, DELETE
@@ -433,6 +438,7 @@
 groups=script
 public.bug                              = SELECT
 public.bugtask                          = SELECT, UPDATE
+public.bugtaskflat                      = SELECT
 public.libraryfilealias                 = SELECT, INSERT
 public.libraryfilecontent               = SELECT, INSERT
 public.milestone                        = SELECT, INSERT
@@ -577,6 +583,7 @@
 public.bugsubscriptionfiltertag         = SELECT
 public.bugtag                           = SELECT
 public.bugtask                          = SELECT, INSERT, UPDATE
+public.bugtaskflat                      = SELECT
 public.bugtracker                       = SELECT, INSERT
 public.bugtrackeralias                  = SELECT
 public.bugtrackerperson                 = SELECT, INSERT
@@ -694,6 +701,7 @@
 groups=script
 public.binarypackagename                        = SELECT
 public.bugtask                                  = SELECT, UPDATE
+public.bugtaskflat                              = SELECT
 public.distribution                             = SELECT
 public.distroseries                             = SELECT
 public.potemplate                               = SELECT, UPDATE
@@ -850,6 +858,7 @@
 public.bugsubscriptionfiltertag         = SELECT
 public.bugtag                           = SELECT
 public.bugtask                          = SELECT, UPDATE
+public.bugtaskflat                      = SELECT
 public.bugtracker                       = SELECT, INSERT
 public.bugtrackeralias                  = SELECT, INSERT
 public.bugwatch                         = SELECT, INSERT
@@ -1128,6 +1137,7 @@
 public.bugsubscriptionfilterstatus      = SELECT, INSERT, UPDATE, DELETE
 public.bugsubscriptionfiltertag         = SELECT, INSERT, UPDATE, DELETE
 public.bugtask                          = SELECT, INSERT, UPDATE, DELETE
+public.bugtaskflat                      = SELECT
 public.bugtracker                       = SELECT, INSERT, UPDATE, DELETE
 public.bugtrackeralias                  = SELECT, INSERT, UPDATE, DELETE
 public.bugwatch                         = SELECT, INSERT, UPDATE, DELETE
@@ -1217,6 +1227,7 @@
 public.bug                              = SELECT
 public.bugaffectsperson                 = SELECT, INSERT, UPDATE, DELETE
 public.bugtask                          = SELECT
+public.bugtaskflat                      = SELECT
 public.distribution                     = SELECT
 public.emailaddress                     = SELECT
 public.faq                              = SELECT
@@ -1279,6 +1290,7 @@
 public.bugsubscriptionfiltertag         = SELECT
 public.bugtag                           = SELECT
 public.bugtask                          = SELECT, UPDATE
+public.bugtaskflat                      = SELECT
 public.bugtracker                       = SELECT, INSERT
 public.bugtrackeralias                  = SELECT, INSERT
 public.bugwatch                         = SELECT, INSERT
@@ -1382,6 +1394,7 @@
 public.bugsubscriptionfiltertag         = SELECT
 public.bugtag                           = SELECT
 public.bugtask                          = SELECT, UPDATE
+public.bugtaskflat                      = SELECT
 public.bugtracker                       = SELECT, INSERT
 public.bugtrackeralias                  = SELECT, INSERT
 public.bugwatch                         = SELECT, INSERT
@@ -1488,6 +1501,7 @@
 public.bugsubscriptionfiltertag         = SELECT, INSERT
 public.bugtag                           = SELECT
 public.bugtask                          = SELECT, INSERT, UPDATE
+public.bugtaskflat                      = SELECT
 public.bugwatch                         = SELECT
 public.component                        = SELECT
 public.distribution                     = SELECT, UPDATE
@@ -1668,6 +1682,7 @@
 public.bugsubscriptionfiltertag         = SELECT, INSERT, UPDATE, DELETE
 public.bugtag                           = SELECT, INSERT, DELETE
 public.bugtask                          = SELECT, INSERT, UPDATE
+public.bugtaskflat                      = SELECT
 public.bugtracker                       = SELECT, INSERT
 public.bugtrackeralias                  = SELECT, INSERT
 public.bugwatch                         = SELECT, INSERT
@@ -1833,6 +1848,7 @@
 public.bugbranch                        = SELECT
 public.bugsubscription                  = SELECT
 public.bugtask                          = SELECT
+public.bugtaskflat                      = SELECT
 public.codereviewmessage                = SELECT, INSERT
 public.codereviewvote                   = SELECT, INSERT
 public.diff                             = SELECT, INSERT
@@ -1924,6 +1940,7 @@
 public.bugsubscriptionfiltertag         = SELECT, INSERT
 public.bugtag                           = SELECT
 public.bugtask                          = SELECT, INSERT, UPDATE
+public.bugtaskflat                      = SELECT
 public.bugtracker                       = SELECT, INSERT
 public.bugtrackeralias                  = SELECT
 public.bugwatch                         = SELECT, INSERT
@@ -1998,6 +2015,7 @@
 public.bugsubscriptionfilter            = SELECT, UPDATE, DELETE
 public.bugsummary                       = SELECT
 public.bugtask                          = SELECT, UPDATE
+public.bugtaskflat                      = SELECT
 public.bugtracker                       = SELECT, UPDATE
 public.bugtrackerperson                 = SELECT, UPDATE
 public.bugwatch                         = SELECT, UPDATE
@@ -2122,6 +2140,7 @@
 public.bugsummary_rollup_journal(integer) = EXECUTE
 public.bugtag                           = SELECT
 public.bugtask                          = SELECT, UPDATE
+public.bugtaskflat                      = SELECT
 public.bugwatch                         = SELECT, UPDATE
 public.bugwatchactivity                 = SELECT, DELETE
 public.codeimportevent                  = SELECT, DELETE

=== added file 'lib/lp/bugs/tests/test_bugtaskflat_triggers.py'
--- lib/lp/bugs/tests/test_bugtaskflat_triggers.py	1970-01-01 00:00:00 +0000
+++ lib/lp/bugs/tests/test_bugtaskflat_triggers.py	2012-03-30 00:02:34 +0000
@@ -0,0 +1,406 @@
+# Copyright 2012 Canonical Ltd.  This software is licensed under the
+# GNU Affero General Public License version 3 (see the file LICENSE).
+
+__metaclass__ = type
+
+from collections import namedtuple
+from contextlib import contextmanager
+
+from testtools.matchers import MatchesStructure
+from zope.component import getUtility
+from zope.security.proxy import removeSecurityProxy
+
+from lp.bugs.interfaces.bugtask import BugTaskStatus
+from lp.bugs.model.bug import Bug
+from lp.registry.enums import InformationType
+from lp.registry.interfaces.accesspolicy import (
+    IAccessArtifactSource,
+    IAccessArtifactGrantSource,
+    IAccessPolicyArtifactSource,
+    IAccessPolicySource,
+    )
+from lp.services.database.lpstorm import IStore
+from lp.services.features.testing import FeatureFixture
+from lp.testing import (
+    login_person,
+    person_logged_in,
+    TestCaseWithFactory,
+    )
+from lp.testing.dbuser import dbuser
+from lp.testing.layers import DatabaseFunctionalLayer
+
+BUGTASKFLAT_COLUMNS = (
+    'bugtask',
+    'bug',
+    'datecreated',
+    'duplicateof',
+    'bug_owner',
+    'fti',
+    'information_type',
+    'date_last_updated',
+    'heat',
+    'product',
+    'productseries',
+    'distribution',
+    'distroseries',
+    'sourcepackagename',
+    'status',
+    'importance',
+    'assignee',
+    'milestone',
+    'owner',
+    'active',
+    'access_policies',
+    'access_grants',
+    )
+
+BugTaskFlat = namedtuple('BugTaskFlat', BUGTASKFLAT_COLUMNS)
+
+
+class BugTaskFlatTestMixin(TestCaseWithFactory):
+
+    def setUp(self):
+        super(BugTaskFlatTestMixin, self).setUp()
+        self.useFixture(FeatureFixture(
+            {'disclosure.allow_multipillar_private_bugs.enabled': 'true'}))
+
+    def checkFlattened(self, bugtask, check_only=True):
+        if hasattr(bugtask, 'id'):
+            bugtask = bugtask.id
+        result = IStore(Bug).execute(
+            "SELECT bugtask_flatten(?, ?)", (bugtask, check_only))
+        return result.get_one()[0]
+
+    def assertFlattened(self, bugtask):
+        # Assert that the BugTask is correctly represented in
+        # BugTaskFlat.
+        self.assertIs(True, self.checkFlattened(bugtask))
+
+    def assertFlattens(self, bugtask):
+        # Assert that the BugTask isn't correctly represented in
+        # BugTaskFlat, but a call to bugtask_flatten fixes it.
+        self.assertFalse(self.checkFlattened(bugtask))
+        self.checkFlattened(bugtask, check_only=False)
+        self.assertTrue(self.checkFlattened(bugtask))
+
+    def getBugTaskFlat(self, bugtask):
+        if hasattr(bugtask, 'id'):
+            bugtask = bugtask.id
+        assert bugtask is not None
+        result = IStore(Bug).execute(
+            "SELECT %s FROM bugtaskflat WHERE bugtask = ?"
+            % ', '.join(BUGTASKFLAT_COLUMNS), (bugtask,)).get_one()
+        if result is not None:
+            result = BugTaskFlat(*result)
+        return result
+
+    def makeLoggedInTask(self, private=False):
+        owner = self.factory.makePerson()
+        login_person(owner)
+        bug = self.factory.makeBug(private=private, owner=owner)
+        return bug.default_bugtask
+
+    @contextmanager
+    def bugtaskflat_is_deleted(self, bugtask):
+        old_row = self.getBugTaskFlat(bugtask)
+        self.assertFlattened(bugtask)
+        self.assertIsNot(None, old_row)
+        yield
+        new_row = self.getBugTaskFlat(bugtask)
+        self.assertFlattened(bugtask)
+        self.assertIs(None, new_row)
+
+    @contextmanager
+    def bugtaskflat_is_updated(self, bugtask, expected_fields):
+        old_row = self.getBugTaskFlat(bugtask)
+        self.assertFlattened(bugtask)
+        yield
+        new_row = self.getBugTaskFlat(bugtask)
+        self.assertFlattened(bugtask)
+        changed_fields = [
+            field for field in BugTaskFlat._fields
+            if getattr(old_row, field) != getattr(new_row, field)]
+        self.assertEqual(expected_fields, changed_fields)
+
+    @contextmanager
+    def bugtaskflat_is_identical(self, bugtask):
+        old_row = self.getBugTaskFlat(bugtask)
+        self.assertFlattened(bugtask)
+        yield
+        new_row = self.getBugTaskFlat(bugtask)
+        self.assertFlattened(bugtask)
+        self.assertEqual(old_row, new_row)
+
+
+class TestBugTaskFlatten(BugTaskFlatTestMixin):
+
+    layer = DatabaseFunctionalLayer
+
+    def test_create(self):
+        # bugtask_flatten() returns true if the BugTaskFlat is missing,
+        # and optionally creates it.
+        task = self.factory.makeBugTask()
+        self.assertTrue(self.checkFlattened(task))
+        with dbuser('testadmin'):
+            IStore(Bug).execute(
+                "DELETE FROM BugTaskFlat WHERE bugtask = ?", (task.id,))
+        self.assertFlattens(task)
+
+    def test_update(self):
+        # bugtask_flatten() returns true if the BugTaskFlat is out of
+        # date, and optionally updates it.
+        task = self.factory.makeBugTask()
+        self.assertTrue(self.checkFlattened(task))
+        with dbuser('testadmin'):
+            IStore(Bug).execute(
+                "UPDATE BugTaskFlat SET status = ? WHERE bugtask = ?",
+                (BugTaskStatus.UNKNOWN.value, task.id))
+        self.assertFlattens(task)
+
+    def test_delete(self):
+        # bugtask_flatten() returns true if the BugTaskFlat exists but
+        # the task doesn't, and optionally deletes it.
+        self.assertTrue(self.checkFlattened(200))
+        with dbuser('testadmin'):
+            IStore(Bug).execute(
+                "INSERT INTO bugtaskflat "
+                "(bug, bugtask, bug_owner, information_type, "
+                " date_last_updated, heat, status, importance, owner, "
+                " active) "
+                "VALUES "
+                "(1, 200, 1, 1, "
+                " current_timestamp at time zone 'UTC', 999, 1, 1, 1, true);")
+        self.assertFlattens(200)
+
+    def test_values(self):
+        task = self.factory.makeBugTask()
+        with person_logged_in(task.product.owner):
+            task.transitionToAssignee(self.factory.makePerson())
+            task.transitionToMilestone(
+                self.factory.makeMilestone(product=task.product),
+                task.product.owner)
+            task.bug.markAsDuplicate(self.factory.makeBug())
+        flat = self.getBugTaskFlat(task)
+        self.assertThat(
+            flat,
+            MatchesStructure.byEquality(
+                bugtask=task.id,
+                bug=task.bug.id,
+                datecreated=task.datecreated.replace(tzinfo=None),
+                duplicateof=task.bug.duplicateof.id,
+                bug_owner=task.bug.owner.id,
+                information_type=task.bug.information_type.value,
+                date_last_updated=task.bug.date_last_updated.replace(
+                    tzinfo=None),
+                heat=task.bug.heat,
+                product=task.product.id,
+                productseries=None,
+                distribution=None,
+                distroseries=None,
+                sourcepackagename=None,
+                status=task.status.value,
+                importance=task.importance.value,
+                assignee=task.assignee.id,
+                milestone=task.milestone.id,
+                owner=task.owner.id,
+                active=task.product.active,
+                access_policies=None,
+                access_grants=None))
+        self.assertIsNot(None, flat.fti)
+
+    def test_productseries_target(self):
+        ps = self.factory.makeProductSeries()
+        task = self.factory.makeBugTask(target=ps)
+        flat = self.getBugTaskFlat(task)
+        self.assertThat(
+            flat,
+            MatchesStructure.byEquality(
+                product=None, productseries=ps.id, distribution=None,
+                distroseries=None, sourcepackagename=None, active=True))
+
+    def test_distributionsourcepackage_target(self):
+        dsp = self.factory.makeDistributionSourcePackage()
+        task = self.factory.makeBugTask(target=dsp)
+        flat = self.getBugTaskFlat(task)
+        self.assertThat(
+            flat,
+            MatchesStructure.byEquality(
+                product=None, productseries=None,
+                distribution=dsp.distribution.id, distroseries=None,
+                sourcepackagename=dsp.sourcepackagename.id, active=True))
+
+    def test_sourcepackage_target(self):
+        sp = self.factory.makeSourcePackage()
+        task = self.factory.makeBugTask(target=sp)
+        flat = self.getBugTaskFlat(task)
+        self.assertThat(
+            flat,
+            MatchesStructure.byEquality(
+                product=None, productseries=None, distribution=None,
+                distroseries=sp.distroseries.id,
+                sourcepackagename=sp.sourcepackagename.id, active=True))
+
+    def test_product_active_flag_respected(self):
+        # A bugtask created on a product or productseries respects the
+        # product's active flag. Note that there are no triggers to
+        # handle this change, as the number of changes can be too large.
+        # A job will be used instead.
+        p = self.factory.makeProduct()
+        removeSecurityProxy(p).active = False
+        ps = self.factory.makeProductSeries(product=p)
+        ptask = self.factory.makeBugTask(target=p)
+        pstask = self.factory.makeBugTask(target=ps)
+        self.assertEqual(False, self.getBugTaskFlat(ptask).active)
+        self.assertEqual(False, self.getBugTaskFlat(pstask).active)
+
+    def test_public_access_cache_is_null(self):
+        # access_policies and access_grants for a public bug are NULL.
+        bugtask = self.makeLoggedInTask()
+        flat = self.getBugTaskFlat(bugtask.id)
+        self.assertIs(None, flat.access_policies)
+        self.assertIs(None, flat.access_grants)
+
+    def test_private_access_cache_is_set(self):
+        # access_policies and access_grants for a private bug are
+        # mirrored appropriately.
+        bugtask = self.makeLoggedInTask(private=True)
+        flat = self.getBugTaskFlat(bugtask.id)
+        [policy] = getUtility(IAccessPolicySource).find(
+            [(bugtask.pillar, InformationType.USERDATA)])
+        self.assertContentEqual([policy.id], flat.access_policies)
+        self.assertContentEqual(
+            [p.id for p in bugtask.bug.getDirectSubscribers()],
+            flat.access_grants)
+
+
+class TestBugTaskFlatTriggers(BugTaskFlatTestMixin):
+
+    layer = DatabaseFunctionalLayer
+
+    def test_bugtask_create(self):
+        # Triggers maintain BugTaskFlat when a task is created.
+        task = self.factory.makeBugTask()
+        self.assertFlattened(task)
+
+    def test_bugtask_delete(self):
+        # Triggers maintain BugTaskFlat when a task is deleted.
+        task = self.makeLoggedInTask()
+        # We need a second task before it will let us delete the first.
+        self.factory.makeBugTask(bug=task.bug)
+        with self.bugtaskflat_is_deleted(task):
+            task.delete()
+
+    def test_bugtask_change(self):
+        # Triggers maintain BugTaskFlat when a task is changed.
+        task = self.makeLoggedInTask()
+        with self.bugtaskflat_is_updated(task, ['status']):
+            task.transitionToStatus(BugTaskStatus.UNKNOWN, task.owner)
+
+    def test_bugtask_change_unflattened(self):
+        # Some fields on BugTask aren't mirrored, so don't trigger updates.
+        task = self.makeLoggedInTask()
+        with self.bugtaskflat_is_identical(task):
+            task.bugwatch = self.factory.makeBugWatch()
+
+    def test_bug_change(self):
+        # Triggers maintain BugTaskFlat when a bug is changed
+        task = self.makeLoggedInTask()
+        with self.bugtaskflat_is_updated(task, ['information_type']):
+            removeSecurityProxy(task.bug).information_type = (
+                InformationType.UNEMBARGOEDSECURITY)
+
+    def test_bug_make_private(self):
+        # Triggers maintain BugTaskFlat when a bug is made private.
+        task = self.makeLoggedInTask()
+        with self.bugtaskflat_is_updated(
+            task, ['information_type', 'access_policies', 'access_grants']):
+            removeSecurityProxy(task.bug).information_type = (
+                InformationType.USERDATA)
+
+    def test_bug_make_public(self):
+        # Triggers maintain BugTaskFlat when a bug is made public.
+        task = self.makeLoggedInTask(private=True)
+        with self.bugtaskflat_is_updated(
+            task, [
+                'information_type', 'date_last_updated', 'heat',
+                'access_policies', 'access_grants']):
+            task.bug.setPrivate(False, task.owner)
+
+    def test_bug_change_unflattened(self):
+        # Some fields on Bug aren't mirrored, so don't trigger updates.
+        task = self.makeLoggedInTask()
+        with self.bugtaskflat_is_identical(task):
+            removeSecurityProxy(task.bug).who_made_private = task.owner
+
+    def test_accessartifactgrant_create(self):
+        # Creating an AccessArtifactGrant updates the relevant bugs.
+        task = self.makeLoggedInTask(private=True)
+        [artifact] = getUtility(IAccessArtifactSource).find([task.bug])
+        with self.bugtaskflat_is_updated(task, ['access_grants']):
+            self.factory.makeAccessArtifactGrant(artifact=artifact)
+
+    def test_accessartifactgrant_update(self):
+        # Updating an AccessArtifactGrant updates the relevant bugs.
+        # Person merge is the main use case here.
+        task = self.makeLoggedInTask(private=True)
+        [artifact] = getUtility(IAccessArtifactSource).find([task.bug])
+        grant = self.factory.makeAccessArtifactGrant(artifact=artifact)
+        with self.bugtaskflat_is_updated(task, ['access_grants']):
+            removeSecurityProxy(grant).grantee = self.factory.makePerson()
+
+    def test_accessartifactgrant_delete(self):
+        # Deleting an AccessArtifactGrant updates the relevant bugs.
+        task = self.makeLoggedInTask(private=True)
+        [artifact] = getUtility(IAccessArtifactSource).find([task.bug])
+        self.factory.makeAccessArtifactGrant(artifact=artifact)
+        with self.bugtaskflat_is_updated(task, ['access_grants']):
+            getUtility(IAccessArtifactGrantSource).revokeByArtifact(
+                [artifact])
+
+    def test_accesspolicyartifact_create(self):
+        # Creating an AccessPolicyArtifact updates the relevant bugtasks.
+        task = self.makeLoggedInTask(private=True)
+        [artifact] = getUtility(IAccessArtifactSource).find([task.bug])
+        with self.bugtaskflat_is_updated(task, ['access_policies']):
+            self.factory.makeAccessPolicyArtifact(artifact=artifact)
+
+    def test_accesspolicyartifact_update(self):
+        # Updating an AccessPolicyArtifact updates the relevant bugs.
+        # There are currently no users of this, but it still works.
+        task = self.makeLoggedInTask(private=True)
+        [artifact] = getUtility(IAccessArtifactSource).find([task.bug])
+        link = self.factory.makeAccessPolicyArtifact(artifact=artifact)
+        with self.bugtaskflat_is_updated(task, ['access_policies']):
+            removeSecurityProxy(link).policy = self.factory.makeAccessPolicy()
+
+    def test_accesspolicyartifact_delete(self):
+        # Deleting an AccessPolicyArtifact updates the relevant bugtasks.
+        task = self.makeLoggedInTask(private=True)
+        [artifact] = getUtility(IAccessArtifactSource).find([task.bug])
+        self.factory.makeAccessPolicyArtifact(artifact=artifact)
+        with self.bugtaskflat_is_updated(task, ['access_policies']):
+            getUtility(IAccessPolicyArtifactSource).deleteByArtifact(
+                [artifact])
+
+    def test_access_create_public(self):
+        # Creating a grant or policy link on a public bug has no effect.
+        # The access caches remain null.
+        task = self.makeLoggedInTask()
+        with self.bugtaskflat_is_identical(task):
+            [artifact] = getUtility(IAccessArtifactSource).ensure([task.bug])
+            self.factory.makeAccessPolicyArtifact(artifact=artifact)
+            self.factory.makeAccessArtifactGrant(artifact=artifact)
+
+    def test_accessartifact_delete(self):
+        # Deleting an AccessArtifact removes the corresponding
+        # AccessArtifactGrant and AccessPolicyArtifact rows. Even though
+        # it's hopefully impossible for a private bug to not have an
+        # AccessArtifact, access_policies and access_grants are empty
+        # lists, not NULL.
+        task = self.makeLoggedInTask(private=True)
+        with self.bugtaskflat_is_updated(
+            task, ['access_policies', 'access_grants']):
+            getUtility(IAccessArtifactSource).delete([task.bug])
+        flat = self.getBugTaskFlat(task.id)
+        self.assertEqual([], flat.access_policies)
+        self.assertEqual([], flat.access_grants)