Mercurial > gemma

annotate schema/updates/1469/aggregate-gm-logs.sql @ 5535:1fba5766d820 aggregate-gm-import-logging

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
WIP: More on migration script.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 25 Oct 2021 20:44:15 +0200
parents cb62985ec252
children dc2000b807bd
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 DO $$
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2 DECLARE
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 logs CURSOR FOR SELECT
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 lo.* FROM import.imports im JOIN import.import_logs lo
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 ON lo.import_id = im.id
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 WHERE im.kind = 'gm'
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 ORDER BY lo.import_id, lo.time;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 last_id integer;
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
9 last_time timestamp with time zone;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
10 curr_gid text;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
11 gauge text;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
12 num integer;
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13 BEGIN
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15 CREATE TEMP TABLE filtered_logs (
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16 import_id integer NOT NULL,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 time timestamp with time zone NOT NULL,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18 kind log_type NOT NULL default 'info'::log_type,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 msg text NOT NULL
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
20 );
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 CREATE TEMP TABLE agg_tracker (
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23 gid text NOT NULL UNIQUE,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
24 unknown boolean NOT NULL DEFAULT false,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
25 assume_zpg boolean NOT NULL DEFAULT false,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26 ign_meas_codes text ARRAY,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
27 rescale_errors text ARRAY,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28 missing_values text ARRAY,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
29 assume_cm integer NOT NULL DEFAULT 0,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 bad_values integer NOT NULL DEFAULT 0,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31 measurements integer NOT NULL DEFAULT 0,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
32 predictions integer NOT NULL DEFAULT 0
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 );
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34 last_id := -1;
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
35
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
36 FOR line IN logs LOOP
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
37
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38 IF last_id <> line.import_id THEN
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
39 --RAISE NOTICE '%', line.import_id;
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 last_id := line.import_id;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41 -- TODO: Generated log lines from agg_tracker
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42 TRUNCATE agg_tracker;
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
43
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
44 ELSIF line.msg ~ '^Found measurements/predictions for ....................$' THEN
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
45 curr_gid := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
46 line.msg from '^Found measurements/predictions for (....................)$');
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
47
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
48 ELSIF line.msg ~ '^Inserted \d+ measurements for ....................$' THEN
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
49 num := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
50 line.msg from '^Inserted (\d+)')::integer;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
51 gauge := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
52 line.msg from '^Inserted \d+ measurements for (....................)$');
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
53
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
54 INSERT INTO agg_tracker (gid, measurements) VALUES (gauge, num)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
55 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
56 DO UPDATE SET measurements = EXCLUDED.measurements + num;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
57
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
58 ELSIF line.msg ~ '^Ignored \d+ measurements with value -99999$' THEN
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
59 num := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
60 line.msg from '^Ignored (\d+)')::integer;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
61
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
62 INSERT INTO agg_tracker (gid, bad_values) VALUES (curr_gid, num)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
63 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
64 DO UPDATE SET bad_values = EXCLUDED.bad_values + num;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
65
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
66 ELSIF line.msg ~ '^Inserted \d+ predictions for ....................$' THEN
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
67 num := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
68 line.msg from '^Inserted (\d+)')::integer;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
69 gauge := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
70 line.msg from '^Inserted \d+ predictions for (....................)$');
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
71
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
72 INSERT INTO agg_tracker (gid, predictions) VALUES (gauge, num)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
73 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
74 DO UPDATE SET predictions = EXCLUDED.predictions + num;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
75
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
76 ELSIF line.msg ~ '^''Reference_code'' not specified. Assuming ''ZPG''$' THEN
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
77
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
78 INSERT INTO agg_tracker (gid, assume_zpg) VALUES (gauge, true)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
79 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
80 DO UPDATE SET assume_zpg = true;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
81
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
82 ELSIF line.msg ~ '^Cannot find gauge "...................." for import$' THEN
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
83 gauge := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
84 line.msg from '^Cannot find gauge "(....................)" for import$');
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
85
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
86 INSERT INTO agg_tracker (gid, unknown) VALUES (gauge, true)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
87 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
88 DO UPDATE SET unknown = true;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
89
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
90 ELSIF line.msg ~ '^''Unit'' not specified. Assuming ''cm''$' THEN
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
91
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
92 INSERT INTO agg_tracker (gid, assume_cm) VALUES (gauge, 1)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
93 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
94 DO UPDATE SET assume_cm = EXCLUDED.assume_cm + 1;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
95
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
96 ELSIF line.msg ~ '^Importing gauge measurements took ' THEN
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
97
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
98 -- TODO: Flush aggregation because its likely the last entry.
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
99 ELSE
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
100 -- TODO: Handle 'Missing mandatory value at %s. Ignored (bad service)'
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
101 -- TODO: Handle 'Ignored message with measure_code %s'
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
102 -- TODO: Handle "unknown unit '%s'"
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
103 -- Not handled, copy through ..
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
104 INSERT INTO filtered_logs VALUES (line.import_id, line.time, line.kind, line.msg);
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
105
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
106 END IF;
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
107
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
108 last_time := line.time;
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
109 END LOOP;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
110
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
111 -- TODO: Handle remains from agg_tracker
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
112
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
113 -- DELETE FROM import.import_logs WHERE import_id IN (
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
114 -- SELECT id FROM import.imports WHERE kind = 'gm')
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
115
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
116 -- INSERT INTO import.import_logs SELECT * FROM import.filtered_logs;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
117
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
118 DROP TABLE filtered_logs;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
119 DROP TABLE agg_tracker;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
120 END $$;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
121 -- VACUUM FULL;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
122
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
123