Launchpad itself

Merge lp:~wgrant/launchpad/bugsummary-v2-db-0 into lp:launchpad/db-devel

  1. bugsummary-v2-db-0
  2. Merge into db-devel
Proposed by William Grant
Status: Merged
Approved by: Stuart Bishop
Approved revision: no longer in the source branch.
Merged at revision: 11735
Proposed branch: lp:~wgrant/launchpad/bugsummary-v2-db-0
Merge into: lp:launchpad/db-devel
Prerequisite: lp:~wgrant/launchpad/bugsummary-v2-db-0-untouched
Diff against target: 668 lines (+194/-298)
2 files modified
database/schema/patch-2209-19-0.sql (+186/-290)
database/schema/security.cfg (+8/-8)
To merge this branch: bzr merge lp:~wgrant/launchpad/bugsummary-v2-db-0
Reviewer Review Type Date Requested Status
Stuart Bishop (community) db Approve
Robert Collins db Pending
Review via email: mp+113008@code.launchpad.net

Commit message

First DB phase of BugSummary v2: adding unpopulated access_policy, changing to a task-driven trigger set.

Description of the change

This branch begins the DB side of the BugSummary access rework (using access policies and grants rather than bug subscriptions).

The main change is the addition of an access_policy column. It's currently always null, so appservers can be taught to filter on it properly before we start leaking data. I also made fixed_upstream (already removed from the app code) default to false in preparation for its removal.

Those two schema changes only make up a tiny portion of the overall DB changes. Most of the rest restructures things to perform less badly and make it easier to populate access_grants later. Rather than triggering on and joining across Bug+BugTask+BugSubscription+BugTag into something that is suspiciously similar to BugTaskFlat, we now just use BugTaskFlat+BugTag.

This means that BugSummary journaling becomes BugTask-based rather than Bug-based, significantly changing its performance characteristics. In the old world, journaling a full bug (on a duplicateof, has_patch, information_type or tag change) would write n*(tasks + distinct_distributions) rows. Because of the rule that package tasks also show up in their distribution's/distroseries' counts, each BugTaskFlat entry writes one or two targets worth of journal rows, so the new approach can take n*2*tasks journal rows -- worse by up to a factor of two. However, the more common case of journaling a single task is much improved: n*tasks to n*2. This becomes even more notable when you consider a bulk task creation such as a nomination approval: what was n*new_tasks*(old_tasks + new_tasks) is now n*2*new_tasks, probably fixing the tricky bit of bug #874250.

The change to a task-based approach also changes the counts slightly: each task on a package contributes to the distribution's counts on its own, rather than each bug with a task on a package contributing only once. So counts that involve bugs with multiple package tasks for a single distribution will see an increase. This new behaviour matches bugsummaryrebuild and the counting method we've used in bug search listings forever, and it's far easier to implement efficiently. This change will mean we have more wildly exaggerated counts until we do a rebuild of the table, but I intend to do one as soon as I verify that the patch performs as expected on production.

I also dropped some foreign key constraints from the journal. The table (bugsummaryjournal) is maintained by triggers on a table (bugtaskflat) maintained by triggers on columns that have foreign keys, and it can be readily rebuilt if a bug causes corruption, so the foreign keys serve only to hinder performance. Testing shows that it can actually speed things up significantly.

The diff is pretty hideous, but I've specified a prereq branch with the original functions to hopefully make things a bit more sensible.

To post a comment you must log in.
Revision history for this message
Stuart Bishop (stub) wrote :

Discussed some on IRC. Existing tests will provide the best review.

review: Approve (db)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== modified file 'database/schema/patch-2209-19-0.sql'
--- database/schema/patch-2209-19-0.sql 2012-07-02 10:06:25 +0000
+++ database/schema/patch-2209-19-0.sql 2012-07-02 10:06:26 +0000
@@ -5,6 +5,12 @@
55
6-- Journal functions. Speed is critical -- these are run by appservers.6-- Journal functions. Speed is critical -- these are run by appservers.
77
8ALTER TABLE bugsummary ALTER COLUMN fixed_upstream SET DEFAULT false;
9ALTER TABLE bugsummaryjournal ALTER COLUMN fixed_upstream SET DEFAULT false;
10
11ALTER TABLE bugsummary ADD COLUMN access_policy integer;
12ALTER TABLE bugsummaryjournal ADD COLUMN access_policy integer;
13
8CREATE OR REPLACE FUNCTION public.bug_summary_flush_temp_journal()14CREATE OR REPLACE FUNCTION public.bug_summary_flush_temp_journal()
9 RETURNS void15 RETURNS void
10 LANGUAGE plpgsql16 LANGUAGE plpgsql
@@ -12,217 +18,130 @@
12DECLARE18DECLARE
13 d bugsummary%ROWTYPE;19 d bugsummary%ROWTYPE;
14BEGIN20BEGIN
15 -- may get called even though no summaries were made (for simplicity in the21 -- May get called even though no summaries were made (for simplicity in the
16 -- callers)22 -- callers). We sum the rows here to minimise the number of inserts
23 -- into the persistent journal, as it's reasonably likely that we'll
24 -- have -1s and +1s cancelling each other out.
17 PERFORM ensure_bugsummary_temp_journal();25 PERFORM ensure_bugsummary_temp_journal();
18 FOR d IN SELECT * FROM bugsummary_temp_journal LOOP26 INSERT INTO BugSummaryJournal(
19 PERFORM bugsummary_journal_ins(d);27 count, product, productseries, distribution,
20 END LOOP;28 distroseries, sourcepackagename, viewed_by, tag,
29 status, milestone, importance, has_patch, fixed_upstream,
30 access_policy)
31 SELECT
32 SUM(count), product, productseries, distribution,
33 distroseries, sourcepackagename, viewed_by, tag,
34 status, milestone, importance, has_patch, fixed_upstream,
35 access_policy
36 FROM bugsummary_temp_journal
37 GROUP BY
38 product, productseries, distribution,
39 distroseries, sourcepackagename, viewed_by, tag,
40 status, milestone, importance, has_patch, fixed_upstream,
41 access_policy
42 HAVING SUM(count) != 0;
21 TRUNCATE bugsummary_temp_journal;43 TRUNCATE bugsummary_temp_journal;
22END;44END;
23$function$;45$function$;
2446
25CREATE OR REPLACE FUNCTION public.bug_summary_temp_journal_ins(d bugsummary)47CREATE OR REPLACE FUNCTION public.bugsummary_journal_bugtaskflat(btf_row bugtaskflat, _count integer)
26 RETURNS void48 RETURNS void
27 LANGUAGE plpgsql49 LANGUAGE plpgsql
28AS $function$50AS $function$
29BEGIN51BEGIN
52 PERFORM ensure_bugsummary_temp_journal();
30 INSERT INTO BugSummary_Temp_Journal(53 INSERT INTO BugSummary_Temp_Journal(
31 count, product, productseries, distribution,54 count, product, productseries, distribution,
32 distroseries, sourcepackagename, viewed_by, tag,55 distroseries, sourcepackagename, viewed_by, tag,
33 status, milestone, importance, has_patch, fixed_upstream)56 status, milestone, importance, has_patch, fixed_upstream,
34 VALUES (57 access_policy)
35 d.count, d.product, d.productseries, d.distribution,58 SELECT
36 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,59 _count, product, productseries, distribution,
37 d.status, d.milestone, d.importance, d.has_patch, d.fixed_upstream);60 distroseries, sourcepackagename, viewed_by, tag,
38 RETURN;61 status, milestone, importance, has_patch, fixed_upstream,
39END;62 access_policy
40$function$;63 FROM bugsummary_locations(btf_row);
4164END;
42CREATE OR REPLACE FUNCTION public.bugsubscription_maintain_bug_summary()65$function$;
43 RETURNS trigger66
44 LANGUAGE plpgsql67CREATE OR REPLACE FUNCTION public.bugsummary_journal_bug(bug_row bug, _count integer)
45 SECURITY DEFINER
46 SET search_path TO public
47AS $function$
48BEGIN
49 -- This trigger only works if we are inserting, updating or deleting
50 -- a single row per statement.
51 IF TG_OP = 'INSERT' THEN
52 IF (bug_row(NEW.bug)).information_type IN (1, 2) THEN
53 -- Public subscriptions are not aggregated.
54 RETURN NEW;
55 END IF;
56 IF TG_WHEN = 'BEFORE' THEN
57 PERFORM unsummarise_bug(bug_row(NEW.bug));
58 ELSE
59 PERFORM summarise_bug(bug_row(NEW.bug));
60 END IF;
61 PERFORM bug_summary_flush_temp_journal();
62 RETURN NEW;
63 ELSIF TG_OP = 'DELETE' THEN
64 IF (bug_row(OLD.bug)).information_type IN (1, 2) THEN
65 -- Public subscriptions are not aggregated.
66 RETURN OLD;
67 END IF;
68 IF TG_WHEN = 'BEFORE' THEN
69 PERFORM unsummarise_bug(bug_row(OLD.bug));
70 ELSE
71 PERFORM summarise_bug(bug_row(OLD.bug));
72 END IF;
73 PERFORM bug_summary_flush_temp_journal();
74 RETURN OLD;
75 ELSE
76 IF (OLD.person IS DISTINCT FROM NEW.person
77 OR OLD.bug IS DISTINCT FROM NEW.bug) THEN
78 IF TG_WHEN = 'BEFORE' THEN
79 IF (bug_row(OLD.bug)).information_type IN (3, 4, 5) THEN
80 -- Public subscriptions are not aggregated.
81 PERFORM unsummarise_bug(bug_row(OLD.bug));
82 END IF;
83 IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).information_type IN (3, 4, 5) THEN
84 -- Public subscriptions are not aggregated.
85 PERFORM unsummarise_bug(bug_row(NEW.bug));
86 END IF;
87 ELSE
88 IF (bug_row(OLD.bug)).information_type IN (3, 4, 5) THEN
89 -- Public subscriptions are not aggregated.
90 PERFORM summarise_bug(bug_row(OLD.bug));
91 END IF;
92 IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).information_type IN (3, 4, 5) THEN
93 -- Public subscriptions are not aggregated.
94 PERFORM summarise_bug(bug_row(NEW.bug));
95 END IF;
96 END IF;
97 END IF;
98 PERFORM bug_summary_flush_temp_journal();
99 RETURN NEW;
100 END IF;
101END;
102$function$;
103
104CREATE OR REPLACE FUNCTION public.bugsummary_journal_ins(d bugsummary)
105 RETURNS void68 RETURNS void
106 LANGUAGE plpgsql69 LANGUAGE plpgsql
107AS $function$70AS $function$
71DECLARE
72 btf_row bugtaskflat%ROWTYPE;
108BEGIN73BEGIN
109 IF d.count <> 0 THEN74 FOR btf_row IN SELECT * FROM bugtaskflat WHERE bug = bug_row.id
110 INSERT INTO BugSummaryJournal (75 LOOP
111 count, product, productseries, distribution,76 PERFORM bugsummary_journal_bugtaskflat(btf_row, _count);
112 distroseries, sourcepackagename, viewed_by, tag,77 END LOOP;
113 status, milestone,
114 importance, has_patch, fixed_upstream)
115 VALUES (
116 d.count, d.product, d.productseries, d.distribution,
117 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
118 d.status, d.milestone,
119 d.importance, d.has_patch, d.fixed_upstream);
120 END IF;
121END;78END;
122$function$;79$function$;
12380
124CREATE OR REPLACE FUNCTION public.bugsummary_locations(bug_row bug)81CREATE OR REPLACE FUNCTION public.bugsummary_locations(btf_row bugtaskflat)
125 RETURNS SETOF bugsummary82 RETURNS SETOF bugsummary
126 LANGUAGE plpgsql83 LANGUAGE plpgsql
127AS $function$84AS $function$
128BEGIN85BEGIN
129 IF BUG_ROW.duplicateof IS NOT NULL THEN86 IF btf_row.duplicateof IS NOT NULL THEN
130 RETURN;87 RETURN;
131 END IF;88 END IF;
132 RETURN QUERY89 RETURN QUERY
133 SELECT90 SELECT
134 CAST(NULL AS integer) AS id,91 CAST(NULL AS integer) AS id,
135 CAST(1 AS integer) AS count,92 CAST(1 AS integer) AS count,
136 product, productseries, distribution, distroseries,93 bug_targets.product, bug_targets.productseries,
137 sourcepackagename, person AS viewed_by, tag, status, milestone,94 bug_targets.distribution, bug_targets.distroseries,
138 importance,95 bug_targets.sourcepackagename,
139 BUG_ROW.latest_patch_uploaded IS NOT NULL AS has_patch,96 bug_viewers.viewed_by, bug_tags.tag, btf_row.status,
140 (EXISTS (97 btf_row.milestone, btf_row.importance,
141 SELECT TRUE FROM BugTask AS RBT98 btf_row.latest_patch_uploaded IS NOT NULL AS has_patch,
142 WHERE99 false AS fixed_upstream, NULL::integer AS access_policy
143 RBT.bug = tasks.bug100 FROM
144 -- This would just be 'RBT.id <> tasks.id', except101 bugsummary_targets(btf_row) as bug_targets,
145 -- that the records from tasks are summaries and not102 bugsummary_tags(btf_row) AS bug_tags,
146 -- real bugtasks, and do not have an id.103 bugsummary_viewers(btf_row) AS bug_viewers;
147 AND (RBT.product IS DISTINCT FROM tasks.product
148 OR RBT.productseries
149 IS DISTINCT FROM tasks.productseries
150 OR RBT.distribution IS DISTINCT FROM tasks.distribution
151 OR RBT.distroseries IS DISTINCT FROM tasks.distroseries
152 OR RBT.sourcepackagename
153 IS DISTINCT FROM tasks.sourcepackagename)
154 -- Flagged as INVALID, FIXCOMMITTED or FIXRELEASED
155 -- via a bugwatch, or FIXCOMMITTED or FIXRELEASED on
156 -- the product.
157 AND ((bugwatch IS NOT NULL AND status IN (17, 25, 30))
158 OR (bugwatch IS NULL AND product IS NOT NULL
159 AND status IN (25, 30))))
160 )::boolean AS fixed_upstream
161 FROM bugsummary_tasks(BUG_ROW) AS tasks
162 JOIN bugsummary_tags(BUG_ROW) AS bug_tags ON TRUE
163 LEFT OUTER JOIN bugsummary_viewers(BUG_ROW) AS bug_viewers ON TRUE;
164END;104END;
165$function$;105$function$;
166106
167CREATE OR REPLACE FUNCTION public.bugsummary_tags(bug_row bug)107CREATE OR REPLACE FUNCTION public.bugsummary_tags(btf_row bugtaskflat)
168 RETURNS SETOF bugtag108 RETURNS SETOF bugtag
169 LANGUAGE sql109 LANGUAGE sql
170 STABLE110 STABLE
171AS $function$111AS $function$
172 SELECT * FROM BugTag WHERE BugTag.bug = $1.id112 SELECT * FROM BugTag WHERE BugTag.bug = $1.bug
173 UNION ALL113 UNION ALL
174 SELECT NULL::integer, $1.id, NULL::text;114 SELECT NULL::integer, $1.bug, NULL::text;
175$function$;115$function$;
176116
177CREATE OR REPLACE FUNCTION public.bugsummary_tasks(bug_row bug)117CREATE OR REPLACE FUNCTION public.bugsummary_targets(btf_row bugtaskflat)
178 RETURNS SETOF bugtask118 RETURNS TABLE(
179 LANGUAGE plpgsql119 product integer, productseries integer, distribution integer,
180 STABLE120 distroseries integer, sourcepackagename integer)
181AS $function$121 LANGUAGE sql
182DECLARE122 IMMUTABLE
183 bt bugtask%ROWTYPE;123AS $function$
184 r record;124 -- Include a sourcepackagename-free task if this one has a
185BEGIN125 -- sourcepackagename, so package tasks are also counted in their
186 bt.bug = BUG_ROW.id;126 -- distro/series.
187127 SELECT
188 -- One row only for each target permutation - need to ignore other fields128 $1.product, $1.productseries, $1.distribution,
189 -- like date last modified to deal with conjoined masters and multiple129 $1.distroseries, $1.sourcepackagename
190 -- sourcepackage tasks in a distro.130 UNION -- Implicit DISTINCT
191 FOR r IN131 SELECT
192 SELECT132 $1.product, $1.productseries, $1.distribution,
193 product, productseries, distribution, distroseries,133 $1.distroseries, NULL;
194 sourcepackagename, status, milestone, importance, bugwatch134$function$;
195 FROM BugTask WHERE bug=BUG_ROW.id135
196 UNION -- Implicit DISTINCT136CREATE OR REPLACE FUNCTION public.bugsummary_viewers(btf_row bugtaskflat)
197 SELECT137 RETURNS TABLE(viewed_by integer)
198 product, productseries, distribution, distroseries,138 LANGUAGE sql
199 NULL, status, milestone, importance, bugwatch139 IMMUTABLE
200 FROM BugTask WHERE bug=BUG_ROW.id AND sourcepackagename IS NOT NULL140AS $function$
201 LOOP141 SELECT NULL WHERE $1.information_type IN (1, 2)
202 bt.product = r.product;142 UNION ALL
203 bt.productseries = r.productseries;143 SELECT unnest($1.access_grants)
204 bt.distribution = r.distribution;144 WHERE $1.information_type IN (3, 4, 5);
205 bt.distroseries = r.distroseries;
206 bt.sourcepackagename = r.sourcepackagename;
207 bt.status = r.status;
208 bt.milestone = r.milestone;
209 bt.importance = r.importance;
210 bt.bugwatch = r.bugwatch;
211 RETURN NEXT bt;
212 END LOOP;
213END;
214$function$;
215
216CREATE OR REPLACE FUNCTION public.bugsummary_viewers(bug_row bug)
217 RETURNS SETOF bugsubscription
218 LANGUAGE sql
219 STABLE
220AS $function$
221 SELECT *
222 FROM BugSubscription
223 WHERE
224 bugsubscription.bug=$1.id
225 AND $1.information_type IN (3, 4, 5);
226$function$;145$function$;
227146
228CREATE OR REPLACE FUNCTION public.bugtag_maintain_bug_summary()147CREATE OR REPLACE FUNCTION public.bugtag_maintain_bug_summary()
@@ -234,30 +153,30 @@
234BEGIN153BEGIN
235 IF TG_OP = 'INSERT' THEN154 IF TG_OP = 'INSERT' THEN
236 IF TG_WHEN = 'BEFORE' THEN155 IF TG_WHEN = 'BEFORE' THEN
237 PERFORM unsummarise_bug(bug_row(NEW.bug));156 PERFORM unsummarise_bug(NEW.bug);
238 ELSE157 ELSE
239 PERFORM summarise_bug(bug_row(NEW.bug));158 PERFORM summarise_bug(NEW.bug);
240 END IF;159 END IF;
241 PERFORM bug_summary_flush_temp_journal();160 PERFORM bug_summary_flush_temp_journal();
242 RETURN NEW;161 RETURN NEW;
243 ELSIF TG_OP = 'DELETE' THEN162 ELSIF TG_OP = 'DELETE' THEN
244 IF TG_WHEN = 'BEFORE' THEN163 IF TG_WHEN = 'BEFORE' THEN
245 PERFORM unsummarise_bug(bug_row(OLD.bug));164 PERFORM unsummarise_bug(OLD.bug);
246 ELSE165 ELSE
247 PERFORM summarise_bug(bug_row(OLD.bug));166 PERFORM summarise_bug(OLD.bug);
248 END IF;167 END IF;
249 PERFORM bug_summary_flush_temp_journal();168 PERFORM bug_summary_flush_temp_journal();
250 RETURN OLD;169 RETURN OLD;
251 ELSE170 ELSE
252 IF TG_WHEN = 'BEFORE' THEN171 IF TG_WHEN = 'BEFORE' THEN
253 PERFORM unsummarise_bug(bug_row(OLD.bug));172 PERFORM unsummarise_bug(OLD.bug);
254 IF OLD.bug <> NEW.bug THEN173 IF OLD.bug <> NEW.bug THEN
255 PERFORM unsummarise_bug(bug_row(NEW.bug));174 PERFORM unsummarise_bug(NEW.bug);
256 END IF;175 END IF;
257 ELSE176 ELSE
258 PERFORM summarise_bug(bug_row(OLD.bug));177 PERFORM summarise_bug(OLD.bug);
259 IF OLD.bug <> NEW.bug THEN178 IF OLD.bug <> NEW.bug THEN
260 PERFORM summarise_bug(bug_row(NEW.bug));179 PERFORM summarise_bug(NEW.bug);
261 END IF;180 END IF;
262 END IF;181 END IF;
263 PERFORM bug_summary_flush_temp_journal();182 PERFORM bug_summary_flush_temp_journal();
@@ -266,90 +185,38 @@
266END;185END;
267$function$;186$function$;
268187
269CREATE OR REPLACE FUNCTION public.bug_maintain_bug_summary()188CREATE OR REPLACE FUNCTION public.bugtaskflat_maintain_bug_summary()
270 RETURNS trigger189 RETURNS trigger
271 LANGUAGE plpgsql190 LANGUAGE plpgsql
272 SECURITY DEFINER191 SECURITY DEFINER
273 SET search_path TO public192 SET search_path TO public
274AS $function$193AS $function$
275BEGIN194BEGIN
276 -- There is no INSERT logic, as a bug will not have any summary
277 -- information until BugTask rows have been attached.
278 IF TG_OP = 'UPDATE' THEN
279 IF OLD.duplicateof IS DISTINCT FROM NEW.duplicateof
280 OR OLD.information_type IS DISTINCT FROM NEW.information_type
281 OR (OLD.latest_patch_uploaded IS NULL)
282 <> (NEW.latest_patch_uploaded IS NULL) THEN
283 PERFORM unsummarise_bug(OLD);
284 PERFORM summarise_bug(NEW);
285 END IF;
286
287 ELSIF TG_OP = 'DELETE' THEN
288 PERFORM unsummarise_bug(OLD);
289 END IF;
290
291 PERFORM bug_summary_flush_temp_journal();
292 RETURN NULL; -- Ignored - this is an AFTER trigger
293END;
294$function$;
295
296CREATE OR REPLACE FUNCTION public.bugtask_maintain_bug_summary()
297 RETURNS trigger
298 LANGUAGE plpgsql
299 SECURITY DEFINER
300 SET search_path TO public
301AS $function$
302BEGIN
303 -- This trigger only works if we are inserting, updating or deleting
304 -- a single row per statement.
305
306 -- Unlike bug_maintain_bug_summary, this trigger does not have access
307 -- to the old bug when invoked as an AFTER trigger. To work around this
308 -- we install this trigger as both a BEFORE and an AFTER trigger.
309 IF TG_OP = 'INSERT' THEN195 IF TG_OP = 'INSERT' THEN
310 IF TG_WHEN = 'BEFORE' THEN196 PERFORM bugsummary_journal_bugtaskflat(NEW, 1);
311 PERFORM unsummarise_bug(bug_row(NEW.bug));
312 ELSE
313 PERFORM summarise_bug(bug_row(NEW.bug));
314 END IF;
315 PERFORM bug_summary_flush_temp_journal();197 PERFORM bug_summary_flush_temp_journal();
316 RETURN NEW;
317
318 ELSIF TG_OP = 'DELETE' THEN198 ELSIF TG_OP = 'DELETE' THEN
319 IF TG_WHEN = 'BEFORE' THEN199 PERFORM bugsummary_journal_bugtaskflat(OLD, -1);
320 PERFORM unsummarise_bug(bug_row(OLD.bug));200 PERFORM bug_summary_flush_temp_journal();
321 ELSE201 ELSIF
322 PERFORM summarise_bug(bug_row(OLD.bug));202 NEW.product IS DISTINCT FROM OLD.product
323 END IF;203 OR NEW.productseries IS DISTINCT FROM OLD.productseries
324 PERFORM bug_summary_flush_temp_journal();204 OR NEW.distribution IS DISTINCT FROM OLD.distribution
325 RETURN OLD;205 OR NEW.distroseries IS DISTINCT FROM OLD.distroseries
326206 OR NEW.sourcepackagename IS DISTINCT FROM OLD.sourcepackagename
327 ELSE207 OR NEW.status IS DISTINCT FROM OLD.status
328 IF (OLD.product IS DISTINCT FROM NEW.product208 OR NEW.milestone IS DISTINCT FROM OLD.milestone
329 OR OLD.productseries IS DISTINCT FROM NEW.productseries209 OR NEW.importance IS DISTINCT FROM OLD.importance
330 OR OLD.distribution IS DISTINCT FROM NEW.distribution210 OR NEW.latest_patch_uploaded IS DISTINCT FROM OLD.latest_patch_uploaded
331 OR OLD.distroseries IS DISTINCT FROM NEW.distroseries211 OR NEW.information_type IS DISTINCT FROM OLD.information_type
332 OR OLD.sourcepackagename IS DISTINCT FROM NEW.sourcepackagename212 OR NEW.access_grants IS DISTINCT FROM OLD.access_grants
333 OR OLD.status IS DISTINCT FROM NEW.status213 OR NEW.access_policies IS DISTINCT FROM OLD.access_policies
334 OR OLD.importance IS DISTINCT FROM NEW.importance214 THEN
335 OR OLD.bugwatch IS DISTINCT FROM NEW.bugwatch215 PERFORM bugsummary_journal_bugtaskflat(OLD, -1);
336 OR OLD.milestone IS DISTINCT FROM NEW.milestone) THEN216 PERFORM bugsummary_journal_bugtaskflat(NEW, 1);
337217 PERFORM bug_summary_flush_temp_journal();
338 IF TG_WHEN = 'BEFORE' THEN
339 PERFORM unsummarise_bug(bug_row(OLD.bug));
340 IF OLD.bug <> NEW.bug THEN
341 PERFORM unsummarise_bug(bug_row(NEW.bug));
342 END IF;
343 ELSE
344 PERFORM summarise_bug(bug_row(OLD.bug));
345 IF OLD.bug <> NEW.bug THEN
346 PERFORM summarise_bug(bug_row(NEW.bug));
347 END IF;
348 END IF;
349 END IF;
350 PERFORM bug_summary_flush_temp_journal();
351 RETURN NEW;
352 END IF;218 END IF;
219 RETURN NULL;
353END;220END;
354$function$;221$function$;
355222
@@ -357,7 +224,6 @@
357 RETURNS void224 RETURNS void
358 LANGUAGE plpgsql225 LANGUAGE plpgsql
359AS $function$226AS $function$
360DECLARE
361BEGIN227BEGIN
362 CREATE TEMPORARY TABLE bugsummary_temp_journal (228 CREATE TEMPORARY TABLE bugsummary_temp_journal (
363 LIKE bugsummary ) ON COMMIT DROP;229 LIKE bugsummary ) ON COMMIT DROP;
@@ -368,33 +234,21 @@
368END;234END;
369$function$;235$function$;
370236
371CREATE OR REPLACE FUNCTION public.summarise_bug(bug_row bug)237CREATE OR REPLACE FUNCTION public.summarise_bug(bug integer)
372 RETURNS void238 RETURNS void
373 LANGUAGE plpgsql239 LANGUAGE plpgsql
374AS $function$240AS $function$
375DECLARE
376 d bugsummary%ROWTYPE;
377BEGIN241BEGIN
378 PERFORM ensure_bugsummary_temp_journal();242 PERFORM bugsummary_journal_bug(bug_row(bug), 1);
379 FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
380 d.count = 1;
381 PERFORM bug_summary_temp_journal_ins(d);
382 END LOOP;
383END;243END;
384$function$;244$function$;
385245
386CREATE OR REPLACE FUNCTION public.unsummarise_bug(bug_row bug)246CREATE OR REPLACE FUNCTION public.unsummarise_bug(bug integer)
387 RETURNS void247 RETURNS void
388 LANGUAGE plpgsql248 LANGUAGE plpgsql
389AS $function$249AS $function$
390DECLARE
391 d bugsummary%ROWTYPE;
392BEGIN250BEGIN
393 PERFORM ensure_bugsummary_temp_journal();251 PERFORM bugsummary_journal_bug(bug_row(bug), -1);
394 FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
395 d.count = -1;
396 PERFORM bug_summary_temp_journal_ins(d);
397 END LOOP;
398END;252END;
399$function$;253$function$;
400254
@@ -440,13 +294,14 @@
440 milestone,294 milestone,
441 importance,295 importance,
442 has_patch,296 has_patch,
443 fixed_upstream297 fixed_upstream,
298 access_policy
444 FROM BugSummaryJournal299 FROM BugSummaryJournal
445 WHERE id <= max_id300 WHERE id <= max_id
446 GROUP BY301 GROUP BY
447 product, productseries, distribution, distroseries,302 product, productseries, distribution, distroseries,
448 sourcepackagename, viewed_by, tag, status, milestone,303 sourcepackagename, viewed_by, tag, status, milestone,
449 importance, has_patch, fixed_upstream304 importance, has_patch, fixed_upstream, access_policy
450 HAVING sum(count) <> 0305 HAVING sum(count) <> 0
451 LOOP306 LOOP
452 IF d.count < 0 THEN307 IF d.count < 0 THEN
@@ -493,7 +348,8 @@
493 OR milestone = $1.milestone)348 OR milestone = $1.milestone)
494 AND importance = $1.importance349 AND importance = $1.importance
495 AND has_patch = $1.has_patch350 AND has_patch = $1.has_patch
496 AND fixed_upstream = $1.fixed_upstream;351 AND fixed_upstream = $1.fixed_upstream
352 AND access_policy IS NOT DISTINCT FROM $1.access_policy;
497 IF found THEN353 IF found THEN
498 RETURN;354 RETURN;
499 END IF;355 END IF;
@@ -504,13 +360,13 @@
504 INSERT INTO BugSummary(360 INSERT INTO BugSummary(
505 count, product, productseries, distribution,361 count, product, productseries, distribution,
506 distroseries, sourcepackagename, viewed_by, tag,362 distroseries, sourcepackagename, viewed_by, tag,
507 status, milestone,363 status, milestone, importance, has_patch, fixed_upstream,
508 importance, has_patch, fixed_upstream)364 access_policy)
509 VALUES (365 VALUES (
510 d.count, d.product, d.productseries, d.distribution,366 d.count, d.product, d.productseries, d.distribution,
511 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,367 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
512 d.status, d.milestone,368 d.status, d.milestone, d.importance, d.has_patch,
513 d.importance, d.has_patch, d.fixed_upstream);369 d.fixed_upstream, d.access_policy);
514 RETURN;370 RETURN;
515 EXCEPTION WHEN unique_violation THEN371 EXCEPTION WHEN unique_violation THEN
516 -- do nothing, and loop to try the UPDATE again372 -- do nothing, and loop to try the UPDATE again
@@ -546,9 +402,11 @@
546 OR milestone = $1.milestone)402 OR milestone = $1.milestone)
547 AND importance = $1.importance403 AND importance = $1.importance
548 AND has_patch = $1.has_patch404 AND has_patch = $1.has_patch
549 AND fixed_upstream = $1.fixed_upstream;405 AND fixed_upstream = $1.fixed_upstream
406 AND access_policy IS NOT DISTINCT FROM access_policy;
550$function$;407$function$;
551408
409DROP VIEW combinedbugsummary;
552CREATE OR REPLACE VIEW combinedbugsummary AS410CREATE OR REPLACE VIEW combinedbugsummary AS
553 SELECT411 SELECT
554 bugsummary.id, bugsummary.count, bugsummary.product,412 bugsummary.id, bugsummary.count, bugsummary.product,
@@ -556,7 +414,7 @@
556 bugsummary.distroseries, bugsummary.sourcepackagename,414 bugsummary.distroseries, bugsummary.sourcepackagename,
557 bugsummary.viewed_by, bugsummary.tag, bugsummary.status,415 bugsummary.viewed_by, bugsummary.tag, bugsummary.status,
558 bugsummary.milestone, bugsummary.importance, bugsummary.has_patch,416 bugsummary.milestone, bugsummary.importance, bugsummary.has_patch,
559 bugsummary.fixed_upstream417 bugsummary.fixed_upstream, bugsummary.access_policy
560 FROM bugsummary418 FROM bugsummary
561 UNION ALL 419 UNION ALL
562 SELECT420 SELECT
@@ -566,7 +424,45 @@
566 bugsummaryjournal.sourcepackagename, bugsummaryjournal.viewed_by,424 bugsummaryjournal.sourcepackagename, bugsummaryjournal.viewed_by,
567 bugsummaryjournal.tag, bugsummaryjournal.status,425 bugsummaryjournal.tag, bugsummaryjournal.status,
568 bugsummaryjournal.milestone, bugsummaryjournal.importance,426 bugsummaryjournal.milestone, bugsummaryjournal.importance,
569 bugsummaryjournal.has_patch, bugsummaryjournal.fixed_upstream427 bugsummaryjournal.has_patch, bugsummaryjournal.fixed_upstream,
428 bugsummaryjournal.access_policy
570 FROM bugsummaryjournal;429 FROM bugsummaryjournal;
571430
431-- With BugSummary updates now triggered by BugTaskFlat we can do away
432-- with the triggers on the tables it aggregates. Only BugTaskFlat and
433-- BugTag remain.
434DROP TRIGGER bug_maintain_bug_summary_trigger ON bug;
435DROP TRIGGER bugtask_maintain_bug_summary_before_trigger ON bugtask;
436DROP TRIGGER bugtask_maintain_bug_summary_after_trigger ON bugtask;
437DROP TRIGGER bugsubscription_maintain_bug_summary_before_trigger ON bugsubscription;
438DROP TRIGGER bugsubscription_maintain_bug_summary_after_trigger ON bugsubscription;
439DROP FUNCTION bug_maintain_bug_summary();
440DROP FUNCTION bugtask_maintain_bug_summary();
441DROP FUNCTION bugsubscription_maintain_bug_summary();
442
443CREATE TRIGGER bugtaskflat_maintain_bug_summary
444 AFTER INSERT OR UPDATE OR DELETE ON bugtaskflat
445 FOR EACH ROW EXECUTE PROCEDURE bugtaskflat_maintain_bug_summary();
446
447
448-- Dispose of various other unused functions.
449DROP FUNCTION unsummarise_bug(bug);
450DROP FUNCTION summarise_bug(bug);
451DROP FUNCTION bug_summary_temp_journal_ins(bugsummary);
452DROP FUNCTION bugsummary_journal_ins(bugsummary);
453DROP FUNCTION bugsummary_locations(bug);
454DROP FUNCTION bugsummary_tags(bug);
455DROP FUNCTION bugsummary_tasks(bug);
456DROP FUNCTION bugsummary_viewers(bug);
457
458-- Remove foreign key constraints. This table is generated from a set of
459-- constrained columns, so the constraints here serve only to make
460-- things slower.
461ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_distribution_fkey;
462ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_distroseries_fkey;
463ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_milestone_fkey;
464ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_product_fkey;
465ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_productseries_fkey;
466ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_sourcepackagename_fkey;
467
572INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 19, 0);468INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 19, 0);
573469
=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg 2012-06-26 14:23:03 +0000
+++ database/schema/security.cfg 2012-07-02 10:06:26 +0000
@@ -92,15 +92,15 @@
92public.bug_summary_dec(bugsummary) =92public.bug_summary_dec(bugsummary) =
93public.bug_summary_flush_temp_journal() =93public.bug_summary_flush_temp_journal() =
94public.bug_summary_inc(bugsummary) =94public.bug_summary_inc(bugsummary) =
95public.bug_summary_temp_journal_ins(bugsummary) =95public.bugsummary_journal_bug(bug, integer) =
96public.bugsummary_journal_ins(bugsummary) =96public.bugsummary_journal_bugtaskflat(bugtaskflat, integer) =
97public.bugsummary_locations(bug) =97public.bugsummary_locations(bugtaskflat) =
98public.bugsummary_tags(bug) =98public.bugsummary_tags(bugtaskflat) =
99public.bugsummary_tasks(bug) =99public.bugsummary_targets(bugtaskflat) =
100public.bugsummary_viewers(bug) =100public.bugsummary_viewers(bugtaskflat) =
101public.ensure_bugsummary_temp_journal() =101public.ensure_bugsummary_temp_journal() =
102public.summarise_bug(bug) =102public.summarise_bug(integer) =
103public.unsummarise_bug(bug) =103public.unsummarise_bug(integer) =
104104
105[ro]105[ro]
106groups=read106groups=read

Subscribers

People subscribed via source and target branches

to status/vote changes: