Mercurial > gemma

annotate schema/updates/1469/aggregate-gm-logs.sql @ 5540:4d815f295e57 aggregate-gm-import-logging

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
WIP: Removed more TODOs.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 26 Oct 2021 01:15:30 +0200
parents ff95d3603e4d
children 29804c8e817d
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
5540
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
3 logs NO SCROLL CURSOR FOR SELECT
5534
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;
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
11 value text;
5536
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
12 joined text;
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
13 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
14 BEGIN
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16 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
17 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
18 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
19 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
20 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
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
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23 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
24 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
25 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
26 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
27 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
28 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
29 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
30 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
31 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
32 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
33 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
34 );
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
35 last_id := -1;
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
36
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37 FOR line IN logs LOOP
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
38
5540
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
39 -- RAISE NOTICE '%', line.msg;
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
40
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41 IF last_id <> line.import_id THEN
5536
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
42
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
43 -- unknown
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
44 SELECT INTO joined string_agg(gid, ', ')
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
45 FROM agg_tracker
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
46 WHERE unknown;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
47
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
48 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
49 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
50 last_id, last_time, 'warn'::log_type,
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
51 'Cannot find following gauges: ' || joined);
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
52 END IF;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
53
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
54 -- assume zpg
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
55 SELECT INTO joined string_agg(gid, ', ')
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
56 FROM agg_tracker
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
57 WHERE assume_zpg;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
58
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
59 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
60 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
61 last_id, last_time, 'warn'::log_type,
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
62 '''Reference_code'' not specified. Assuming ''ZPG'': ' || joined);
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
63 END IF;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
64
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
65 -- assume cm
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
66 SELECT INTO joined string_agg(
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
67 gid || ' (' || assume_cm || ')', ', ')
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
68 FROM agg_tracker
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
69 WHERE assume_cm > 0;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
70
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
71 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
72 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
73 last_id, last_time, 'warn'::log_type,
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
74 '''Unit'' not specified. Assuming ''cm'': ' || joined);
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
75 END IF;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
76
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
77 -- bad values
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
78 SELECT INTO joined string_agg(
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
79 gid || ' (' || bad_values || ')', ', ')
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
80 FROM agg_tracker
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
81 WHERE bad_values > 0;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
82
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
83 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
84 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
85 last_id, last_time, 'warn'::log_type,
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
86 'Ignored measurements with value -99999: ' || joined);
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
87 END IF;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
88
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
89
5540
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
90 -- missing mandatory values
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
91 SELECT INTO joined string_agg(
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
92 gid || ' ('
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
93 || array_to_string(ARRAY(SELECT DISTINCT unnest(missing_values)), '; ')
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
94 || ')', ', ')
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
95 FROM agg_tracker
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
96 WHERE missing_values IS NOT NULL;
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
97
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
98 IF joined IS NOT NULL THEN
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
99 INSERT INTO filtered_logs VALUES (
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
100 last_id, last_time, 'warn'::log_type,
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
101 'Missing mandatory values: ' || joined);
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
102 END IF;
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
103
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
104 -- convert units
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
105 SELECT INTO joined string_agg(
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
106 gid || ' ('
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
107 || array_to_string(ARRAY(SELECT DISTINCT unnest(rescale_errors)), '; ')
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
108 || ')', ', ')
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
109 FROM agg_tracker
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
110 WHERE rescale_errors IS NOT NULL;
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
111
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
112 IF joined IS NOT NULL THEN
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
113 INSERT INTO filtered_logs VALUES (
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
114 last_id, last_time, 'error'::log_type,
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
115 'Cannot convert units: ' || joined);
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
116 END IF;
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
117
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
118 -- ignored measure codes
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
119 SELECT INTO joined string_agg(
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
120 gid || ' ('
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
121 || array_to_string(ARRAY(SELECT DISTINCT unnest(ign_meas_codes)), '; ')
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
122 || ')', ', ')
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
123 FROM agg_tracker
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
124 WHERE ign_meas_codes IS NOT NULL;
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
125
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
126 IF joined IS NOT NULL THEN
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
127 INSERT INTO filtered_logs VALUES (
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
128 last_id, last_time, 'warn'::log_type,
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
129 'Ignored measure codes: ' || joined);
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
130 END IF;
5536
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
131
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
132 -- predictions
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
133 SELECT INTO joined string_agg(
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
134 gid || ' (' || predictions || ')', ', ')
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
135 FROM agg_tracker
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
136 WHERE predictions > 0;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
137
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
138 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
139 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
140 last_id, last_time, 'info'::log_type,
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
141 'New predictions: ' || joined);
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
142 END IF;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
143
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
144 -- measurements
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
145 SELECT INTO joined string_agg(
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
146 gid || ' (' || measurements || ')', ', ')
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
147 FROM agg_tracker
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
148 WHERE measurements > 0;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
149
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
150 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
151 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
152 last_id, last_time, 'info'::log_type,
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
153 'New measurements: ' || joined);
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
154 END IF;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
155
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
156 -- nothing changed
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
157 SELECT INTO joined string_agg(gid, ', ')
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
158 FROM agg_tracker
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
159 WHERE measurements = 0 and predictions = 0;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
160
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
161 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
162 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
163 last_id, last_time, 'info'::log_type,
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
164 'No changes for: ' || joined);
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
165 END IF;
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
166
dc2000b807bd WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5535
diff changeset
167
5540
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
168 -- reset aggregate table
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
169 TRUNCATE agg_tracker;
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
170 last_id := line.import_id;
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
171
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
172 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
173 curr_gid := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
174 line.msg from '^Found measurements/predictions for (....................)$');
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
175
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
176 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
177 num := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
178 line.msg from '^Inserted (\d+)')::integer;
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
179 value := substring(
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
180 line.msg from '^Inserted \d+ measurements for (....................)$');
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
181
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
182 INSERT INTO agg_tracker (gid, measurements) VALUES (value, num)
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
183 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
184 DO UPDATE SET measurements = EXCLUDED.measurements + num;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
185
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
186 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
187 num := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
188 line.msg from '^Ignored (\d+)')::integer;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
189
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
190 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
191 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
192 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
193
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
194 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
195 num := substring(
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
196 line.msg from '^Inserted (\d+)')::integer;
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
197 value := substring(
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
198 line.msg from '^Inserted \d+ predictions for (....................)$');
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
199
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
200 INSERT INTO agg_tracker (gid, predictions) VALUES (value, num)
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
201 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
202 DO UPDATE SET predictions = EXCLUDED.predictions + num;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
203
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
204 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
205
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
206 INSERT INTO agg_tracker (gid, assume_zpg) VALUES (curr_gid, true)
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
207 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
208 DO UPDATE SET assume_zpg = true;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
209
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
210 ELSIF line.msg ~ '^Cannot find gauge "...................." for import$' THEN
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
211 value := substring(
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
212 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
213
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
214 INSERT INTO agg_tracker (gid, unknown) VALUES (value, true)
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
215 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
216 DO UPDATE SET unknown = true;
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
217
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
218 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
219
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
220 INSERT INTO agg_tracker (gid, assume_cm) VALUES (curr_gid, 1)
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
221 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
222 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
223
5538
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
224 ELSIF line.msg ~ '^Ignored message with measure_code .+' THEN
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
225 value := substring(
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
226 line.msg from '^Ignored message with measure_code (.+)$');
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
227
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
228 INSERT INTO agg_tracker (gid, ign_meas_codes) VALUES (curr_gid, array[value])
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
229 ON CONFLICT (gid)
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
230 DO UPDATE SET ign_meas_codes =
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
231 array_cat(agg_tracker.ign_meas_codes, EXCLUDED.ign_meas_codes);
ff95d3603e4d WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5536
diff changeset
232
5540
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
233 ELSIF line.msg ~ '^Missing mandatory value at [^.]+\.' THEN
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
234 value := substring(
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
235 line.msg from '^Missing mandatory value at ([^.]+)\.');
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
236
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
237 INSERT INTO agg_tracker (gid, missing_values) VALUES (curr_gid, array[value])
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
238 ON CONFLICT (gid)
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
239 DO UPDATE SET missing_values =
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
240 array_cat(agg_tracker.missing_values, EXCLUDED.missing_values);
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
241
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
242 ELSIF line.msg ~ '^unknown unit ''[^'']*''' THEN
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
243
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
244 value := substring(
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
245 line.msg from '^unknown unit ''([^'']*)''');
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
246
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
247 INSERT INTO agg_tracker (gid, rescale_errors) VALUES (curr_gid, array[value])
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
248 ON CONFLICT (gid)
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
249 DO UPDATE SET rescale_errors =
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
250 array_cat(agg_tracker.rescale_errors, EXCLUDED.rescale_errors);
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
251
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
252 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
253
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
254 -- 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
255 ELSE
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
256 -- Not handled, copy through ..
5540
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
257 RAISE NOTICE '%', line.msg;
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
258 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
259
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
260 END IF;
5535
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
261
1fba5766d820 WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5534
diff changeset
262 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
263 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
264
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
265 -- 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
266
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
267 -- 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
268 -- 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
269
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
270 -- 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
271
5540
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
272 SELECT INTO num count(*) FROM filtered_logs;
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
273
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
274 RAISE NOTICE 'number of new gm log message entries: %', num;
4d815f295e57 WIP: Removed more TODOs.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5538
diff changeset
275
5534
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
276 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
277 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
278 END $$;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
279 -- 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
280
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
281