|
13175.3.1
by Robert Collins
Do not spuriously summarise in public bug subscriptions. |
1 |
-- Copyright 2011 Canonical Ltd. This software is licensed under the
|
2 |
-- GNU Affero General Public License version 3 (see the file LICENSE).
|
|
3 |
||
4 |
SET client_min_messages=ERROR; |
|
5 |
||
|
13175.3.8
by Stuart Bishop
Optimize real BugSummary _inc and _dec functions too |
6 |
CREATE OR REPLACE FUNCTION bug_summary_inc(d bugsummary) RETURNS VOID |
7 |
LANGUAGE plpgsql AS |
|
8 |
$$
|
|
9 |
BEGIN
|
|
10 |
-- Shameless adaption from postgresql manual
|
|
11 |
LOOP
|
|
12 |
-- first try to update the row
|
|
13 |
UPDATE BugSummary SET count = count + 1 |
|
14 |
WHERE
|
|
15 |
((d.product IS NULL AND product IS NULL) |
|
16 |
OR product = d.product) |
|
17 |
AND ((d.productseries IS NULL AND productseries IS NULL) |
|
18 |
OR productseries = d.productseries) |
|
19 |
AND ((d.distribution IS NULL AND distribution IS NULL) |
|
20 |
OR distribution = d.distribution) |
|
21 |
AND ((d.distroseries IS NULL AND distroseries IS NULL) |
|
22 |
OR distroseries = d.distroseries) |
|
23 |
AND ((d.sourcepackagename IS NULL AND sourcepackagename IS NULL) |
|
24 |
OR sourcepackagename = d.sourcepackagename) |
|
25 |
AND ((d.viewed_by IS NULL AND viewed_by IS NULL) |
|
26 |
OR viewed_by = d.viewed_by) |
|
27 |
AND ((d.tag IS NULL AND tag IS NULL) |
|
28 |
OR tag = d.tag) |
|
29 |
AND ((d.status IS NULL AND status IS NULL) |
|
30 |
OR status = d.status) |
|
31 |
AND ((d.milestone IS NULL AND milestone IS NULL) |
|
32 |
OR milestone = d.milestone); |
|
33 |
IF found THEN |
|
34 |
RETURN; |
|
35 |
END IF; |
|
36 |
-- not there, so try to insert the key
|
|
37 |
-- if someone else inserts the same key concurrently,
|
|
38 |
-- we could get a unique-key failure
|
|
39 |
BEGIN
|
|
40 |
INSERT INTO BugSummary( |
|
41 |
count, product, productseries, distribution, |
|
42 |
distroseries, sourcepackagename, viewed_by, tag, |
|
43 |
status, milestone) |
|
44 |
VALUES ( |
|
45 |
1, d.product, d.productseries, d.distribution, |
|
46 |
d.distroseries, d.sourcepackagename, d.viewed_by, d.tag, |
|
47 |
d.status, d.milestone); |
|
48 |
RETURN; |
|
49 |
EXCEPTION WHEN unique_violation THEN |
|
50 |
-- do nothing, and loop to try the UPDATE again
|
|
51 |
END; |
|
52 |
END LOOP; |
|
53 |
END; |
|
54 |
$$; |
|
55 |
||
56 |
COMMENT ON FUNCTION bug_summary_inc(bugsummary) IS |
|
57 |
'UPSERT into bugsummary incrementing one row'; |
|
58 |
||
59 |
CREATE OR REPLACE FUNCTION bug_summary_dec(bugsummary) RETURNS VOID |
|
60 |
LANGUAGE SQL AS |
|
61 |
$$
|
|
62 |
-- We own the row reference, so in the absence of bugs this cannot
|
|
63 |
-- fail - just decrement the row.
|
|
64 |
UPDATE BugSummary SET count = count - 1 |
|
65 |
WHERE
|
|
66 |
(($1.product IS NULL AND product IS NULL) |
|
67 |
OR product = $1.product) |
|
68 |
AND (($1.productseries IS NULL AND productseries IS NULL) |
|
69 |
OR productseries = $1.productseries) |
|
70 |
AND (($1.distribution IS NULL AND distribution IS NULL) |
|
71 |
OR distribution = $1.distribution) |
|
72 |
AND (($1.distroseries IS NULL AND distroseries IS NULL) |
|
73 |
OR distroseries = $1.distroseries) |
|
74 |
AND (($1.sourcepackagename IS NULL AND sourcepackagename IS NULL) |
|
75 |
OR sourcepackagename = $1.sourcepackagename) |
|
76 |
AND (($1.viewed_by IS NULL AND viewed_by IS NULL) |
|
77 |
OR viewed_by = $1.viewed_by) |
|
78 |
AND (($1.tag IS NULL AND tag IS NULL) |
|
79 |
OR tag = $1.tag) |
|
80 |
AND (($1.status IS NULL AND status IS NULL) |
|
81 |
OR status = $1.status) |
|
82 |
AND (($1.milestone IS NULL AND milestone IS NULL) |
|
83 |
OR milestone = $1.milestone); |
|
84 |
-- gc the row (perhaps should be garbo but easy enough to add here:
|
|
85 |
DELETE FROM bugsummary |
|
86 |
WHERE
|
|
87 |
count=0 |
|
88 |
AND (($1.product IS NULL AND product IS NULL) |
|
89 |
OR product = $1.product) |
|
90 |
AND (($1.productseries IS NULL AND productseries IS NULL) |
|
91 |
OR productseries = $1.productseries) |
|
92 |
AND (($1.distribution IS NULL AND distribution IS NULL) |
|
93 |
OR distribution = $1.distribution) |
|
94 |
AND (($1.distroseries IS NULL AND distroseries IS NULL) |
|
95 |
OR distroseries = $1.distroseries) |
|
96 |
AND (($1.sourcepackagename IS NULL AND sourcepackagename IS NULL) |
|
97 |
OR sourcepackagename = $1.sourcepackagename) |
|
98 |
AND (($1.viewed_by IS NULL AND viewed_by IS NULL) |
|
99 |
OR viewed_by = $1.viewed_by) |
|
100 |
AND (($1.tag IS NULL AND tag IS NULL) |
|
101 |
OR tag = $1.tag) |
|
102 |
AND (($1.status IS NULL AND status IS NULL) |
|
103 |
OR status = $1.status) |
|
104 |
AND (($1.milestone IS NULL AND milestone IS NULL) |
|
105 |
OR milestone = $1.milestone); |
|
106 |
-- If its not found then someone else also dec'd and won concurrently.
|
|
107 |
$$; |
|
108 |
||
109 |
||
|
13175.3.2
by Robert Collins
Rather than taking a table lock, do row level locking and update only the rows which a net different would have applied to. |
110 |
-- bad comment fixup
|
111 |
COMMENT ON FUNCTION bug_summary_dec(bugsummary) IS |
|
112 |
'UPSERT into bugsummary incrementing one row'; |
|
113 |
||
114 |
CREATE OR REPLACE FUNCTION ensure_bugsummary_temp_journal() RETURNS VOID |
|
115 |
LANGUAGE plpgsql VOLATILE AS |
|
116 |
$$
|
|
117 |
DECLARE
|
|
118 |
BEGIN
|
|
119 |
CREATE TEMPORARY TABLE bugsummary_temp_journal ( |
|
120 |
LIKE bugsummary ) ON COMMIT DROP; |
|
121 |
ALTER TABLE bugsummary_temp_journal ALTER COLUMN id DROP NOT NULL; |
|
122 |
-- For safety use a unique index.
|
|
123 |
CREATE UNIQUE INDEX bugsummary__temp_journal__dimensions__unique ON bugsummary_temp_journal ( |
|
124 |
status, |
|
125 |
COALESCE(product, (-1)), |
|
126 |
COALESCE(productseries, (-1)), |
|
127 |
COALESCE(distribution, (-1)), |
|
128 |
COALESCE(distroseries, (-1)), |
|
129 |
COALESCE(sourcepackagename, (-1)), |
|
130 |
COALESCE(viewed_by, (-1)), |
|
131 |
COALESCE(milestone, (-1)), |
|
132 |
COALESCE(tag, (''))); |
|
133 |
EXCEPTION
|
|
134 |
WHEN duplicate_table THEN |
|
135 |
NULL; |
|
136 |
END; |
|
137 |
$$; |
|
138 |
||
139 |
COMMENT ON FUNCTION ensure_bugsummary_temp_journal() IS |
|
140 |
'Create a temporary table bugsummary_temp_journal if it does not exist.'; |
|
141 |
||
142 |
||
143 |
CREATE OR REPLACE FUNCTION bug_summary_temp_journal_dec(d bugsummary) RETURNS VOID |
|
144 |
LANGUAGE plpgsql AS |
|
145 |
$$
|
|
146 |
BEGIN
|
|
147 |
-- We own the row reference, so in the absence of bugs this cannot
|
|
148 |
-- fail - just decrement the row.
|
|
149 |
UPDATE BugSummary_Temp_Journal SET count = count - 1 |
|
150 |
WHERE
|
|
|
13175.3.4
by Stuart Bishop
Belt queries with the ugly stick until they use indexes |
151 |
((d.product IS NULL AND product IS NULL) |
152 |
OR product = d.product) |
|
153 |
AND ((d.productseries IS NULL AND productseries IS NULL) |
|
154 |
OR productseries = d.productseries) |
|
155 |
AND ((d.distribution IS NULL AND distribution IS NULL) |
|
156 |
OR distribution = d.distribution) |
|
157 |
AND ((d.distroseries IS NULL AND distroseries IS NULL) |
|
158 |
OR distroseries = d.distroseries) |
|
159 |
AND ((d.sourcepackagename IS NULL AND sourcepackagename IS NULL) |
|
160 |
OR sourcepackagename = d.sourcepackagename) |
|
161 |
AND ((d.viewed_by IS NULL AND viewed_by IS NULL) |
|
162 |
OR viewed_by = d.viewed_by) |
|
163 |
AND ((d.tag IS NULL AND tag IS NULL) |
|
164 |
OR tag = d.tag) |
|
165 |
AND ((d.status IS NULL AND status IS NULL) |
|
166 |
OR status = d.status) |
|
167 |
AND ((d.milestone IS NULL AND milestone IS NULL) |
|
168 |
OR milestone = d.milestone); |
|
|
13175.3.2
by Robert Collins
Rather than taking a table lock, do row level locking and update only the rows which a net different would have applied to. |
169 |
IF found THEN |
170 |
RETURN; |
|
171 |
END IF; |
|
172 |
-- not there, so try to insert the key
|
|
173 |
INSERT INTO BugSummary_Temp_Journal( |
|
174 |
count, product, productseries, distribution, |
|
175 |
distroseries, sourcepackagename, viewed_by, tag, |
|
176 |
status, milestone) |
|
177 |
VALUES ( |
|
178 |
-1, d.product, d.productseries, d.distribution, |
|
179 |
d.distroseries, d.sourcepackagename, d.viewed_by, d.tag, |
|
180 |
d.status, d.milestone); |
|
181 |
RETURN; |
|
182 |
END; |
|
183 |
$$; |
|
184 |
||
185 |
COMMENT ON FUNCTION bug_summary_temp_journal_dec(bugsummary) IS |
|
186 |
'UPSERT into bugsummary_temp_journal decrementing one row'; |
|
187 |
||
188 |
CREATE OR REPLACE FUNCTION bug_summary_temp_journal_inc(d bugsummary) RETURNS VOID |
|
189 |
LANGUAGE plpgsql AS |
|
190 |
$$
|
|
191 |
BEGIN
|
|
192 |
-- first try to update the row
|
|
193 |
UPDATE BugSummary_Temp_Journal SET count = count + 1 |
|
194 |
WHERE
|
|
|
13175.3.4
by Stuart Bishop
Belt queries with the ugly stick until they use indexes |
195 |
((d.product IS NULL AND product IS NULL) |
196 |
OR product = d.product) |
|
197 |
AND ((d.productseries IS NULL AND productseries IS NULL) |
|
198 |
OR productseries = d.productseries) |
|
199 |
AND ((d.distribution IS NULL AND distribution IS NULL) |
|
200 |
OR distribution = d.distribution) |
|
201 |
AND ((d.distroseries IS NULL AND distroseries IS NULL) |
|
202 |
OR distroseries = d.distroseries) |
|
203 |
AND ((d.sourcepackagename IS NULL AND sourcepackagename IS NULL) |
|
204 |
OR sourcepackagename = d.sourcepackagename) |
|
205 |
AND ((d.viewed_by IS NULL AND viewed_by IS NULL) |
|
206 |
OR viewed_by = d.viewed_by) |
|
207 |
AND ((d.tag IS NULL AND tag IS NULL) |
|
208 |
OR tag = d.tag) |
|
209 |
AND ((d.status IS NULL AND status IS NULL) |
|
210 |
OR status = d.status) |
|
211 |
AND ((d.milestone IS NULL AND milestone IS NULL) |
|
212 |
OR milestone = d.milestone); |
|
|
13175.3.2
by Robert Collins
Rather than taking a table lock, do row level locking and update only the rows which a net different would have applied to. |
213 |
IF found THEN |
214 |
RETURN; |
|
215 |
END IF; |
|
216 |
-- not there, so try to insert the key
|
|
217 |
INSERT INTO BugSummary_Temp_Journal( |
|
218 |
count, product, productseries, distribution, |
|
219 |
distroseries, sourcepackagename, viewed_by, tag, |
|
220 |
status, milestone) |
|
221 |
VALUES ( |
|
222 |
1, d.product, d.productseries, d.distribution, |
|
223 |
d.distroseries, d.sourcepackagename, d.viewed_by, d.tag, |
|
224 |
d.status, d.milestone); |
|
225 |
RETURN; |
|
226 |
END; |
|
227 |
$$; |
|
228 |
||
229 |
COMMENT ON FUNCTION bug_summary_temp_journal_inc(bugsummary) IS |
|
230 |
'UPSERT into bugsummary incrementing one row'; |
|
231 |
||
232 |
CREATE OR REPLACE FUNCTION bug_summary_flush_temp_journal() RETURNS VOID |
|
233 |
LANGUAGE plpgsql VOLATILE AS |
|
234 |
$$
|
|
235 |
DECLARE
|
|
236 |
d bugsummary%ROWTYPE; |
|
237 |
BEGIN
|
|
238 |
-- may get called even though no summaries were made (for simplicity in the
|
|
239 |
-- callers)
|
|
240 |
PERFORM ensure_bugsummary_temp_journal(); |
|
241 |
FOR d IN SELECT * FROM bugsummary_temp_journal LOOP |
|
242 |
IF d.count < 0 THEN |
|
243 |
PERFORM bug_summary_dec(d); |
|
244 |
ELSIF d.count > 0 THEN |
|
245 |
PERFORM bug_summary_inc(d); |
|
246 |
END IF; |
|
247 |
END LOOP; |
|
248 |
DELETE FROM bugsummary_temp_journal; |
|
249 |
END; |
|
250 |
$$; |
|
251 |
||
252 |
COMMENT ON FUNCTION bug_summary_flush_temp_journal() IS |
|
253 |
'flush the temporary bugsummary journal into the bugsummary table'; |
|
254 |
||
255 |
CREATE OR REPLACE FUNCTION unsummarise_bug(BUG_ROW bug) RETURNS VOID |
|
256 |
LANGUAGE plpgsql VOLATILE AS |
|
257 |
$$
|
|
258 |
DECLARE
|
|
259 |
d bugsummary%ROWTYPE; |
|
260 |
BEGIN
|
|
261 |
PERFORM ensure_bugsummary_temp_journal(); |
|
262 |
FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP |
|
263 |
PERFORM bug_summary_temp_journal_dec(d); |
|
264 |
END LOOP; |
|
265 |
END; |
|
266 |
$$; |
|
267 |
||
268 |
CREATE OR REPLACE FUNCTION summarise_bug(BUG_ROW bug) RETURNS VOID |
|
269 |
LANGUAGE plpgsql VOLATILE AS |
|
270 |
$$
|
|
271 |
DECLARE
|
|
272 |
d bugsummary%ROWTYPE; |
|
273 |
BEGIN
|
|
274 |
PERFORM ensure_bugsummary_temp_journal(); |
|
275 |
FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP |
|
276 |
PERFORM bug_summary_temp_journal_inc(d); |
|
277 |
END LOOP; |
|
278 |
END; |
|
279 |
$$; |
|
280 |
||
281 |
-- fixed to summarise less often and use the journal.
|
|
|
13175.3.1
by Robert Collins
Do not spuriously summarise in public bug subscriptions. |
282 |
CREATE OR REPLACE FUNCTION bugsubscription_maintain_bug_summary() |
283 |
RETURNS TRIGGER LANGUAGE plpgsql VOLATILE |
|
284 |
SECURITY DEFINER SET search_path TO public AS |
|
285 |
$$
|
|
286 |
BEGIN
|
|
287 |
-- This trigger only works if we are inserting, updating or deleting
|
|
288 |
-- a single row per statement.
|
|
289 |
IF TG_OP = 'INSERT' THEN |
|
290 |
IF NOT (bug_row(NEW.bug)).private THEN |
|
291 |
-- Public subscriptions are not aggregated.
|
|
292 |
RETURN NEW; |
|
293 |
END IF; |
|
294 |
IF TG_WHEN = 'BEFORE' THEN |
|
295 |
PERFORM unsummarise_bug(bug_row(NEW.bug)); |
|
296 |
ELSE
|
|
297 |
PERFORM summarise_bug(bug_row(NEW.bug)); |
|
298 |
END IF; |
|
|
13175.3.2
by Robert Collins
Rather than taking a table lock, do row level locking and update only the rows which a net different would have applied to. |
299 |
PERFORM bug_summary_flush_temp_journal(); |
|
13175.3.1
by Robert Collins
Do not spuriously summarise in public bug subscriptions. |
300 |
RETURN NEW; |
301 |
ELSIF TG_OP = 'DELETE' THEN |
|
302 |
IF NOT (bug_row(OLD.bug)).private THEN |
|
303 |
-- Public subscriptions are not aggregated.
|
|
304 |
RETURN OLD; |
|
305 |
END IF; |
|
306 |
IF TG_WHEN = 'BEFORE' THEN |
|
307 |
PERFORM unsummarise_bug(bug_row(OLD.bug)); |
|
308 |
ELSE
|
|
309 |
PERFORM summarise_bug(bug_row(OLD.bug)); |
|
310 |
END IF; |
|
|
13175.3.2
by Robert Collins
Rather than taking a table lock, do row level locking and update only the rows which a net different would have applied to. |
311 |
PERFORM bug_summary_flush_temp_journal(); |
|
13175.3.1
by Robert Collins
Do not spuriously summarise in public bug subscriptions. |
312 |
RETURN OLD; |
313 |
ELSE
|
|
314 |
IF (OLD.person IS DISTINCT FROM NEW.person |
|
315 |
OR OLD.bug IS DISTINCT FROM NEW.bug) THEN |
|
316 |
IF TG_WHEN = 'BEFORE' THEN |
|
317 |
IF (bug_row(OLD.bug)).private THEN |
|
318 |
-- Public subscriptions are not aggregated.
|
|
319 |
PERFORM unsummarise_bug(bug_row(OLD.bug)); |
|
320 |
END IF; |
|
321 |
IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).private THEN |
|
322 |
-- Public subscriptions are not aggregated.
|
|
323 |
PERFORM unsummarise_bug(bug_row(NEW.bug)); |
|
324 |
END IF; |
|
325 |
ELSE
|
|
326 |
IF (bug_row(OLD.bug)).private THEN |
|
327 |
-- Public subscriptions are not aggregated.
|
|
328 |
PERFORM summarise_bug(bug_row(OLD.bug)); |
|
329 |
END IF; |
|
330 |
IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).private THEN |
|
331 |
-- Public subscriptions are not aggregated.
|
|
332 |
PERFORM summarise_bug(bug_row(NEW.bug)); |
|
333 |
END IF; |
|
334 |
END IF; |
|
335 |
END IF; |
|
|
13175.3.2
by Robert Collins
Rather than taking a table lock, do row level locking and update only the rows which a net different would have applied to. |
336 |
PERFORM bug_summary_flush_temp_journal(); |
337 |
RETURN NEW; |
|
338 |
END IF; |
|
339 |
END; |
|
340 |
$$; |
|
341 |
||
342 |
-- fixed to use the journal
|
|
343 |
CREATE OR REPLACE FUNCTION bugtag_maintain_bug_summary() RETURNS TRIGGER |
|
344 |
LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS |
|
345 |
$$
|
|
346 |
BEGIN
|
|
347 |
IF TG_OP = 'INSERT' THEN |
|
348 |
IF TG_WHEN = 'BEFORE' THEN |
|
349 |
PERFORM unsummarise_bug(bug_row(NEW.bug)); |
|
350 |
ELSE
|
|
351 |
PERFORM summarise_bug(bug_row(NEW.bug)); |
|
352 |
END IF; |
|
353 |
PERFORM bug_summary_flush_temp_journal(); |
|
354 |
RETURN NEW; |
|
355 |
ELSIF TG_OP = 'DELETE' THEN |
|
356 |
IF TG_WHEN = 'BEFORE' THEN |
|
357 |
PERFORM unsummarise_bug(bug_row(OLD.bug)); |
|
358 |
ELSE
|
|
359 |
PERFORM summarise_bug(bug_row(OLD.bug)); |
|
360 |
END IF; |
|
361 |
PERFORM bug_summary_flush_temp_journal(); |
|
362 |
RETURN OLD; |
|
363 |
ELSE
|
|
364 |
IF TG_WHEN = 'BEFORE' THEN |
|
365 |
PERFORM unsummarise_bug(bug_row(OLD.bug)); |
|
366 |
IF OLD.bug <> NEW.bug THEN |
|
367 |
PERFORM unsummarise_bug(bug_row(NEW.bug)); |
|
368 |
END IF; |
|
369 |
ELSE
|
|
370 |
PERFORM summarise_bug(bug_row(OLD.bug)); |
|
371 |
IF OLD.bug <> NEW.bug THEN |
|
372 |
PERFORM summarise_bug(bug_row(NEW.bug)); |
|
373 |
END IF; |
|
374 |
END IF; |
|
375 |
PERFORM bug_summary_flush_temp_journal(); |
|
376 |
RETURN NEW; |
|
377 |
END IF; |
|
378 |
END; |
|
379 |
$$; |
|
380 |
||
381 |
-- fixed to use the journal
|
|
382 |
CREATE OR REPLACE FUNCTION bug_maintain_bug_summary() RETURNS TRIGGER |
|
383 |
LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS |
|
384 |
$$
|
|
385 |
BEGIN
|
|
386 |
-- There is no INSERT logic, as a bug will not have any summary
|
|
387 |
-- information until BugTask rows have been attached.
|
|
388 |
IF TG_OP = 'UPDATE' THEN |
|
389 |
IF OLD.duplicateof IS DISTINCT FROM NEW.duplicateof |
|
390 |
OR OLD.private IS DISTINCT FROM NEW.private THEN |
|
391 |
PERFORM unsummarise_bug(OLD); |
|
392 |
PERFORM summarise_bug(NEW); |
|
393 |
END IF; |
|
394 |
||
395 |
ELSIF TG_OP = 'DELETE' THEN |
|
396 |
PERFORM unsummarise_bug(OLD); |
|
397 |
END IF; |
|
398 |
||
399 |
PERFORM bug_summary_flush_temp_journal(); |
|
400 |
RETURN NULL; -- Ignored - this is an AFTER trigger |
|
401 |
END; |
|
402 |
$$; |
|
403 |
||
404 |
-- fixed to use the journal
|
|
405 |
CREATE OR REPLACE FUNCTION bugtask_maintain_bug_summary() RETURNS TRIGGER |
|
406 |
LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS |
|
407 |
$$
|
|
408 |
BEGIN
|
|
409 |
-- This trigger only works if we are inserting, updating or deleting
|
|
410 |
-- a single row per statement.
|
|
411 |
||
412 |
-- Unlike bug_maintain_bug_summary, this trigger does not have access
|
|
413 |
-- to the old bug when invoked as an AFTER trigger. To work around this
|
|
414 |
-- we install this trigger as both a BEFORE and an AFTER trigger.
|
|
415 |
IF TG_OP = 'INSERT' THEN |
|
416 |
IF TG_WHEN = 'BEFORE' THEN |
|
417 |
PERFORM unsummarise_bug(bug_row(NEW.bug)); |
|
418 |
ELSE
|
|
419 |
PERFORM summarise_bug(bug_row(NEW.bug)); |
|
420 |
END IF; |
|
421 |
PERFORM bug_summary_flush_temp_journal(); |
|
422 |
RETURN NEW; |
|
423 |
||
424 |
ELSIF TG_OP = 'DELETE' THEN |
|
425 |
IF TG_WHEN = 'BEFORE' THEN |
|
426 |
PERFORM unsummarise_bug(bug_row(OLD.bug)); |
|
427 |
ELSE
|
|
428 |
PERFORM summarise_bug(bug_row(OLD.bug)); |
|
429 |
END IF; |
|
430 |
PERFORM bug_summary_flush_temp_journal(); |
|
431 |
RETURN OLD; |
|
432 |
||
433 |
ELSE
|
|
434 |
IF (OLD.product IS DISTINCT FROM NEW.product |
|
435 |
OR OLD.productseries IS DISTINCT FROM NEW.productseries |
|
436 |
OR OLD.distribution IS DISTINCT FROM NEW.distribution |
|
437 |
OR OLD.distroseries IS DISTINCT FROM NEW.distroseries |
|
438 |
OR OLD.sourcepackagename IS DISTINCT FROM NEW.sourcepackagename |
|
439 |
OR OLD.status IS DISTINCT FROM NEW.status |
|
440 |
OR OLD.milestone IS DISTINCT FROM NEW.milestone) THEN |
|
441 |
IF TG_WHEN = 'BEFORE' THEN |
|
442 |
PERFORM unsummarise_bug(bug_row(OLD.bug)); |
|
443 |
IF OLD.bug <> NEW.bug THEN |
|
444 |
PERFORM unsummarise_bug(bug_row(NEW.bug)); |
|
445 |
END IF; |
|
446 |
ELSE
|
|
447 |
PERFORM summarise_bug(bug_row(OLD.bug)); |
|
448 |
IF OLD.bug <> NEW.bug THEN |
|
449 |
PERFORM summarise_bug(bug_row(NEW.bug)); |
|
450 |
END IF; |
|
451 |
END IF; |
|
452 |
END IF; |
|
453 |
PERFORM bug_summary_flush_temp_journal(); |
|
454 |
RETURN NEW; |
|
455 |
END IF; |
|
456 |
END; |
|
457 |
$$; |
|
|
13175.3.1
by Robert Collins
Do not spuriously summarise in public bug subscriptions. |
458 |
|
|
13175.3.4
by Stuart Bishop
Belt queries with the ugly stick until they use indexes |
459 |
|
|
13175.3.6
by Stuart Bishop
Remove live application cruft |
460 |
INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 63, 1); |