1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
-- Copyright 2011 Canonical Ltd. This software is licensed under the
-- GNU Affero General Public License version 3 (see the file LICENSE).
SET client_min_messages=ERROR;
DROP FUNCTION bugsummary_rollup_journal();
CREATE OR REPLACE FUNCTION bugsummary_rollup_journal(batchsize integer=NULL)
RETURNS VOID
LANGUAGE plpgsql VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER SET search_path TO public AS
$$
DECLARE
d bugsummary%ROWTYPE;
max_id integer;
BEGIN
-- Lock so we don't content with other invokations of this
-- function. We can happily lock the BugSummary table for writes
-- as this function is the only thing that updates that table.
-- BugSummaryJournal remains unlocked so nothing should be blocked.
LOCK TABLE BugSummary IN ROW EXCLUSIVE MODE;
IF batchsize IS NULL THEN
SELECT MAX(id) INTO max_id FROM BugSummaryJournal;
ELSE
SELECT MAX(id) INTO max_id FROM (
SELECT id FROM BugSummaryJournal ORDER BY id LIMIT batchsize
) AS Whatever;
END IF;
FOR d IN
SELECT
NULL as id,
SUM(count),
product,
productseries,
distribution,
distroseries,
sourcepackagename,
viewed_by,
tag,
status,
milestone,
importance,
has_patch,
fixed_upstream
FROM BugSummaryJournal
WHERE id <= max_id
GROUP BY
product, productseries, distribution, distroseries,
sourcepackagename, viewed_by, tag, status, milestone,
importance, has_patch, fixed_upstream
HAVING sum(count) <> 0
LOOP
IF d.count < 0 THEN
PERFORM bug_summary_dec(d);
ELSIF d.count > 0 THEN
PERFORM bug_summary_inc(d);
END IF;
END LOOP;
-- Clean out any counts we reduced to 0.
DELETE FROM BugSummary WHERE count=0;
-- Clean out the journal entries we have handled.
DELETE FROM BugSummaryJournal WHERE id <= max_id;
END;
$$;
COMMENT ON FUNCTION bugsummary_rollup_journal(integer) IS
'Collate and migrate rows from BugSummaryJournal to BugSummary';
CREATE OR REPLACE FUNCTION bug_summary_dec(bugsummary) RETURNS VOID
LANGUAGE SQL AS
$$
-- We own the row reference, so in the absence of bugs this cannot
-- fail - just decrement the row.
UPDATE BugSummary SET count = count + $1.count
WHERE
((product IS NULL AND $1.product IS NULL)
OR product = $1.product)
AND ((productseries IS NULL AND $1.productseries IS NULL)
OR productseries = $1.productseries)
AND ((distribution IS NULL AND $1.distribution IS NULL)
OR distribution = $1.distribution)
AND ((distroseries IS NULL AND $1.distroseries IS NULL)
OR distroseries = $1.distroseries)
AND ((sourcepackagename IS NULL AND $1.sourcepackagename IS NULL)
OR sourcepackagename = $1.sourcepackagename)
AND ((viewed_by IS NULL AND $1.viewed_by IS NULL)
OR viewed_by = $1.viewed_by)
AND ((tag IS NULL AND $1.tag IS NULL)
OR tag = $1.tag)
AND status = $1.status
AND ((milestone IS NULL AND $1.milestone IS NULL)
OR milestone = $1.milestone)
AND importance = $1.importance
AND has_patch = $1.has_patch
AND fixed_upstream = $1.fixed_upstream;
$$;
CREATE OR REPLACE FUNCTION bug_summary_inc(d bugsummary) RETURNS VOID
LANGUAGE plpgsql AS
$$
BEGIN
-- Shameless adaption from postgresql manual
LOOP
-- first try to update the row
UPDATE BugSummary SET count = count + d.count
WHERE
((product IS NULL AND $1.product IS NULL)
OR product = $1.product)
AND ((productseries IS NULL AND $1.productseries IS NULL)
OR productseries = $1.productseries)
AND ((distribution IS NULL AND $1.distribution IS NULL)
OR distribution = $1.distribution)
AND ((distroseries IS NULL AND $1.distroseries IS NULL)
OR distroseries = $1.distroseries)
AND ((sourcepackagename IS NULL AND $1.sourcepackagename IS NULL)
OR sourcepackagename = $1.sourcepackagename)
AND ((viewed_by IS NULL AND $1.viewed_by IS NULL)
OR viewed_by = $1.viewed_by)
AND ((tag IS NULL AND $1.tag IS NULL)
OR tag = $1.tag)
AND status = $1.status
AND ((milestone IS NULL AND $1.milestone IS NULL)
OR milestone = $1.milestone)
AND importance = $1.importance
AND has_patch = $1.has_patch
AND fixed_upstream = $1.fixed_upstream;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO BugSummary(
count, product, productseries, distribution,
distroseries, sourcepackagename, viewed_by, tag,
status, milestone,
importance, has_patch, fixed_upstream)
VALUES (
d.count, d.product, d.productseries, d.distribution,
d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
d.status, d.milestone,
d.importance, d.has_patch, d.fixed_upstream);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$;
INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 76, 4);
|