Mercurial > gemma

comparison schema/updates/1469/aggregate-gm-logs.sql @ 5538:ff95d3603e4d aggregate-gm-import-logging

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
WIP: More aggregation.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 25 Oct 2021 23:05:45 +0200
parents dc2000b807bd
children 4d815f295e57
comparison
equal deleted inserted replaced
5537:2e5e51288a6c 5538:ff95d3603e4d
6 WHERE im.kind = 'gm' 6 WHERE im.kind = 'gm'
7 ORDER BY lo.import_id, lo.time; 7 ORDER BY lo.import_id, lo.time;
8 last_id integer; 8 last_id integer;
9 last_time timestamp with time zone; 9 last_time timestamp with time zone;
10 curr_gid text; 10 curr_gid text;
11 gauge text; 11 value text;
12 joined text; 12 joined text;
13 num integer; 13 num integer;
14 BEGIN 14 BEGIN
15 15
16 CREATE TEMP TABLE filtered_logs ( 16 CREATE TEMP TABLE filtered_logs (
83 last_id, last_time, 'warn'::log_type, 83 last_id, last_time, 'warn'::log_type,
84 'Ignored measurements with value -99999: ' || joined); 84 'Ignored measurements with value -99999: ' || joined);
85 END IF; 85 END IF;
86 86
87 87
88 -- TODO: ""Missing mandatory values: " 88 -- TODO: ""Missing mandatory values: "
89 -- TODO: "Cannot convert units: " 89 -- TODO: "Cannot convert units: "
90 -- TODO: "Ignored measure codes:" 90
91 -- ignored measure codes
92 SELECT INTO joined string_agg(
93 gid || ' ('
94 || array_to_string(ARRAY(SELECT DISTINCT unnest(ign_meas_codes)), '; ')
95 || ')', ', ')
96 FROM agg_tracker
97 WHERE ign_meas_codes IS NOT NULL;
98
99 IF joined IS NOT NULL THEN
100 INSERT INTO filtered_logs VALUES (
101 last_id, last_time, 'warn'::log_type,
102 'Ignored measure codes: ' || joined);
103 END IF;
91 104
92 -- predictions 105 -- predictions
93 SELECT INTO joined string_agg( 106 SELECT INTO joined string_agg(
94 gid || ' (' || predictions || ')', ', ') 107 gid || ' (' || predictions || ')', ', ')
95 FROM agg_tracker 108 FROM agg_tracker
133 line.msg from '^Found measurements/predictions for (....................)$'); 146 line.msg from '^Found measurements/predictions for (....................)$');
134 147
135 ELSIF line.msg ~ '^Inserted \d+ measurements for ....................$' THEN 148 ELSIF line.msg ~ '^Inserted \d+ measurements for ....................$' THEN
136 num := substring( 149 num := substring(
137 line.msg from '^Inserted (\d+)')::integer; 150 line.msg from '^Inserted (\d+)')::integer;
138 gauge := substring( 151 value := substring(
139 line.msg from '^Inserted \d+ measurements for (....................)$'); 152 line.msg from '^Inserted \d+ measurements for (....................)$');
140 153
141 INSERT INTO agg_tracker (gid, measurements) VALUES (gauge, num) 154 INSERT INTO agg_tracker (gid, measurements) VALUES (value, num)
142 ON CONFLICT (gid) 155 ON CONFLICT (gid)
143 DO UPDATE SET measurements = EXCLUDED.measurements + num; 156 DO UPDATE SET measurements = EXCLUDED.measurements + num;
144 157
145 ELSIF line.msg ~ '^Ignored \d+ measurements with value -99999$' THEN 158 ELSIF line.msg ~ '^Ignored \d+ measurements with value -99999$' THEN
146 num := substring( 159 num := substring(
151 DO UPDATE SET bad_values = EXCLUDED.bad_values + num; 164 DO UPDATE SET bad_values = EXCLUDED.bad_values + num;
152 165
153 ELSIF line.msg ~ '^Inserted \d+ predictions for ....................$' THEN 166 ELSIF line.msg ~ '^Inserted \d+ predictions for ....................$' THEN
154 num := substring( 167 num := substring(
155 line.msg from '^Inserted (\d+)')::integer; 168 line.msg from '^Inserted (\d+)')::integer;
156 gauge := substring( 169 value := substring(
157 line.msg from '^Inserted \d+ predictions for (....................)$'); 170 line.msg from '^Inserted \d+ predictions for (....................)$');
158 171
159 INSERT INTO agg_tracker (gid, predictions) VALUES (gauge, num) 172 INSERT INTO agg_tracker (gid, predictions) VALUES (value, num)
160 ON CONFLICT (gid) 173 ON CONFLICT (gid)
161 DO UPDATE SET predictions = EXCLUDED.predictions + num; 174 DO UPDATE SET predictions = EXCLUDED.predictions + num;
162 175
163 ELSIF line.msg ~ '^''Reference_code'' not specified. Assuming ''ZPG''$' THEN 176 ELSIF line.msg ~ '^''Reference_code'' not specified. Assuming ''ZPG''$' THEN
164 177
165 INSERT INTO agg_tracker (gid, assume_zpg) VALUES (gauge, true) 178 INSERT INTO agg_tracker (gid, assume_zpg) VALUES (curr_gid, true)
166 ON CONFLICT (gid) 179 ON CONFLICT (gid)
167 DO UPDATE SET assume_zpg = true; 180 DO UPDATE SET assume_zpg = true;
168 181
169 ELSIF line.msg ~ '^Cannot find gauge "...................." for import$' THEN 182 ELSIF line.msg ~ '^Cannot find gauge "...................." for import$' THEN
170 gauge := substring( 183 value := substring(
171 line.msg from '^Cannot find gauge "(....................)" for import$'); 184 line.msg from '^Cannot find gauge "(....................)" for import$');
172 185
173 INSERT INTO agg_tracker (gid, unknown) VALUES (gauge, true) 186 INSERT INTO agg_tracker (gid, unknown) VALUES (value, true)
174 ON CONFLICT (gid) 187 ON CONFLICT (gid)
175 DO UPDATE SET unknown = true; 188 DO UPDATE SET unknown = true;
176 189
177 ELSIF line.msg ~ '^''Unit'' not specified. Assuming ''cm''$' THEN 190 ELSIF line.msg ~ '^''Unit'' not specified. Assuming ''cm''$' THEN
178 191
179 INSERT INTO agg_tracker (gid, assume_cm) VALUES (gauge, 1) 192 INSERT INTO agg_tracker (gid, assume_cm) VALUES (curr_gid, 1)
180 ON CONFLICT (gid) 193 ON CONFLICT (gid)
181 DO UPDATE SET assume_cm = EXCLUDED.assume_cm + 1; 194 DO UPDATE SET assume_cm = EXCLUDED.assume_cm + 1;
195
196 ELSIF line.msg ~ '^Ignored message with measure_code .+' THEN
197 value := substring(
198 line.msg from '^Ignored message with measure_code (.+)$');
199
200 INSERT INTO agg_tracker (gid, ign_meas_codes) VALUES (curr_gid, array[value])
201 ON CONFLICT (gid)
202 DO UPDATE SET ign_meas_codes =
203 array_cat(agg_tracker.ign_meas_codes, EXCLUDED.ign_meas_codes);
182 204
183 ELSIF line.msg ~ '^Importing gauge measurements took ' THEN 205 ELSIF line.msg ~ '^Importing gauge measurements took ' THEN
184 206
185 -- TODO: Flush aggregation because its likely the last entry. 207 -- TODO: Flush aggregation because its likely the last entry.
186 ELSE 208 ELSE
187 -- TODO: Handle 'Missing mandatory value at %s. Ignored (bad service)' 209 -- TODO: Handle 'Missing mandatory value at %s. Ignored (bad service)'
188 -- TODO: Handle 'Ignored message with measure_code %s'
189 -- TODO: Handle "unknown unit '%s'" 210 -- TODO: Handle "unknown unit '%s'"
190 -- Not handled, copy through .. 211 -- Not handled, copy through ..
191 INSERT INTO filtered_logs VALUES (line.import_id, line.time, line.kind, line.msg); 212 INSERT INTO filtered_logs VALUES (line.import_id, line.time, line.kind, line.msg);
192 213
193 END IF; 214 END IF;