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
1=== modified file 'database/schema/patch-2209-19-0.sql'
2--- database/schema/patch-2209-19-0.sql 2012-07-02 10:06:25 +0000
3+++ database/schema/patch-2209-19-0.sql 2012-07-02 10:06:26 +0000
4@@ -5,6 +5,12 @@
5
6 -- Journal functions. Speed is critical -- these are run by appservers.
7
8+ALTER TABLE bugsummary ALTER COLUMN fixed_upstream SET DEFAULT false;
9+ALTER TABLE bugsummaryjournal ALTER COLUMN fixed_upstream SET DEFAULT false;
10+
11+ALTER TABLE bugsummary ADD COLUMN access_policy integer;
12+ALTER TABLE bugsummaryjournal ADD COLUMN access_policy integer;
13+
14 CREATE OR REPLACE FUNCTION public.bug_summary_flush_temp_journal()
15 RETURNS void
16 LANGUAGE plpgsql
17@@ -12,217 +18,130 @@
18 DECLARE
19 d bugsummary%ROWTYPE;
20 BEGIN
21- -- may get called even though no summaries were made (for simplicity in the
22- -- callers)
23+ -- May get called even though no summaries were made (for simplicity in the
24+ -- callers). We sum the rows here to minimise the number of inserts
25+ -- into the persistent journal, as it's reasonably likely that we'll
26+ -- have -1s and +1s cancelling each other out.
27 PERFORM ensure_bugsummary_temp_journal();
28- FOR d IN SELECT * FROM bugsummary_temp_journal LOOP
29- PERFORM bugsummary_journal_ins(d);
30- END LOOP;
31+ INSERT INTO BugSummaryJournal(
32+ count, product, productseries, distribution,
33+ distroseries, sourcepackagename, viewed_by, tag,
34+ status, milestone, importance, has_patch, fixed_upstream,
35+ access_policy)
36+ SELECT
37+ SUM(count), product, productseries, distribution,
38+ distroseries, sourcepackagename, viewed_by, tag,
39+ status, milestone, importance, has_patch, fixed_upstream,
40+ access_policy
41+ FROM bugsummary_temp_journal
42+ GROUP BY
43+ product, productseries, distribution,
44+ distroseries, sourcepackagename, viewed_by, tag,
45+ status, milestone, importance, has_patch, fixed_upstream,
46+ access_policy
47+ HAVING SUM(count) != 0;
48 TRUNCATE bugsummary_temp_journal;
49 END;
50 $function$;
51
52-CREATE OR REPLACE FUNCTION public.bug_summary_temp_journal_ins(d bugsummary)
53+CREATE OR REPLACE FUNCTION public.bugsummary_journal_bugtaskflat(btf_row bugtaskflat, _count integer)
54 RETURNS void
55 LANGUAGE plpgsql
56 AS $function$
57 BEGIN
58+ PERFORM ensure_bugsummary_temp_journal();
59 INSERT INTO BugSummary_Temp_Journal(
60 count, product, productseries, distribution,
61 distroseries, sourcepackagename, viewed_by, tag,
62- status, milestone, importance, has_patch, fixed_upstream)
63- VALUES (
64- d.count, d.product, d.productseries, d.distribution,
65- d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
66- d.status, d.milestone, d.importance, d.has_patch, d.fixed_upstream);
67- RETURN;
68-END;
69-$function$;
70-
71-CREATE OR REPLACE FUNCTION public.bugsubscription_maintain_bug_summary()
72- RETURNS trigger
73- LANGUAGE plpgsql
74- SECURITY DEFINER
75- SET search_path TO public
76-AS $function$
77-BEGIN
78- -- This trigger only works if we are inserting, updating or deleting
79- -- a single row per statement.
80- IF TG_OP = 'INSERT' THEN
81- IF (bug_row(NEW.bug)).information_type IN (1, 2) THEN
82- -- Public subscriptions are not aggregated.
83- RETURN NEW;
84- END IF;
85- IF TG_WHEN = 'BEFORE' THEN
86- PERFORM unsummarise_bug(bug_row(NEW.bug));
87- ELSE
88- PERFORM summarise_bug(bug_row(NEW.bug));
89- END IF;
90- PERFORM bug_summary_flush_temp_journal();
91- RETURN NEW;
92- ELSIF TG_OP = 'DELETE' THEN
93- IF (bug_row(OLD.bug)).information_type IN (1, 2) THEN
94- -- Public subscriptions are not aggregated.
95- RETURN OLD;
96- END IF;
97- IF TG_WHEN = 'BEFORE' THEN
98- PERFORM unsummarise_bug(bug_row(OLD.bug));
99- ELSE
100- PERFORM summarise_bug(bug_row(OLD.bug));
101- END IF;
102- PERFORM bug_summary_flush_temp_journal();
103- RETURN OLD;
104- ELSE
105- IF (OLD.person IS DISTINCT FROM NEW.person
106- OR OLD.bug IS DISTINCT FROM NEW.bug) THEN
107- IF TG_WHEN = 'BEFORE' THEN
108- IF (bug_row(OLD.bug)).information_type IN (3, 4, 5) THEN
109- -- Public subscriptions are not aggregated.
110- PERFORM unsummarise_bug(bug_row(OLD.bug));
111- END IF;
112- IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).information_type IN (3, 4, 5) THEN
113- -- Public subscriptions are not aggregated.
114- PERFORM unsummarise_bug(bug_row(NEW.bug));
115- END IF;
116- ELSE
117- IF (bug_row(OLD.bug)).information_type IN (3, 4, 5) THEN
118- -- Public subscriptions are not aggregated.
119- PERFORM summarise_bug(bug_row(OLD.bug));
120- END IF;
121- IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).information_type IN (3, 4, 5) THEN
122- -- Public subscriptions are not aggregated.
123- PERFORM summarise_bug(bug_row(NEW.bug));
124- END IF;
125- END IF;
126- END IF;
127- PERFORM bug_summary_flush_temp_journal();
128- RETURN NEW;
129- END IF;
130-END;
131-$function$;
132-
133-CREATE OR REPLACE FUNCTION public.bugsummary_journal_ins(d bugsummary)
134+ status, milestone, importance, has_patch, fixed_upstream,
135+ access_policy)
136+ SELECT
137+ _count, product, productseries, distribution,
138+ distroseries, sourcepackagename, viewed_by, tag,
139+ status, milestone, importance, has_patch, fixed_upstream,
140+ access_policy
141+ FROM bugsummary_locations(btf_row);
142+END;
143+$function$;
144+
145+CREATE OR REPLACE FUNCTION public.bugsummary_journal_bug(bug_row bug, _count integer)
146 RETURNS void
147 LANGUAGE plpgsql
148 AS $function$
149+DECLARE
150+ btf_row bugtaskflat%ROWTYPE;
151 BEGIN
152- IF d.count <> 0 THEN
153- INSERT INTO BugSummaryJournal (
154- count, product, productseries, distribution,
155- distroseries, sourcepackagename, viewed_by, tag,
156- status, milestone,
157- importance, has_patch, fixed_upstream)
158- VALUES (
159- d.count, d.product, d.productseries, d.distribution,
160- d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
161- d.status, d.milestone,
162- d.importance, d.has_patch, d.fixed_upstream);
163- END IF;
164+ FOR btf_row IN SELECT * FROM bugtaskflat WHERE bug = bug_row.id
165+ LOOP
166+ PERFORM bugsummary_journal_bugtaskflat(btf_row, _count);
167+ END LOOP;
168 END;
169 $function$;
170
171-CREATE OR REPLACE FUNCTION public.bugsummary_locations(bug_row bug)
172+CREATE OR REPLACE FUNCTION public.bugsummary_locations(btf_row bugtaskflat)
173 RETURNS SETOF bugsummary
174 LANGUAGE plpgsql
175 AS $function$
176 BEGIN
177- IF BUG_ROW.duplicateof IS NOT NULL THEN
178+ IF btf_row.duplicateof IS NOT NULL THEN
179 RETURN;
180 END IF;
181 RETURN QUERY
182 SELECT
183 CAST(NULL AS integer) AS id,
184 CAST(1 AS integer) AS count,
185- product, productseries, distribution, distroseries,
186- sourcepackagename, person AS viewed_by, tag, status, milestone,
187- importance,
188- BUG_ROW.latest_patch_uploaded IS NOT NULL AS has_patch,
189- (EXISTS (
190- SELECT TRUE FROM BugTask AS RBT
191- WHERE
192- RBT.bug = tasks.bug
193- -- This would just be 'RBT.id <> tasks.id', except
194- -- that the records from tasks are summaries and not
195- -- real bugtasks, and do not have an id.
196- AND (RBT.product IS DISTINCT FROM tasks.product
197- OR RBT.productseries
198- IS DISTINCT FROM tasks.productseries
199- OR RBT.distribution IS DISTINCT FROM tasks.distribution
200- OR RBT.distroseries IS DISTINCT FROM tasks.distroseries
201- OR RBT.sourcepackagename
202- IS DISTINCT FROM tasks.sourcepackagename)
203- -- Flagged as INVALID, FIXCOMMITTED or FIXRELEASED
204- -- via a bugwatch, or FIXCOMMITTED or FIXRELEASED on
205- -- the product.
206- AND ((bugwatch IS NOT NULL AND status IN (17, 25, 30))
207- OR (bugwatch IS NULL AND product IS NOT NULL
208- AND status IN (25, 30))))
209- )::boolean AS fixed_upstream
210- FROM bugsummary_tasks(BUG_ROW) AS tasks
211- JOIN bugsummary_tags(BUG_ROW) AS bug_tags ON TRUE
212- LEFT OUTER JOIN bugsummary_viewers(BUG_ROW) AS bug_viewers ON TRUE;
213+ bug_targets.product, bug_targets.productseries,
214+ bug_targets.distribution, bug_targets.distroseries,
215+ bug_targets.sourcepackagename,
216+ bug_viewers.viewed_by, bug_tags.tag, btf_row.status,
217+ btf_row.milestone, btf_row.importance,
218+ btf_row.latest_patch_uploaded IS NOT NULL AS has_patch,
219+ false AS fixed_upstream, NULL::integer AS access_policy
220+ FROM
221+ bugsummary_targets(btf_row) as bug_targets,
222+ bugsummary_tags(btf_row) AS bug_tags,
223+ bugsummary_viewers(btf_row) AS bug_viewers;
224 END;
225 $function$;
226
227-CREATE OR REPLACE FUNCTION public.bugsummary_tags(bug_row bug)
228+CREATE OR REPLACE FUNCTION public.bugsummary_tags(btf_row bugtaskflat)
229 RETURNS SETOF bugtag
230 LANGUAGE sql
231 STABLE
232 AS $function$
233- SELECT * FROM BugTag WHERE BugTag.bug = $1.id
234- UNION ALL
235- SELECT NULL::integer, $1.id, NULL::text;
236-$function$;
237-
238-CREATE OR REPLACE FUNCTION public.bugsummary_tasks(bug_row bug)
239- RETURNS SETOF bugtask
240- LANGUAGE plpgsql
241- STABLE
242-AS $function$
243-DECLARE
244- bt bugtask%ROWTYPE;
245- r record;
246-BEGIN
247- bt.bug = BUG_ROW.id;
248-
249- -- One row only for each target permutation - need to ignore other fields
250- -- like date last modified to deal with conjoined masters and multiple
251- -- sourcepackage tasks in a distro.
252- FOR r IN
253- SELECT
254- product, productseries, distribution, distroseries,
255- sourcepackagename, status, milestone, importance, bugwatch
256- FROM BugTask WHERE bug=BUG_ROW.id
257- UNION -- Implicit DISTINCT
258- SELECT
259- product, productseries, distribution, distroseries,
260- NULL, status, milestone, importance, bugwatch
261- FROM BugTask WHERE bug=BUG_ROW.id AND sourcepackagename IS NOT NULL
262- LOOP
263- bt.product = r.product;
264- bt.productseries = r.productseries;
265- bt.distribution = r.distribution;
266- bt.distroseries = r.distroseries;
267- bt.sourcepackagename = r.sourcepackagename;
268- bt.status = r.status;
269- bt.milestone = r.milestone;
270- bt.importance = r.importance;
271- bt.bugwatch = r.bugwatch;
272- RETURN NEXT bt;
273- END LOOP;
274-END;
275-$function$;
276-
277-CREATE OR REPLACE FUNCTION public.bugsummary_viewers(bug_row bug)
278- RETURNS SETOF bugsubscription
279- LANGUAGE sql
280- STABLE
281-AS $function$
282- SELECT *
283- FROM BugSubscription
284- WHERE
285- bugsubscription.bug=$1.id
286- AND $1.information_type IN (3, 4, 5);
287+ SELECT * FROM BugTag WHERE BugTag.bug = $1.bug
288+ UNION ALL
289+ SELECT NULL::integer, $1.bug, NULL::text;
290+$function$;
291+
292+CREATE OR REPLACE FUNCTION public.bugsummary_targets(btf_row bugtaskflat)
293+ RETURNS TABLE(
294+ product integer, productseries integer, distribution integer,
295+ distroseries integer, sourcepackagename integer)
296+ LANGUAGE sql
297+ IMMUTABLE
298+AS $function$
299+ -- Include a sourcepackagename-free task if this one has a
300+ -- sourcepackagename, so package tasks are also counted in their
301+ -- distro/series.
302+ SELECT
303+ $1.product, $1.productseries, $1.distribution,
304+ $1.distroseries, $1.sourcepackagename
305+ UNION -- Implicit DISTINCT
306+ SELECT
307+ $1.product, $1.productseries, $1.distribution,
308+ $1.distroseries, NULL;
309+$function$;
310+
311+CREATE OR REPLACE FUNCTION public.bugsummary_viewers(btf_row bugtaskflat)
312+ RETURNS TABLE(viewed_by integer)
313+ LANGUAGE sql
314+ IMMUTABLE
315+AS $function$
316+ SELECT NULL WHERE $1.information_type IN (1, 2)
317+ UNION ALL
318+ SELECT unnest($1.access_grants)
319+ WHERE $1.information_type IN (3, 4, 5);
320 $function$;
321
322 CREATE OR REPLACE FUNCTION public.bugtag_maintain_bug_summary()
323@@ -234,30 +153,30 @@
324 BEGIN
325 IF TG_OP = 'INSERT' THEN
326 IF TG_WHEN = 'BEFORE' THEN
327- PERFORM unsummarise_bug(bug_row(NEW.bug));
328+ PERFORM unsummarise_bug(NEW.bug);
329 ELSE
330- PERFORM summarise_bug(bug_row(NEW.bug));
331+ PERFORM summarise_bug(NEW.bug);
332 END IF;
333 PERFORM bug_summary_flush_temp_journal();
334 RETURN NEW;
335 ELSIF TG_OP = 'DELETE' THEN
336 IF TG_WHEN = 'BEFORE' THEN
337- PERFORM unsummarise_bug(bug_row(OLD.bug));
338+ PERFORM unsummarise_bug(OLD.bug);
339 ELSE
340- PERFORM summarise_bug(bug_row(OLD.bug));
341+ PERFORM summarise_bug(OLD.bug);
342 END IF;
343 PERFORM bug_summary_flush_temp_journal();
344 RETURN OLD;
345 ELSE
346 IF TG_WHEN = 'BEFORE' THEN
347- PERFORM unsummarise_bug(bug_row(OLD.bug));
348+ PERFORM unsummarise_bug(OLD.bug);
349 IF OLD.bug <> NEW.bug THEN
350- PERFORM unsummarise_bug(bug_row(NEW.bug));
351+ PERFORM unsummarise_bug(NEW.bug);
352 END IF;
353 ELSE
354- PERFORM summarise_bug(bug_row(OLD.bug));
355+ PERFORM summarise_bug(OLD.bug);
356 IF OLD.bug <> NEW.bug THEN
357- PERFORM summarise_bug(bug_row(NEW.bug));
358+ PERFORM summarise_bug(NEW.bug);
359 END IF;
360 END IF;
361 PERFORM bug_summary_flush_temp_journal();
362@@ -266,90 +185,38 @@
363 END;
364 $function$;
365
366-CREATE OR REPLACE FUNCTION public.bug_maintain_bug_summary()
367- RETURNS trigger
368- LANGUAGE plpgsql
369- SECURITY DEFINER
370- SET search_path TO public
371-AS $function$
372-BEGIN
373- -- There is no INSERT logic, as a bug will not have any summary
374- -- information until BugTask rows have been attached.
375- IF TG_OP = 'UPDATE' THEN
376- IF OLD.duplicateof IS DISTINCT FROM NEW.duplicateof
377- OR OLD.information_type IS DISTINCT FROM NEW.information_type
378- OR (OLD.latest_patch_uploaded IS NULL)
379- <> (NEW.latest_patch_uploaded IS NULL) THEN
380- PERFORM unsummarise_bug(OLD);
381- PERFORM summarise_bug(NEW);
382- END IF;
383-
384- ELSIF TG_OP = 'DELETE' THEN
385- PERFORM unsummarise_bug(OLD);
386- END IF;
387-
388- PERFORM bug_summary_flush_temp_journal();
389- RETURN NULL; -- Ignored - this is an AFTER trigger
390-END;
391-$function$;
392-
393-CREATE OR REPLACE FUNCTION public.bugtask_maintain_bug_summary()
394- RETURNS trigger
395- LANGUAGE plpgsql
396- SECURITY DEFINER
397- SET search_path TO public
398-AS $function$
399-BEGIN
400- -- This trigger only works if we are inserting, updating or deleting
401- -- a single row per statement.
402-
403- -- Unlike bug_maintain_bug_summary, this trigger does not have access
404- -- to the old bug when invoked as an AFTER trigger. To work around this
405- -- we install this trigger as both a BEFORE and an AFTER trigger.
406+CREATE OR REPLACE FUNCTION public.bugtaskflat_maintain_bug_summary()
407+ RETURNS trigger
408+ LANGUAGE plpgsql
409+ SECURITY DEFINER
410+ SET search_path TO public
411+AS $function$
412+BEGIN
413 IF TG_OP = 'INSERT' THEN
414- IF TG_WHEN = 'BEFORE' THEN
415- PERFORM unsummarise_bug(bug_row(NEW.bug));
416- ELSE
417- PERFORM summarise_bug(bug_row(NEW.bug));
418- END IF;
419+ PERFORM bugsummary_journal_bugtaskflat(NEW, 1);
420 PERFORM bug_summary_flush_temp_journal();
421- RETURN NEW;
422-
423 ELSIF TG_OP = 'DELETE' THEN
424- IF TG_WHEN = 'BEFORE' THEN
425- PERFORM unsummarise_bug(bug_row(OLD.bug));
426- ELSE
427- PERFORM summarise_bug(bug_row(OLD.bug));
428- END IF;
429- PERFORM bug_summary_flush_temp_journal();
430- RETURN OLD;
431-
432- ELSE
433- IF (OLD.product IS DISTINCT FROM NEW.product
434- OR OLD.productseries IS DISTINCT FROM NEW.productseries
435- OR OLD.distribution IS DISTINCT FROM NEW.distribution
436- OR OLD.distroseries IS DISTINCT FROM NEW.distroseries
437- OR OLD.sourcepackagename IS DISTINCT FROM NEW.sourcepackagename
438- OR OLD.status IS DISTINCT FROM NEW.status
439- OR OLD.importance IS DISTINCT FROM NEW.importance
440- OR OLD.bugwatch IS DISTINCT FROM NEW.bugwatch
441- OR OLD.milestone IS DISTINCT FROM NEW.milestone) THEN
442-
443- IF TG_WHEN = 'BEFORE' THEN
444- PERFORM unsummarise_bug(bug_row(OLD.bug));
445- IF OLD.bug <> NEW.bug THEN
446- PERFORM unsummarise_bug(bug_row(NEW.bug));
447- END IF;
448- ELSE
449- PERFORM summarise_bug(bug_row(OLD.bug));
450- IF OLD.bug <> NEW.bug THEN
451- PERFORM summarise_bug(bug_row(NEW.bug));
452- END IF;
453- END IF;
454- END IF;
455- PERFORM bug_summary_flush_temp_journal();
456- RETURN NEW;
457+ PERFORM bugsummary_journal_bugtaskflat(OLD, -1);
458+ PERFORM bug_summary_flush_temp_journal();
459+ ELSIF
460+ NEW.product IS DISTINCT FROM OLD.product
461+ OR NEW.productseries IS DISTINCT FROM OLD.productseries
462+ OR NEW.distribution IS DISTINCT FROM OLD.distribution
463+ OR NEW.distroseries IS DISTINCT FROM OLD.distroseries
464+ OR NEW.sourcepackagename IS DISTINCT FROM OLD.sourcepackagename
465+ OR NEW.status IS DISTINCT FROM OLD.status
466+ OR NEW.milestone IS DISTINCT FROM OLD.milestone
467+ OR NEW.importance IS DISTINCT FROM OLD.importance
468+ OR NEW.latest_patch_uploaded IS DISTINCT FROM OLD.latest_patch_uploaded
469+ OR NEW.information_type IS DISTINCT FROM OLD.information_type
470+ OR NEW.access_grants IS DISTINCT FROM OLD.access_grants
471+ OR NEW.access_policies IS DISTINCT FROM OLD.access_policies
472+ THEN
473+ PERFORM bugsummary_journal_bugtaskflat(OLD, -1);
474+ PERFORM bugsummary_journal_bugtaskflat(NEW, 1);
475+ PERFORM bug_summary_flush_temp_journal();
476 END IF;
477+ RETURN NULL;
478 END;
479 $function$;
480
481@@ -357,7 +224,6 @@
482 RETURNS void
483 LANGUAGE plpgsql
484 AS $function$
485-DECLARE
486 BEGIN
487 CREATE TEMPORARY TABLE bugsummary_temp_journal (
488 LIKE bugsummary ) ON COMMIT DROP;
489@@ -368,33 +234,21 @@
490 END;
491 $function$;
492
493-CREATE OR REPLACE FUNCTION public.summarise_bug(bug_row bug)
494+CREATE OR REPLACE FUNCTION public.summarise_bug(bug integer)
495 RETURNS void
496 LANGUAGE plpgsql
497 AS $function$
498-DECLARE
499- d bugsummary%ROWTYPE;
500 BEGIN
501- PERFORM ensure_bugsummary_temp_journal();
502- FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
503- d.count = 1;
504- PERFORM bug_summary_temp_journal_ins(d);
505- END LOOP;
506+ PERFORM bugsummary_journal_bug(bug_row(bug), 1);
507 END;
508 $function$;
509
510-CREATE OR REPLACE FUNCTION public.unsummarise_bug(bug_row bug)
511+CREATE OR REPLACE FUNCTION public.unsummarise_bug(bug integer)
512 RETURNS void
513 LANGUAGE plpgsql
514 AS $function$
515-DECLARE
516- d bugsummary%ROWTYPE;
517 BEGIN
518- PERFORM ensure_bugsummary_temp_journal();
519- FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
520- d.count = -1;
521- PERFORM bug_summary_temp_journal_ins(d);
522- END LOOP;
523+ PERFORM bugsummary_journal_bug(bug_row(bug), -1);
524 END;
525 $function$;
526
527@@ -440,13 +294,14 @@
528 milestone,
529 importance,
530 has_patch,
531- fixed_upstream
532+ fixed_upstream,
533+ access_policy
534 FROM BugSummaryJournal
535 WHERE id <= max_id
536 GROUP BY
537 product, productseries, distribution, distroseries,
538 sourcepackagename, viewed_by, tag, status, milestone,
539- importance, has_patch, fixed_upstream
540+ importance, has_patch, fixed_upstream, access_policy
541 HAVING sum(count) <> 0
542 LOOP
543 IF d.count < 0 THEN
544@@ -493,7 +348,8 @@
545 OR milestone = $1.milestone)
546 AND importance = $1.importance
547 AND has_patch = $1.has_patch
548- AND fixed_upstream = $1.fixed_upstream;
549+ AND fixed_upstream = $1.fixed_upstream
550+ AND access_policy IS NOT DISTINCT FROM $1.access_policy;
551 IF found THEN
552 RETURN;
553 END IF;
554@@ -504,13 +360,13 @@
555 INSERT INTO BugSummary(
556 count, product, productseries, distribution,
557 distroseries, sourcepackagename, viewed_by, tag,
558- status, milestone,
559- importance, has_patch, fixed_upstream)
560+ status, milestone, importance, has_patch, fixed_upstream,
561+ access_policy)
562 VALUES (
563 d.count, d.product, d.productseries, d.distribution,
564 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
565- d.status, d.milestone,
566- d.importance, d.has_patch, d.fixed_upstream);
567+ d.status, d.milestone, d.importance, d.has_patch,
568+ d.fixed_upstream, d.access_policy);
569 RETURN;
570 EXCEPTION WHEN unique_violation THEN
571 -- do nothing, and loop to try the UPDATE again
572@@ -546,9 +402,11 @@
573 OR milestone = $1.milestone)
574 AND importance = $1.importance
575 AND has_patch = $1.has_patch
576- AND fixed_upstream = $1.fixed_upstream;
577+ AND fixed_upstream = $1.fixed_upstream
578+ AND access_policy IS NOT DISTINCT FROM access_policy;
579 $function$;
580
581+DROP VIEW combinedbugsummary;
582 CREATE OR REPLACE VIEW combinedbugsummary AS
583 SELECT
584 bugsummary.id, bugsummary.count, bugsummary.product,
585@@ -556,7 +414,7 @@
586 bugsummary.distroseries, bugsummary.sourcepackagename,
587 bugsummary.viewed_by, bugsummary.tag, bugsummary.status,
588 bugsummary.milestone, bugsummary.importance, bugsummary.has_patch,
589- bugsummary.fixed_upstream
590+ bugsummary.fixed_upstream, bugsummary.access_policy
591 FROM bugsummary
592 UNION ALL
593 SELECT
594@@ -566,7 +424,45 @@
595 bugsummaryjournal.sourcepackagename, bugsummaryjournal.viewed_by,
596 bugsummaryjournal.tag, bugsummaryjournal.status,
597 bugsummaryjournal.milestone, bugsummaryjournal.importance,
598- bugsummaryjournal.has_patch, bugsummaryjournal.fixed_upstream
599+ bugsummaryjournal.has_patch, bugsummaryjournal.fixed_upstream,
600+ bugsummaryjournal.access_policy
601 FROM bugsummaryjournal;
602
603+-- With BugSummary updates now triggered by BugTaskFlat we can do away
604+-- with the triggers on the tables it aggregates. Only BugTaskFlat and
605+-- BugTag remain.
606+DROP TRIGGER bug_maintain_bug_summary_trigger ON bug;
607+DROP TRIGGER bugtask_maintain_bug_summary_before_trigger ON bugtask;
608+DROP TRIGGER bugtask_maintain_bug_summary_after_trigger ON bugtask;
609+DROP TRIGGER bugsubscription_maintain_bug_summary_before_trigger ON bugsubscription;
610+DROP TRIGGER bugsubscription_maintain_bug_summary_after_trigger ON bugsubscription;
611+DROP FUNCTION bug_maintain_bug_summary();
612+DROP FUNCTION bugtask_maintain_bug_summary();
613+DROP FUNCTION bugsubscription_maintain_bug_summary();
614+
615+CREATE TRIGGER bugtaskflat_maintain_bug_summary
616+ AFTER INSERT OR UPDATE OR DELETE ON bugtaskflat
617+ FOR EACH ROW EXECUTE PROCEDURE bugtaskflat_maintain_bug_summary();
618+
619+
620+-- Dispose of various other unused functions.
621+DROP FUNCTION unsummarise_bug(bug);
622+DROP FUNCTION summarise_bug(bug);
623+DROP FUNCTION bug_summary_temp_journal_ins(bugsummary);
624+DROP FUNCTION bugsummary_journal_ins(bugsummary);
625+DROP FUNCTION bugsummary_locations(bug);
626+DROP FUNCTION bugsummary_tags(bug);
627+DROP FUNCTION bugsummary_tasks(bug);
628+DROP FUNCTION bugsummary_viewers(bug);
629+
630+-- Remove foreign key constraints. This table is generated from a set of
631+-- constrained columns, so the constraints here serve only to make
632+-- things slower.
633+ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_distribution_fkey;
634+ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_distroseries_fkey;
635+ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_milestone_fkey;
636+ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_product_fkey;
637+ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_productseries_fkey;
638+ALTER TABLE bugsummaryjournal DROP CONSTRAINT bugsummaryjournal_sourcepackagename_fkey;
639+
640 INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 19, 0);
641
642=== modified file 'database/schema/security.cfg'
643--- database/schema/security.cfg 2012-06-26 14:23:03 +0000
644+++ database/schema/security.cfg 2012-07-02 10:06:26 +0000
645@@ -92,15 +92,15 @@
646 public.bug_summary_dec(bugsummary) =
647 public.bug_summary_flush_temp_journal() =
648 public.bug_summary_inc(bugsummary) =
649-public.bug_summary_temp_journal_ins(bugsummary) =
650-public.bugsummary_journal_ins(bugsummary) =
651-public.bugsummary_locations(bug) =
652-public.bugsummary_tags(bug) =
653-public.bugsummary_tasks(bug) =
654-public.bugsummary_viewers(bug) =
655+public.bugsummary_journal_bug(bug, integer) =
656+public.bugsummary_journal_bugtaskflat(bugtaskflat, integer) =
657+public.bugsummary_locations(bugtaskflat) =
658+public.bugsummary_tags(bugtaskflat) =
659+public.bugsummary_targets(bugtaskflat) =
660+public.bugsummary_viewers(bugtaskflat) =
661 public.ensure_bugsummary_temp_journal() =
662-public.summarise_bug(bug) =
663-public.unsummarise_bug(bug) =
664+public.summarise_bug(integer) =
665+public.unsummarise_bug(integer) =
666
667 [ro]
668 groups=read

Subscribers

People subscribed via source and target branches

to status/vote changes: