1 |
-- |
---|
2 |
-- PostgreSQL database dump |
---|
3 |
-- |
---|
4 |
|
---|
5 |
SET client_encoding = 'UTF8'; |
---|
6 |
SET standard_conforming_strings = off; |
---|
7 |
SET check_function_bodies = false; |
---|
8 |
SET client_min_messages = warning; |
---|
9 |
SET escape_string_warning = off; |
---|
10 |
|
---|
11 |
-- |
---|
12 |
-- Name: stratcon; Type: SCHEMA; Schema: -; Owner: - |
---|
13 |
-- |
---|
14 |
|
---|
15 |
CREATE SCHEMA stratcon; |
---|
16 |
|
---|
17 |
|
---|
18 |
SET search_path = stratcon, pg_catalog; |
---|
19 |
|
---|
20 |
SET default_tablespace = ''; |
---|
21 |
|
---|
22 |
SET default_with_oids = false; |
---|
23 |
|
---|
24 |
-- |
---|
25 |
-- Name: current_node_config; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
26 |
-- |
---|
27 |
|
---|
28 |
CREATE TABLE current_node_config ( |
---|
29 |
remote_address inet NOT NULL, |
---|
30 |
node_type text NOT NULL, |
---|
31 |
whence timestamp with time zone NOT NULL, |
---|
32 |
config xml NOT NULL |
---|
33 |
); |
---|
34 |
|
---|
35 |
|
---|
36 |
-- |
---|
37 |
-- Name: current_node_config_changelog; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
38 |
-- |
---|
39 |
|
---|
40 |
CREATE TABLE current_node_config_changelog ( |
---|
41 |
remote_address inet NOT NULL, |
---|
42 |
node_type text NOT NULL, |
---|
43 |
whence timestamp with time zone NOT NULL, |
---|
44 |
config xml NOT NULL |
---|
45 |
); |
---|
46 |
|
---|
47 |
|
---|
48 |
-- |
---|
49 |
-- Name: loading_dock_check_s; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
50 |
-- |
---|
51 |
|
---|
52 |
CREATE TABLE loading_dock_check_s ( |
---|
53 |
sid integer NOT NULL, |
---|
54 |
remote_address inet, |
---|
55 |
whence timestamp with time zone NOT NULL, |
---|
56 |
id uuid NOT NULL, |
---|
57 |
target text NOT NULL, |
---|
58 |
module text NOT NULL, |
---|
59 |
name text NOT NULL |
---|
60 |
); |
---|
61 |
|
---|
62 |
|
---|
63 |
-- |
---|
64 |
-- Name: loading_dock_metric_numeric_s; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
65 |
-- |
---|
66 |
|
---|
67 |
CREATE TABLE loading_dock_metric_numeric_s ( |
---|
68 |
sid integer NOT NULL, |
---|
69 |
whence timestamp with time zone NOT NULL, |
---|
70 |
name text NOT NULL, |
---|
71 |
value numeric |
---|
72 |
); |
---|
73 |
|
---|
74 |
|
---|
75 |
-- |
---|
76 |
-- Name: loading_dock_metric_text_s; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
77 |
-- |
---|
78 |
|
---|
79 |
CREATE TABLE loading_dock_metric_text_s ( |
---|
80 |
sid integer NOT NULL, |
---|
81 |
whence timestamp with time zone NOT NULL, |
---|
82 |
name text NOT NULL, |
---|
83 |
value text |
---|
84 |
); |
---|
85 |
|
---|
86 |
|
---|
87 |
-- |
---|
88 |
-- Name: loading_dock_metric_text_s_change_log; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
89 |
-- |
---|
90 |
|
---|
91 |
CREATE TABLE loading_dock_metric_text_s_change_log ( |
---|
92 |
sid integer NOT NULL, |
---|
93 |
whence timestamp with time zone NOT NULL, |
---|
94 |
name text NOT NULL, |
---|
95 |
value text |
---|
96 |
); |
---|
97 |
|
---|
98 |
|
---|
99 |
-- |
---|
100 |
-- Name: loading_dock_status_s; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
101 |
-- |
---|
102 |
|
---|
103 |
CREATE TABLE loading_dock_status_s ( |
---|
104 |
sid integer NOT NULL, |
---|
105 |
whence timestamp with time zone NOT NULL, |
---|
106 |
state character(1) NOT NULL, |
---|
107 |
availability character(1) NOT NULL, |
---|
108 |
duration integer NOT NULL, |
---|
109 |
status text |
---|
110 |
); |
---|
111 |
|
---|
112 |
|
---|
113 |
-- |
---|
114 |
-- Name: loading_dock_status_s_change_log; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
115 |
-- |
---|
116 |
|
---|
117 |
CREATE TABLE loading_dock_status_s_change_log ( |
---|
118 |
sid integer NOT NULL, |
---|
119 |
whence timestamp with time zone NOT NULL, |
---|
120 |
state character(1) NOT NULL, |
---|
121 |
availability character(1) NOT NULL, |
---|
122 |
duration integer NOT NULL, |
---|
123 |
status text |
---|
124 |
); |
---|
125 |
|
---|
126 |
|
---|
127 |
-- |
---|
128 |
-- Name: log_whence_s; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
129 |
-- |
---|
130 |
|
---|
131 |
CREATE TABLE log_whence_s ( |
---|
132 |
whence timestamp with time zone NOT NULL, |
---|
133 |
"interval" character varying(20) NOT NULL |
---|
134 |
); |
---|
135 |
|
---|
136 |
|
---|
137 |
-- |
---|
138 |
-- Name: map_uuid_to_sid; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
139 |
-- |
---|
140 |
|
---|
141 |
CREATE TABLE map_uuid_to_sid ( |
---|
142 |
id uuid NOT NULL, |
---|
143 |
sid integer NOT NULL |
---|
144 |
); |
---|
145 |
|
---|
146 |
|
---|
147 |
-- |
---|
148 |
-- Name: metric_name_summary; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
149 |
-- |
---|
150 |
|
---|
151 |
CREATE TABLE metric_name_summary ( |
---|
152 |
sid integer NOT NULL, |
---|
153 |
metric_name text NOT NULL, |
---|
154 |
metric_type character varying(22), |
---|
155 |
active boolean DEFAULT true |
---|
156 |
); |
---|
157 |
|
---|
158 |
|
---|
159 |
-- |
---|
160 |
-- Name: mv_loading_dock_check_s; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
161 |
-- |
---|
162 |
|
---|
163 |
CREATE TABLE mv_loading_dock_check_s ( |
---|
164 |
sid integer NOT NULL, |
---|
165 |
remote_address inet, |
---|
166 |
whence timestamp with time zone NOT NULL, |
---|
167 |
id uuid NOT NULL, |
---|
168 |
target text NOT NULL, |
---|
169 |
module text NOT NULL, |
---|
170 |
name text NOT NULL |
---|
171 |
); |
---|
172 |
|
---|
173 |
|
---|
174 |
-- |
---|
175 |
-- Name: rollup_matrix_numeric_12hours; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
176 |
-- |
---|
177 |
|
---|
178 |
CREATE TABLE rollup_matrix_numeric_12hours ( |
---|
179 |
sid integer NOT NULL, |
---|
180 |
name text NOT NULL, |
---|
181 |
rollup_time timestamp with time zone NOT NULL, |
---|
182 |
count_rows integer, |
---|
183 |
avg_value numeric |
---|
184 |
); |
---|
185 |
|
---|
186 |
|
---|
187 |
-- |
---|
188 |
-- Name: rollup_matrix_numeric_20m; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
189 |
-- |
---|
190 |
|
---|
191 |
CREATE TABLE rollup_matrix_numeric_20m ( |
---|
192 |
sid integer NOT NULL, |
---|
193 |
name text NOT NULL, |
---|
194 |
rollup_time timestamp with time zone NOT NULL, |
---|
195 |
count_rows integer, |
---|
196 |
avg_value numeric |
---|
197 |
); |
---|
198 |
|
---|
199 |
|
---|
200 |
-- |
---|
201 |
-- Name: rollup_matrix_numeric_5m; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
202 |
-- |
---|
203 |
|
---|
204 |
CREATE TABLE rollup_matrix_numeric_5m ( |
---|
205 |
sid integer NOT NULL, |
---|
206 |
name text NOT NULL, |
---|
207 |
rollup_time timestamp with time zone NOT NULL, |
---|
208 |
count_rows integer, |
---|
209 |
avg_value numeric |
---|
210 |
); |
---|
211 |
|
---|
212 |
|
---|
213 |
-- |
---|
214 |
-- Name: rollup_matrix_numeric_60m; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
215 |
-- |
---|
216 |
|
---|
217 |
CREATE TABLE rollup_matrix_numeric_60m ( |
---|
218 |
sid integer NOT NULL, |
---|
219 |
name text NOT NULL, |
---|
220 |
rollup_time timestamp with time zone NOT NULL, |
---|
221 |
count_rows integer, |
---|
222 |
avg_value numeric |
---|
223 |
); |
---|
224 |
|
---|
225 |
|
---|
226 |
-- |
---|
227 |
-- Name: rollup_matrix_numeric_6hours; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
228 |
-- |
---|
229 |
|
---|
230 |
CREATE TABLE rollup_matrix_numeric_6hours ( |
---|
231 |
sid integer NOT NULL, |
---|
232 |
name text NOT NULL, |
---|
233 |
rollup_time timestamp with time zone NOT NULL, |
---|
234 |
count_rows integer, |
---|
235 |
avg_value numeric |
---|
236 |
); |
---|
237 |
|
---|
238 |
|
---|
239 |
-- |
---|
240 |
-- Name: rollup_runner; Type: TABLE; Schema: stratcon; Owner: -; Tablespace: |
---|
241 |
-- |
---|
242 |
|
---|
243 |
CREATE TABLE rollup_runner ( |
---|
244 |
rollup_table character varying(100), |
---|
245 |
runner character varying(22) |
---|
246 |
); |
---|
247 |
|
---|
248 |
|
---|
249 |
-- |
---|
250 |
-- Name: choose_window(timestamp without time zone, timestamp without time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
251 |
-- |
---|
252 |
|
---|
253 |
CREATE FUNCTION choose_window(in_start_time timestamp without time zone, in_end_time timestamp without time zone, in_hopeful_nperiods integer, OUT tablename text, OUT period interval, OUT nperiods integer) RETURNS SETOF record |
---|
254 |
AS $$ |
---|
255 |
declare |
---|
256 |
window record; |
---|
257 |
begin |
---|
258 |
-- Figure out which table we should be looking in |
---|
259 |
for window in |
---|
260 |
select atablename, aperiod, anperiods |
---|
261 |
from (select aperiod, iv/isec as anperiods, atablename, |
---|
262 |
abs(case when iv/isec - in_hopeful_nperiods < 0 |
---|
263 |
then 10 * (in_hopeful_nperiods - iv/isec) |
---|
264 |
else iv/isec - in_hopeful_nperiods |
---|
265 |
end) as badness |
---|
266 |
from (select extract('epoch' from in_end_time) - |
---|
267 |
extract('epoch' from in_start_time) as iv |
---|
268 |
) i, |
---|
269 |
( select 5*60 as isec, '5 minutes'::interval as aperiod, |
---|
270 |
'rollup_matrix_numeric_5m' as atablename |
---|
271 |
union all |
---|
272 |
select 20*60 as isec, '20 minutes'::interval as aperiod, |
---|
273 |
'rollup_matrix_numeric_20m' as atablename |
---|
274 |
union all |
---|
275 |
select 60*60 as isec, '1 hour'::interval as aperiod, |
---|
276 |
'rollup_matrix_numeric_60m' as atablename |
---|
277 |
union all |
---|
278 |
select 6*60*60 as isec, '6 hours'::interval as aaperiod, |
---|
279 |
'rollup_matrix_numeric_6hours' as atablename |
---|
280 |
union all |
---|
281 |
select 12*60*60 as isec, '12 hours'::interval as aperiod, |
---|
282 |
'rollup_matrix_numeric_12hours' as atablename |
---|
283 |
) ivs |
---|
284 |
) b |
---|
285 |
order by badness asc |
---|
286 |
limit 1 |
---|
287 |
loop |
---|
288 |
tablename := window.atablename; |
---|
289 |
period := window.aperiod; |
---|
290 |
nperiods := window.anperiods; |
---|
291 |
return next; |
---|
292 |
end loop; |
---|
293 |
return; |
---|
294 |
end |
---|
295 |
$$ |
---|
296 |
LANGUAGE plpgsql; |
---|
297 |
|
---|
298 |
|
---|
299 |
-- |
---|
300 |
-- Name: choose_window(timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
301 |
-- |
---|
302 |
|
---|
303 |
CREATE FUNCTION choose_window(in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, OUT tablename text, OUT period interval, OUT nperiods integer) RETURNS SETOF record |
---|
304 |
AS $$ |
---|
305 |
declare |
---|
306 |
window record; |
---|
307 |
begin |
---|
308 |
-- Figure out which table we should be looking in |
---|
309 |
for window in |
---|
310 |
select atablename, aperiod, anperiods |
---|
311 |
from (select aperiod, iv/isec as anperiods, atablename, |
---|
312 |
abs(case when iv/isec - in_hopeful_nperiods < 0 |
---|
313 |
then 10 * (in_hopeful_nperiods - iv/isec) |
---|
314 |
else iv/isec - in_hopeful_nperiods |
---|
315 |
end) as badness |
---|
316 |
from (select extract('epoch' from in_end_time) - |
---|
317 |
extract('epoch' from in_start_time) as iv |
---|
318 |
) i, |
---|
319 |
( select 5*60 as isec, '5 minutes'::interval as aperiod, |
---|
320 |
'rollup_matrix_numeric_5m' as atablename |
---|
321 |
union all |
---|
322 |
select 20*60 as isec, '20 minutes'::interval as aperiod, |
---|
323 |
'rollup_matrix_numeric_20m' as atablename |
---|
324 |
union all |
---|
325 |
select 60*60 as isec, '1 hour'::interval as aperiod, |
---|
326 |
'rollup_matrix_numeric_60m' as atablename |
---|
327 |
union all |
---|
328 |
select 6*60*60 as isec, '6 hours'::interval as aaperiod, |
---|
329 |
'rollup_matrix_numeric_6hours' as atablename |
---|
330 |
union all |
---|
331 |
select 12*60*60 as isec, '12 hours'::interval as aperiod, |
---|
332 |
'rollup_matrix_numeric_12hours' as atablename |
---|
333 |
) ivs |
---|
334 |
) b |
---|
335 |
order by badness asc |
---|
336 |
limit 1 |
---|
337 |
loop |
---|
338 |
tablename := window.atablename; |
---|
339 |
period := window.aperiod; |
---|
340 |
nperiods := window.anperiods; |
---|
341 |
return next; |
---|
342 |
end loop; |
---|
343 |
return; |
---|
344 |
end |
---|
345 |
$$ |
---|
346 |
LANGUAGE plpgsql; |
---|
347 |
|
---|
348 |
|
---|
349 |
-- |
---|
350 |
-- Name: date_hour(timestamp with time zone); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
351 |
-- |
---|
352 |
|
---|
353 |
CREATE FUNCTION date_hour(timestamp with time zone) RETURNS timestamp with time zone |
---|
354 |
AS $_$ |
---|
355 |
SELECT date_trunc('hour',$1); |
---|
356 |
$_$ |
---|
357 |
LANGUAGE sql IMMUTABLE STRICT; |
---|
358 |
|
---|
359 |
|
---|
360 |
-- |
---|
361 |
-- Name: fetch_dataset(uuid, text, timestamp with time zone, timestamp with time zone, integer, boolean); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
362 |
-- |
---|
363 |
|
---|
364 |
CREATE FUNCTION fetch_dataset(in_uuid uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean) RETURNS SETOF rollup_matrix_numeric_5m |
---|
365 |
AS $$ |
---|
366 |
declare |
---|
367 |
v_sid int; |
---|
368 |
begin |
---|
369 |
select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check; |
---|
370 |
if not found then |
---|
371 |
return; |
---|
372 |
end if; |
---|
373 |
|
---|
374 |
return query select * from stratcon.fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive); |
---|
375 |
end |
---|
376 |
$$ |
---|
377 |
LANGUAGE plpgsql; |
---|
378 |
|
---|
379 |
|
---|
380 |
-- |
---|
381 |
-- Name: fetch_dataset(integer, text, timestamp with time zone, timestamp with time zone, integer, boolean); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
382 |
-- |
---|
383 |
|
---|
384 |
CREATE FUNCTION fetch_dataset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean) RETURNS SETOF rollup_matrix_numeric_5m |
---|
385 |
AS $$ |
---|
386 |
declare |
---|
387 |
v_sql text; |
---|
388 |
v_sid int; |
---|
389 |
v_target record; |
---|
390 |
v_interval numeric; |
---|
391 |
v_start_adj timestamptz; |
---|
392 |
v_end_adj timestamptz; |
---|
393 |
v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
394 |
v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
395 |
v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
396 |
begin |
---|
397 |
|
---|
398 |
-- Map out uuid to an sid. |
---|
399 |
v_sid := in_sid; |
---|
400 |
|
---|
401 |
select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods); |
---|
402 |
|
---|
403 |
if not found then |
---|
404 |
raise exception 'no target table'; |
---|
405 |
return; |
---|
406 |
end if; |
---|
407 |
|
---|
408 |
select 'epoch'::timestamp + |
---|
409 |
((floor(extract('epoch' from in_start_time) / |
---|
410 |
extract('epoch' from v_target.period)) * |
---|
411 |
extract('epoch' from v_target.period)) || ' seconds') ::interval |
---|
412 |
into v_start_adj; |
---|
413 |
|
---|
414 |
select 'epoch'::timestamp + |
---|
415 |
((floor(extract('epoch' from in_end_time) / |
---|
416 |
extract('epoch' from v_target.period)) * |
---|
417 |
extract('epoch' from v_target.period)) || ' seconds') ::interval |
---|
418 |
into v_end_adj; |
---|
419 |
|
---|
420 |
v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' || |
---|
421 |
's.rollup_time, d.count_rows, d.avg_value ' || |
---|
422 |
' from ' || |
---|
423 |
'(select ' || quote_literal(v_start_adj) || '::timestamp' || |
---|
424 |
' + t * ' || quote_literal(v_target.period) || '::interval' || |
---|
425 |
' as rollup_time' || |
---|
426 |
' from generate_series(1,' || v_target.nperiods || ') t) s ' || |
---|
427 |
'left join ' || |
---|
428 |
'(select * from stratcon.' || v_target.tablename || |
---|
429 |
' where sid = ' || v_sid || |
---|
430 |
' and name = ' || quote_literal(in_name) || |
---|
431 |
' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' || |
---|
432 |
' and ' || quote_literal(v_end_adj) || '::timestamp) d' || |
---|
433 |
' using(rollup_time)'; |
---|
434 |
|
---|
435 |
for v_rollup_row in execute v_sql loop |
---|
436 |
if derive is true then |
---|
437 |
v_r_rollup_row := v_rollup_row; |
---|
438 |
if v_l_rollup_row.count_rows is not null and |
---|
439 |
v_rollup_row.count_rows is not null then |
---|
440 |
v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time); |
---|
441 |
v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2; |
---|
442 |
v_r_rollup_row.avg_value := |
---|
443 |
(v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval; |
---|
444 |
else |
---|
445 |
v_r_rollup_row.count_rows = NULL; |
---|
446 |
v_r_rollup_row.avg_value = NULL; |
---|
447 |
|
---|
448 |
end if; |
---|
449 |
else |
---|
450 |
v_r_rollup_row := v_rollup_row; |
---|
451 |
end if; |
---|
452 |
return next v_r_rollup_row; |
---|
453 |
v_l_rollup_row := v_rollup_row; |
---|
454 |
end loop; |
---|
455 |
return; |
---|
456 |
end |
---|
457 |
$$ |
---|
458 |
LANGUAGE plpgsql; |
---|
459 |
|
---|
460 |
|
---|
461 |
-- |
---|
462 |
-- Name: fetch_varset(uuid, text, timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
463 |
-- |
---|
464 |
|
---|
465 |
CREATE FUNCTION fetch_varset(in_check uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer) RETURNS SETOF loading_dock_metric_text_s_change_log |
---|
466 |
AS $$ |
---|
467 |
declare |
---|
468 |
v_sid int; |
---|
469 |
begin |
---|
470 |
-- Map out uuid to an sid. |
---|
471 |
select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check; |
---|
472 |
if not found then |
---|
473 |
return; |
---|
474 |
end if; |
---|
475 |
|
---|
476 |
return query select * from stratcon.fetch_varset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods); |
---|
477 |
end |
---|
478 |
$$ |
---|
479 |
LANGUAGE plpgsql; |
---|
480 |
|
---|
481 |
|
---|
482 |
-- |
---|
483 |
-- Name: fetch_varset(integer, text, timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
484 |
-- |
---|
485 |
|
---|
486 |
CREATE FUNCTION fetch_varset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer) RETURNS SETOF loading_dock_metric_text_s_change_log |
---|
487 |
AS $$ |
---|
488 |
declare |
---|
489 |
v_sid int; |
---|
490 |
v_target record; |
---|
491 |
v_start_adj timestamptz; |
---|
492 |
v_start_text text; |
---|
493 |
v_next_text text; |
---|
494 |
v_end_adj timestamptz; |
---|
495 |
v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype; |
---|
496 |
begin |
---|
497 |
-- Map out uuid to an sid. |
---|
498 |
v_sid := in_sid; |
---|
499 |
|
---|
500 |
select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods); |
---|
501 |
|
---|
502 |
select 'epoch'::timestamp + |
---|
503 |
((floor(extract('epoch' from in_start_time) / |
---|
504 |
extract('epoch' from v_target.period)) * |
---|
505 |
extract('epoch' from v_target.period)) || ' seconds') ::interval |
---|
506 |
into v_start_adj; |
---|
507 |
|
---|
508 |
select 'epoch'::timestamp + |
---|
509 |
((floor(extract('epoch' from in_end_time) / |
---|
510 |
extract('epoch' from v_target.period)) * |
---|
511 |
extract('epoch' from v_target.period)) || ' seconds') ::interval |
---|
512 |
into v_end_adj; |
---|
513 |
|
---|
514 |
for v_change_row in |
---|
515 |
select sid, 'epoch'::timestamp + |
---|
516 |
((floor(extract('epoch' from whence) / |
---|
517 |
extract('epoch' from v_target.period)) * |
---|
518 |
extract('epoch' from v_target.period)) || ' seconds') ::interval as whence, |
---|
519 |
name, value |
---|
520 |
from stratcon.loading_dock_metric_text_s_change_log |
---|
521 |
where sid = v_sid |
---|
522 |
and name = in_name |
---|
523 |
and whence <= v_start_adj |
---|
524 |
order by 'epoch'::timestamp + |
---|
525 |
((floor(extract('epoch' from whence) / |
---|
526 |
extract('epoch' from v_target.period)) * |
---|
527 |
extract('epoch' from v_target.period)) || ' seconds') ::interval desc |
---|
528 |
limit 1 |
---|
529 |
loop |
---|
530 |
v_start_text := coalesce(v_change_row.value, '[unset]'); |
---|
531 |
end loop; |
---|
532 |
|
---|
533 |
for v_change_row in |
---|
534 |
select v_sid as sid, whence, in_name as name, value from |
---|
535 |
-- (select v_start_adj::timestamp + t * v_target.period::interval as whence |
---|
536 |
-- from generate_series(1, v_target.nperiods) t) s |
---|
537 |
-- left join |
---|
538 |
(select 'epoch'::timestamp + |
---|
539 |
((floor(extract('epoch' from whence) / |
---|
540 |
extract('epoch' from v_target.period)) * |
---|
541 |
extract('epoch' from v_target.period)) || ' seconds') ::interval as whence, |
---|
542 |
coalesce(value, '[unset]') as value |
---|
543 |
from stratcon.loading_dock_metric_text_s_change_log |
---|
544 |
where sid = v_sid |
---|
545 |
and name = in_name |
---|
546 |
and whence > v_start_adj |
---|
547 |
and whence <= v_end_adj) d |
---|
548 |
-- using (whence) |
---|
549 |
order by whence asc |
---|
550 |
loop |
---|
551 |
v_next_text := v_change_row.value; |
---|
552 |
if v_change_row.value is not null and |
---|
553 |
v_start_text != v_change_row.value then |
---|
554 |
v_change_row.value := coalesce(v_start_text, '[unset]') || ' -> ' || coalesce(v_change_row.value, '[unset]'); |
---|
555 |
else |
---|
556 |
v_change_row.value := v_start_text; |
---|
557 |
end if; |
---|
558 |
if v_next_text is not null then |
---|
559 |
v_start_text := v_next_text; |
---|
560 |
end if; |
---|
561 |
return next v_change_row; |
---|
562 |
end loop; |
---|
563 |
|
---|
564 |
return; |
---|
565 |
end |
---|
566 |
$$ |
---|
567 |
LANGUAGE plpgsql; |
---|
568 |
|
---|
569 |
|
---|
570 |
-- |
---|
571 |
-- Name: generate_sid_from_id(uuid); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
572 |
-- |
---|
573 |
|
---|
574 |
CREATE FUNCTION generate_sid_from_id(v_in_id uuid) RETURNS integer |
---|
575 |
AS $$ |
---|
576 |
DECLARE |
---|
577 |
v_ex_sid integer; |
---|
578 |
v_new_sid integer; |
---|
579 |
|
---|
580 |
BEGIN |
---|
581 |
|
---|
582 |
SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id |
---|
583 |
INTO v_ex_sid; |
---|
584 |
|
---|
585 |
IF NOT FOUND THEN |
---|
586 |
SELECT nextval('stratcon.seq_sid') |
---|
587 |
INTO v_new_sid; |
---|
588 |
|
---|
589 |
INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid); |
---|
590 |
|
---|
591 |
|
---|
592 |
RETURN v_new_sid; |
---|
593 |
ELSE |
---|
594 |
RETURN v_ex_sid; |
---|
595 |
END IF; |
---|
596 |
|
---|
597 |
END |
---|
598 |
$$ |
---|
599 |
LANGUAGE plpgsql; |
---|
600 |
|
---|
601 |
|
---|
602 |
-- |
---|
603 |
-- Name: loading_dock_metric_numeric_s_whence_log(); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
604 |
-- |
---|
605 |
|
---|
606 |
CREATE FUNCTION loading_dock_metric_numeric_s_whence_log() RETURNS trigger |
---|
607 |
AS $$ |
---|
608 |
DECLARE |
---|
609 |
v_whence timestamptz; |
---|
610 |
v_whence_5 timestamptz; |
---|
611 |
v_sid integer; |
---|
612 |
v_name text; |
---|
613 |
BEGIN |
---|
614 |
IF TG_OP = 'INSERT' THEN |
---|
615 |
|
---|
616 |
v_whence_5:=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval; |
---|
617 |
|
---|
618 |
SELECT whence FROM stratcon.log_whence_s WHERE whence=v_whence_5 and interval='5 minutes' |
---|
619 |
INTO v_whence; |
---|
620 |
|
---|
621 |
IF NOT FOUND THEN |
---|
622 |
BEGIN |
---|
623 |
INSERT INTO stratcon.log_whence_s VALUES(v_whence_5,'5 minutes'); |
---|
624 |
EXCEPTION |
---|
625 |
WHEN UNIQUE_VIOLATION THEN |
---|
626 |
-- do nothing |
---|
627 |
END; |
---|
628 |
END IF; |
---|
629 |
|
---|
630 |
SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid and metric_name=NEW.name |
---|
631 |
INTO v_sid,v_name; |
---|
632 |
IF NOT FOUND THEN |
---|
633 |
INSERT INTO stratcon.metric_name_summary VALUES(NEW.sid,NEW.name,'numeric'); |
---|
634 |
END IF; |
---|
635 |
|
---|
636 |
END IF; |
---|
637 |
RETURN NULL; |
---|
638 |
END |
---|
639 |
$$ |
---|
640 |
LANGUAGE plpgsql; |
---|
641 |
|
---|
642 |
|
---|
643 |
-- |
---|
644 |
-- Name: loading_dock_metric_text_s_change_log(); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
645 |
-- |
---|
646 |
|
---|
647 |
CREATE FUNCTION loading_dock_metric_text_s_change_log() RETURNS trigger |
---|
648 |
AS $$ |
---|
649 |
DECLARE |
---|
650 |
v_oldvalue text; |
---|
651 |
v_sid integer; |
---|
652 |
v_name text; |
---|
653 |
v_value text; |
---|
654 |
v_whence timestamptz; |
---|
655 |
v_old_whence timestamptz; |
---|
656 |
v_old_name text; |
---|
657 |
v_old_sid integer; |
---|
658 |
v_old_value text; |
---|
659 |
v_max_whence timestamptz; |
---|
660 |
BEGIN |
---|
661 |
|
---|
662 |
IF TG_OP = 'INSERT' THEN |
---|
663 |
|
---|
664 |
SELECT value FROM stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name |
---|
665 |
AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log |
---|
666 |
WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name ) |
---|
667 |
INTO v_oldvalue; |
---|
668 |
|
---|
669 |
IF v_oldvalue IS DISTINCT FROM NEW.value THEN |
---|
670 |
|
---|
671 |
INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value) |
---|
672 |
VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value); |
---|
673 |
END IF; |
---|
674 |
|
---|
675 |
SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid and metric_name=NEW.name |
---|
676 |
INTO v_sid,v_name; |
---|
677 |
IF NOT FOUND THEN |
---|
678 |
INSERT INTO stratcon.metric_name_summary(sid,metric_name,metric_type) VALUES(NEW.sid,NEW.name,'text'); |
---|
679 |
END IF; |
---|
680 |
|
---|
681 |
ELSE |
---|
682 |
RAISE EXCEPTION 'something wrong with stratcon.loading_dock_metric_text_s_change_log '; |
---|
683 |
END IF; |
---|
684 |
|
---|
685 |
RETURN NULL; |
---|
686 |
|
---|
687 |
END |
---|
688 |
$$ |
---|
689 |
LANGUAGE plpgsql; |
---|
690 |
|
---|
691 |
|
---|
692 |
-- |
---|
693 |
-- Name: loading_dock_status_s_change_log(); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
694 |
-- |
---|
695 |
|
---|
696 |
CREATE FUNCTION loading_dock_status_s_change_log() RETURNS trigger |
---|
697 |
AS $$ |
---|
698 |
DECLARE |
---|
699 |
v_state CHAR(1); |
---|
700 |
v_avail CHAR(1); |
---|
701 |
BEGIN |
---|
702 |
|
---|
703 |
IF TG_OP = 'INSERT' THEN |
---|
704 |
SELECT state,availability FROM stratcon.loading_dock_status_s WHERE sid = NEW.sid |
---|
705 |
AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_status_s_change_log |
---|
706 |
WHERE SID=NEW.sid and WHENCE <> NEW.whence ) |
---|
707 |
INTO v_state,v_avail; |
---|
708 |
|
---|
709 |
IF v_state IS DISTINCT FROM NEW.state OR v_avail IS DISTINCT FROM NEW.availability THEN |
---|
710 |
|
---|
711 |
INSERT INTO stratcon.loading_dock_status_s_change_log (sid,whence,state,availability,duration,status) |
---|
712 |
VALUES (NEW.sid,NEW.whence,NEW.state,NEW.availability,NEW.duration,NEW.status); |
---|
713 |
|
---|
714 |
END IF; |
---|
715 |
|
---|
716 |
ELSE |
---|
717 |
RAISE EXCEPTION 'Something wrong with stratcon.loading_dock_status_s_change_log'; |
---|
718 |
END IF; |
---|
719 |
|
---|
720 |
RETURN NULL; |
---|
721 |
|
---|
722 |
END |
---|
723 |
$$ |
---|
724 |
LANGUAGE plpgsql; |
---|
725 |
|
---|
726 |
|
---|
727 |
-- |
---|
728 |
-- Name: mv_loading_dock_check_s(); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
729 |
-- |
---|
730 |
|
---|
731 |
CREATE FUNCTION mv_loading_dock_check_s() RETURNS trigger |
---|
732 |
AS $$ |
---|
733 |
DECLARE |
---|
734 |
v_remote_address INET; |
---|
735 |
v_target TEXT; |
---|
736 |
v_module TEXT; |
---|
737 |
v_name TEXT; |
---|
738 |
BEGIN |
---|
739 |
|
---|
740 |
IF TG_OP = 'INSERT' THEN |
---|
741 |
SELECT remote_address,target,module,name FROM stratcon.mv_loading_dock_check_s WHERE sid = NEW.sid AND id=NEW.id |
---|
742 |
INTO v_remote_address,v_target,v_module,v_name; |
---|
743 |
|
---|
744 |
IF v_remote_address IS DISTINCT FROM NEW.remote_address OR v_target IS DISTINCT FROM NEW.target OR v_name IS DISTINCT FROM NEW.name THEN |
---|
745 |
|
---|
746 |
DELETE from stratcon.mv_loading_dock_check_s WHERE sid = NEW.sid AND id=NEW.id; |
---|
747 |
|
---|
748 |
INSERT INTO stratcon.mv_loading_dock_check_s (sid,remote_address,whence,id,target,module,name) |
---|
749 |
VALUES (NEW.sid,NEW.remote_address,NEW.whence,NEW.id,NEW.target,NEW.module,NEW.name); |
---|
750 |
|
---|
751 |
END IF; |
---|
752 |
|
---|
753 |
ELSE |
---|
754 |
RAISE EXCEPTION 'Something wrong with stratcon.mv_loading_dock_check_s'; |
---|
755 |
END IF; |
---|
756 |
|
---|
757 |
RETURN NULL; |
---|
758 |
|
---|
759 |
END |
---|
760 |
$$ |
---|
761 |
LANGUAGE plpgsql; |
---|
762 |
|
---|
763 |
|
---|
764 |
-- |
---|
765 |
-- Name: remove_metric(uuid, text, text); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
766 |
-- |
---|
767 |
|
---|
768 |
CREATE FUNCTION remove_metric(in_uuid uuid, in_metric_name text, v_debug text, OUT v_out text) RETURNS text |
---|
769 |
AS $$ |
---|
770 |
DECLARE |
---|
771 |
v_del_sid INT; |
---|
772 |
v_del_metric_name TEXT; |
---|
773 |
v_del_metric_type TEXT; |
---|
774 |
deleted_t INT; |
---|
775 |
deleted_tc INT; |
---|
776 |
deleted_n INT; |
---|
777 |
deleted_5 INT; |
---|
778 |
deleted_20 INT; |
---|
779 |
deleted_60 INT; |
---|
780 |
deleted_6h INT; |
---|
781 |
deleted_12h INT; |
---|
782 |
deleted_sum INT; |
---|
783 |
|
---|
784 |
BEGIN |
---|
785 |
SELECT s.sid,m.metric_name,m.metric_type |
---|
786 |
FROM |
---|
787 |
stratcon.map_uuid_to_sid s, |
---|
788 |
stratcon.metric_name_summary m |
---|
789 |
WHERE s.id=in_uuid |
---|
790 |
and s.sid=m.sid |
---|
791 |
and m.metric_name=in_metric_name |
---|
792 |
INTO v_del_sid,v_del_metric_name,v_del_metric_type; |
---|
793 |
IF NOT FOUND THEN |
---|
794 |
IF v_debug = 'DEBUG' THEN |
---|
795 |
RAISE NOTICE 'Given UUID can not map to SID,Metric Name: %,%',in_uuid,in_metric_name; |
---|
796 |
END IF; |
---|
797 |
v_out:='Please Supply Valid UUID,Metric Name Combination :'||in_uuid||','||in_metric_name; |
---|
798 |
RETURN; |
---|
799 |
END IF; |
---|
800 |
IF v_debug = 'DEBUG' THEN |
---|
801 |
RAISE NOTICE 'Delete In Progress For: %,%,%',v_del_sid,v_del_metric_name,v_del_metric_type; |
---|
802 |
END IF; |
---|
803 |
|
---|
804 |
-- Check of Text or Numeric Type |
---|
805 |
IF v_del_metric_type ='text' THEN |
---|
806 |
-- Delete from Metrix Tex table |
---|
807 |
DELETE FROM stratcon.loading_dock_metric_text_s WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
808 |
GET DIAGNOSTICS deleted_t = ROW_COUNT; |
---|
809 |
IF v_debug = 'DEBUG' THEN |
---|
810 |
RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s : %',deleted; |
---|
811 |
END IF; |
---|
812 |
-- Delete from Metrix Change Log table |
---|
813 |
DELETE FROM stratcon.loading_dock_metric_text_s_change_log WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
814 |
GET DIAGNOSTICS deleted_tc = ROW_COUNT; |
---|
815 |
IF v_debug = 'DEBUG' THEN |
---|
816 |
RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s_change_log : %',deleted; |
---|
817 |
END IF; |
---|
818 |
ELSE |
---|
819 |
-- Delete from Metrix Numeric table |
---|
820 |
DELETE FROM stratcon.loading_dock_metric_numeric_s WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
821 |
GET DIAGNOSTICS deleted_n = ROW_COUNT; |
---|
822 |
IF v_debug = 'DEBUG' THEN |
---|
823 |
RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_numeric_s : %',deleted; |
---|
824 |
END IF; |
---|
825 |
-- Delete from Rollup tables |
---|
826 |
DELETE FROM stratcon.rollup_matrix_numeric_5m WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
827 |
GET DIAGNOSTICS deleted_5 = ROW_COUNT; |
---|
828 |
IF v_debug = 'DEBUG' THEN |
---|
829 |
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_5m : %',deleted; |
---|
830 |
END IF; |
---|
831 |
DELETE FROM stratcon.rollup_matrix_numeric_20m WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
832 |
GET DIAGNOSTICS deleted_20= ROW_COUNT; |
---|
833 |
IF v_debug = 'DEBUG' THEN |
---|
834 |
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_20m : %',deleted; |
---|
835 |
END IF; |
---|
836 |
DELETE FROM stratcon.rollup_matrix_numeric_60m WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
837 |
GET DIAGNOSTICS deleted_60 = ROW_COUNT; |
---|
838 |
IF v_debug = 'DEBUG' THEN |
---|
839 |
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_60m : %',deleted; |
---|
840 |
END IF; |
---|
841 |
DELETE FROM stratcon.rollup_matrix_numeric_6hours WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
842 |
GET DIAGNOSTICS deleted_6h = ROW_COUNT; |
---|
843 |
IF v_debug = 'DEBUG' THEN |
---|
844 |
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_6hours : %',deleted; |
---|
845 |
END IF; |
---|
846 |
DELETE FROM stratcon.rollup_matrix_numeric_12hours WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
847 |
GET DIAGNOSTICS deleted_12h = ROW_COUNT; |
---|
848 |
IF v_debug = 'DEBUG' THEN |
---|
849 |
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_12hours : %',deleted; |
---|
850 |
END IF; |
---|
851 |
END IF; |
---|
852 |
-- Delete from metrix summary table |
---|
853 |
DELETE FROM stratcon.metrix_name_summary WHERE sid=v_del_sid AND metric_name=v_del_metric_name; |
---|
854 |
GET DIAGNOSTICS deleted_sum= ROW_COUNT; |
---|
855 |
IF v_debug = 'DEBUG' THEN |
---|
856 |
RAISE NOTICE 'DELELTED ROWS FROM metric_name_summary : %',deleted; |
---|
857 |
END IF; |
---|
858 |
v_out:='Deleted Rows for Metric_Text, Metrix_Text_change_log,Metric_Numeric,Rollup_5m,Rollup_20m,Rollup_1hour,Rollup_6hours,Rollup_12hours,Metric_Summary:'||deleted_t||','||deleted_tc||','||deleted_n||','||deleted_5||','||deleted_20||','||deleted_60||','||deleted_6h||','||deleted_12h||','||deleted_sum; |
---|
859 |
RETURN; |
---|
860 |
EXCEPTION |
---|
861 |
WHEN RAISE_EXCEPTION THEN |
---|
862 |
RAISE EXCEPTION '%', SQLERRM; |
---|
863 |
WHEN OTHERS THEN |
---|
864 |
RAISE NOTICE '%', SQLERRM; |
---|
865 |
END |
---|
866 |
$$ |
---|
867 |
LANGUAGE plpgsql; |
---|
868 |
|
---|
869 |
|
---|
870 |
-- |
---|
871 |
-- Name: rollup_matrix_numeric_12hours(); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
872 |
-- |
---|
873 |
|
---|
874 |
CREATE FUNCTION rollup_matrix_numeric_12hours() RETURNS void |
---|
875 |
AS $$ |
---|
876 |
DECLARE |
---|
877 |
rec stratcon.rollup_matrix_numeric_12hours%rowtype; |
---|
878 |
v_sql TEXT; |
---|
879 |
v_min_whence TIMESTAMPTZ; |
---|
880 |
v_max_rollup_12 TIMESTAMPTZ; |
---|
881 |
v_whence TIMESTAMPTZ; |
---|
882 |
v_nrunning INT; |
---|
883 |
v_self VARCHAR(22); |
---|
884 |
whenceint RECORD; |
---|
885 |
|
---|
886 |
BEGIN |
---|
887 |
|
---|
888 |
SELECT COUNT(1) INTO v_nrunning |
---|
889 |
from stratcon.rollup_runner t, pg_stat_activity a |
---|
890 |
where rollup_table ='rollup_matrix_numeric_12hours' |
---|
891 |
and runner = procpid || '.' || date_part('epoch',backend_start); |
---|
892 |
|
---|
893 |
IF v_nrunning > 0 THEN |
---|
894 |
RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running'; |
---|
895 |
RETURN ; |
---|
896 |
END IF; |
---|
897 |
|
---|
898 |
SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) |
---|
899 |
FROM pg_stat_activity |
---|
900 |
WHERE procpid = pg_backend_pid(); |
---|
901 |
|
---|
902 |
IF v_self IS NULL THEN |
---|
903 |
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot self-identify'; |
---|
904 |
END IF; |
---|
905 |
|
---|
906 |
v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_12hours'''; |
---|
907 |
|
---|
908 |
EXECUTE v_sql; |
---|
909 |
|
---|
910 |
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='12 hours' LOOP |
---|
911 |
|
---|
912 |
SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours' |
---|
913 |
INTO v_min_whence; |
---|
914 |
|
---|
915 |
SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_12hours |
---|
916 |
INTO v_max_rollup_12; |
---|
917 |
|
---|
918 |
/*-- Insert Log for 24 Hours rollup |
---|
919 |
|
---|
920 |
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/24)*24) * '1 hour'::interval and interval='24 hours' |
---|
921 |
INTO v_whence; |
---|
922 |
IF NOT FOUND THEN |
---|
923 |
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/24)*24) * '1 hour'::interval,'24 hours'); |
---|
924 |
END IF; |
---|
925 |
*/ |
---|
926 |
|
---|
927 |
IF v_min_whence <= v_max_rollup_12 THEN |
---|
928 |
|
---|
929 |
DELETE FROM stratcon.rollup_matrix_numeric_12hours |
---|
930 |
WHERE rollup_time= v_min_whence; |
---|
931 |
|
---|
932 |
END IF; |
---|
933 |
|
---|
934 |
FOR rec IN |
---|
935 |
SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value |
---|
936 |
FROM stratcon.rollup_matrix_numeric_6hours |
---|
937 |
WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval |
---|
938 |
GROUP BY sid,name |
---|
939 |
LOOP |
---|
940 |
|
---|
941 |
|
---|
942 |
INSERT INTO stratcon.rollup_matrix_numeric_12hours |
---|
943 |
(sid,name,rollup_time,count_rows,avg_value) VALUES |
---|
944 |
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); |
---|
945 |
|
---|
946 |
END LOOP; |
---|
947 |
|
---|
948 |
|
---|
949 |
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours'; |
---|
950 |
|
---|
951 |
v_min_whence := NULL; |
---|
952 |
v_max_rollup_12 := NULL; |
---|
953 |
|
---|
954 |
END LOOP; |
---|
955 |
|
---|
956 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_12hours'; |
---|
957 |
|
---|
958 |
RETURN; |
---|
959 |
EXCEPTION |
---|
960 |
WHEN RAISE_EXCEPTION THEN |
---|
961 |
UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours'; |
---|
962 |
RAISE EXCEPTION '%', SQLERRM; |
---|
963 |
WHEN OTHERS THEN |
---|
964 |
RAISE NOTICE '%', SQLERRM; |
---|
965 |
END |
---|
966 |
$$ |
---|
967 |
LANGUAGE plpgsql; |
---|
968 |
|
---|
969 |
|
---|
970 |
-- |
---|
971 |
-- Name: rollup_matrix_numeric_20m(); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
972 |
-- |
---|
973 |
|
---|
974 |
CREATE FUNCTION rollup_matrix_numeric_20m() RETURNS void |
---|
975 |
AS $$ |
---|
976 |
DECLARE |
---|
977 |
|
---|
978 |
rec stratcon.rollup_matrix_numeric_20m%rowtype; |
---|
979 |
v_sql TEXT; |
---|
980 |
v_min_whence TIMESTAMPTZ; |
---|
981 |
v_max_rollup_20 TIMESTAMPTZ; |
---|
982 |
v_whence TIMESTAMPTZ; |
---|
983 |
rows INT; |
---|
984 |
v_nrunning INT; |
---|
985 |
v_self VARCHAR(22); |
---|
986 |
whenceint RECORD; |
---|
987 |
BEGIN |
---|
988 |
|
---|
989 |
SELECT COUNT(1) INTO v_nrunning |
---|
990 |
from stratcon.rollup_runner t, pg_stat_activity a |
---|
991 |
where rollup_table ='rollup_matrix_numeric_20m' |
---|
992 |
and runner = procpid || '.' || date_part('epoch',backend_start); |
---|
993 |
|
---|
994 |
IF v_nrunning > 0 THEN |
---|
995 |
RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running'; |
---|
996 |
RETURN ; |
---|
997 |
END IF; |
---|
998 |
|
---|
999 |
SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) |
---|
1000 |
FROM pg_stat_activity |
---|
1001 |
WHERE procpid = pg_backend_pid(); |
---|
1002 |
|
---|
1003 |
IF v_self IS NULL THEN |
---|
1004 |
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot self-identify'; |
---|
1005 |
END IF; |
---|
1006 |
|
---|
1007 |
v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_20m'''; |
---|
1008 |
|
---|
1009 |
EXECUTE v_sql; |
---|
1010 |
|
---|
1011 |
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='20 minutes' LOOP |
---|
1012 |
|
---|
1013 |
SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='20 minutes' |
---|
1014 |
INTO v_min_whence; |
---|
1015 |
|
---|
1016 |
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_20m |
---|
1017 |
INTO v_max_rollup_20; |
---|
1018 |
|
---|
1019 |
-- Insert Log for Hourly rollup |
---|
1020 |
|
---|
1021 |
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour' |
---|
1022 |
INTO v_whence; |
---|
1023 |
IF NOT FOUND THEN |
---|
1024 |
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour'); |
---|
1025 |
END IF; |
---|
1026 |
|
---|
1027 |
IF v_min_whence <= v_max_rollup_20 THEN |
---|
1028 |
|
---|
1029 |
DELETE FROM stratcon.rollup_matrix_numeric_20m |
---|
1030 |
WHERE rollup_time = v_min_whence; |
---|
1031 |
|
---|
1032 |
END IF; |
---|
1033 |
|
---|
1034 |
FOR rec IN |
---|
1035 |
SELECT sid , name,v_min_whence as rollup_time, |
---|
1036 |
SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value |
---|
1037 |
FROM stratcon.rollup_matrix_numeric_5m |
---|
1038 |
WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval |
---|
1039 |
GROUP BY sid,name |
---|
1040 |
|
---|
1041 |
LOOP |
---|
1042 |
|
---|
1043 |
|
---|
1044 |
INSERT INTO stratcon.rollup_matrix_numeric_20m |
---|
1045 |
(sid,name,rollup_time,count_rows,avg_value) VALUES |
---|
1046 |
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); |
---|
1047 |
|
---|
1048 |
END LOOP; |
---|
1049 |
|
---|
1050 |
-- Delete from whence log table |
---|
1051 |
|
---|
1052 |
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='20 minutes'; |
---|
1053 |
|
---|
1054 |
v_min_whence:= NULL; |
---|
1055 |
v_max_rollup_20:= NULL; |
---|
1056 |
|
---|
1057 |
END LOOP; |
---|
1058 |
|
---|
1059 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; |
---|
1060 |
|
---|
1061 |
RETURN; |
---|
1062 |
|
---|
1063 |
EXCEPTION |
---|
1064 |
WHEN RAISE_EXCEPTION THEN |
---|
1065 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; |
---|
1066 |
RAISE EXCEPTION '%', SQLERRM; |
---|
1067 |
WHEN OTHERS THEN |
---|
1068 |
RAISE NOTICE '%', SQLERRM; |
---|
1069 |
END |
---|
1070 |
$$ |
---|
1071 |
LANGUAGE plpgsql; |
---|
1072 |
|
---|
1073 |
|
---|
1074 |
-- |
---|
1075 |
-- Name: rollup_matrix_numeric_5m(); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
1076 |
-- |
---|
1077 |
|
---|
1078 |
CREATE FUNCTION rollup_matrix_numeric_5m() RETURNS void |
---|
1079 |
AS $$ |
---|
1080 |
DECLARE |
---|
1081 |
|
---|
1082 |
rec stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
1083 |
v_sql TEXT; |
---|
1084 |
v_min_whence TIMESTAMPTZ; |
---|
1085 |
v_max_rollup_5 TIMESTAMPTZ; |
---|
1086 |
v_whence TIMESTAMPTZ; |
---|
1087 |
rows INT; |
---|
1088 |
v_nrunning INT; |
---|
1089 |
v_self VARCHAR(22); |
---|
1090 |
whenceint RECORD; |
---|
1091 |
BEGIN |
---|
1092 |
|
---|
1093 |
SELECT COUNT(1) INTO v_nrunning |
---|
1094 |
from stratcon.rollup_runner t, pg_stat_activity a |
---|
1095 |
where rollup_table ='rollup_matrix_numeric_5m' |
---|
1096 |
and runner = procpid || '.' || date_part('epoch',backend_start); |
---|
1097 |
|
---|
1098 |
IF v_nrunning > 0 THEN |
---|
1099 |
RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running'; |
---|
1100 |
RETURN ; |
---|
1101 |
END IF; |
---|
1102 |
|
---|
1103 |
SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) |
---|
1104 |
FROM pg_stat_activity |
---|
1105 |
WHERE procpid = pg_backend_pid(); |
---|
1106 |
|
---|
1107 |
IF v_self IS NULL THEN |
---|
1108 |
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot self-identify'; |
---|
1109 |
END IF; |
---|
1110 |
|
---|
1111 |
v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_5m'''; |
---|
1112 |
|
---|
1113 |
EXECUTE v_sql; |
---|
1114 |
|
---|
1115 |
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='5 minutes' LOOP |
---|
1116 |
|
---|
1117 |
|
---|
1118 |
SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes' |
---|
1119 |
INTO v_min_whence; |
---|
1120 |
|
---|
1121 |
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_5m |
---|
1122 |
INTO v_max_rollup_5; |
---|
1123 |
|
---|
1124 |
-- Insert Log for 20 minutes rollup |
---|
1125 |
|
---|
1126 |
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval and interval='20 minutes' |
---|
1127 |
INTO v_whence; |
---|
1128 |
IF NOT FOUND THEN |
---|
1129 |
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval,'20 minutes'); |
---|
1130 |
END IF; |
---|
1131 |
IF v_min_whence <= v_max_rollup_5 THEN |
---|
1132 |
|
---|
1133 |
DELETE FROM stratcon.rollup_matrix_numeric_5m |
---|
1134 |
WHERE rollup_time = v_min_whence; |
---|
1135 |
|
---|
1136 |
END IF; |
---|
1137 |
|
---|
1138 |
FOR rec IN |
---|
1139 |
SELECT sid , name,v_min_whence as rollup_time, |
---|
1140 |
COUNT(1) as count_rows ,AVG(value) as avg_value |
---|
1141 |
FROM stratcon.loading_dock_metric_numeric_s |
---|
1142 |
WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval |
---|
1143 |
GROUP BY rollup_time,sid,name |
---|
1144 |
|
---|
1145 |
LOOP |
---|
1146 |
|
---|
1147 |
|
---|
1148 |
INSERT INTO stratcon.rollup_matrix_numeric_5m |
---|
1149 |
(sid,name,rollup_time,count_rows,avg_value) VALUES |
---|
1150 |
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); |
---|
1151 |
|
---|
1152 |
END LOOP; |
---|
1153 |
|
---|
1154 |
-- Delete from whence log table |
---|
1155 |
|
---|
1156 |
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes'; |
---|
1157 |
|
---|
1158 |
v_min_whence:= NULL; |
---|
1159 |
v_max_rollup_5:= NULL; |
---|
1160 |
|
---|
1161 |
END LOOP; |
---|
1162 |
|
---|
1163 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; |
---|
1164 |
|
---|
1165 |
RETURN; |
---|
1166 |
|
---|
1167 |
EXCEPTION |
---|
1168 |
WHEN RAISE_EXCEPTION THEN |
---|
1169 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; |
---|
1170 |
RAISE EXCEPTION '%', SQLERRM; |
---|
1171 |
WHEN OTHERS THEN |
---|
1172 |
RAISE NOTICE '%', SQLERRM; |
---|
1173 |
END |
---|
1174 |
$$ |
---|
1175 |
LANGUAGE plpgsql; |
---|
1176 |
|
---|
1177 |
|
---|
1178 |
-- |
---|
1179 |
-- Name: rollup_matrix_numeric_60m(); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
1180 |
-- |
---|
1181 |
|
---|
1182 |
CREATE FUNCTION rollup_matrix_numeric_60m() RETURNS void |
---|
1183 |
AS $$ |
---|
1184 |
DECLARE |
---|
1185 |
rec stratcon.rollup_matrix_numeric_60m%rowtype; |
---|
1186 |
v_sql TEXT; |
---|
1187 |
v_min_whence TIMESTAMPTZ; |
---|
1188 |
v_max_rollup_60 TIMESTAMPTZ; |
---|
1189 |
v_whence TIMESTAMPTZ; |
---|
1190 |
v_nrunning INT; |
---|
1191 |
v_self VARCHAR(22); |
---|
1192 |
whenceint RECORD; |
---|
1193 |
BEGIN |
---|
1194 |
|
---|
1195 |
SELECT COUNT(1) INTO v_nrunning |
---|
1196 |
from stratcon.rollup_runner t, pg_stat_activity a |
---|
1197 |
where rollup_table ='rollup_matrix_numeric_60m' |
---|
1198 |
and runner = procpid || '.' || date_part('epoch',backend_start); |
---|
1199 |
|
---|
1200 |
IF v_nrunning > 0 THEN |
---|
1201 |
RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running'; |
---|
1202 |
RETURN ; |
---|
1203 |
END IF; |
---|
1204 |
|
---|
1205 |
SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) |
---|
1206 |
FROM pg_stat_activity |
---|
1207 |
WHERE procpid = pg_backend_pid(); |
---|
1208 |
|
---|
1209 |
IF v_self IS NULL THEN |
---|
1210 |
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot self-identify'; |
---|
1211 |
END IF; |
---|
1212 |
|
---|
1213 |
v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_60m'''; |
---|
1214 |
|
---|
1215 |
EXECUTE v_sql; |
---|
1216 |
|
---|
1217 |
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='1 hour' LOOP |
---|
1218 |
|
---|
1219 |
SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour' |
---|
1220 |
INTO v_min_whence; |
---|
1221 |
|
---|
1222 |
SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_60m |
---|
1223 |
INTO v_max_rollup_60; |
---|
1224 |
|
---|
1225 |
-- Insert Log for 6 Hour rollup |
---|
1226 |
|
---|
1227 |
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/6)*6) * '1 hour'::interval and interval='6 hours' |
---|
1228 |
INTO v_whence; |
---|
1229 |
IF NOT FOUND THEN |
---|
1230 |
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/6)*6) * '1 hour'::interval,'6 hours'); |
---|
1231 |
END IF; |
---|
1232 |
|
---|
1233 |
|
---|
1234 |
IF v_min_whence <= v_max_rollup_60 THEN |
---|
1235 |
|
---|
1236 |
DELETE FROM stratcon.rollup_matrix_numeric_60m |
---|
1237 |
WHERE rollup_time= v_min_whence; |
---|
1238 |
|
---|
1239 |
END IF; |
---|
1240 |
|
---|
1241 |
FOR rec IN |
---|
1242 |
SELECT sid,name,date_hour(rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value |
---|
1243 |
FROM stratcon.rollup_matrix_numeric_20m |
---|
1244 |
WHERE date_hour(rollup_time)= v_min_whence |
---|
1245 |
GROUP BY date_hour(rollup_time),sid,name |
---|
1246 |
LOOP |
---|
1247 |
|
---|
1248 |
INSERT INTO stratcon.rollup_matrix_numeric_60m |
---|
1249 |
(sid,name,rollup_time,count_rows,avg_value) VALUES |
---|
1250 |
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); |
---|
1251 |
|
---|
1252 |
END LOOP; |
---|
1253 |
|
---|
1254 |
|
---|
1255 |
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour'; |
---|
1256 |
|
---|
1257 |
v_min_whence := NULL; |
---|
1258 |
v_max_rollup_60 := NULL; |
---|
1259 |
|
---|
1260 |
END LOOP; |
---|
1261 |
|
---|
1262 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; |
---|
1263 |
|
---|
1264 |
RETURN; |
---|
1265 |
|
---|
1266 |
EXCEPTION |
---|
1267 |
WHEN RAISE_EXCEPTION THEN |
---|
1268 |
RAISE EXCEPTION '%', SQLERRM; |
---|
1269 |
WHEN OTHERS THEN |
---|
1270 |
RAISE NOTICE '%', SQLERRM; |
---|
1271 |
END |
---|
1272 |
$$ |
---|
1273 |
LANGUAGE plpgsql; |
---|
1274 |
|
---|
1275 |
|
---|
1276 |
-- |
---|
1277 |
-- Name: rollup_matrix_numeric_6hours(); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
1278 |
-- |
---|
1279 |
|
---|
1280 |
CREATE FUNCTION rollup_matrix_numeric_6hours() RETURNS void |
---|
1281 |
AS $$ |
---|
1282 |
DECLARE |
---|
1283 |
rec stratcon.rollup_matrix_numeric_6hours%rowtype; |
---|
1284 |
v_sql TEXT; |
---|
1285 |
v_min_whence TIMESTAMPTZ; |
---|
1286 |
v_max_rollup_6 TIMESTAMPTZ; |
---|
1287 |
v_whence TIMESTAMPTZ; |
---|
1288 |
v_nrunning INT; |
---|
1289 |
v_self VARCHAR(22); |
---|
1290 |
whenceint RECORD; |
---|
1291 |
BEGIN |
---|
1292 |
|
---|
1293 |
SELECT COUNT(1) INTO v_nrunning |
---|
1294 |
from stratcon.rollup_runner t, pg_stat_activity a |
---|
1295 |
where rollup_table ='rollup_matrix_numeric_6hours' |
---|
1296 |
and runner = procpid || '.' || date_part('epoch',backend_start); |
---|
1297 |
|
---|
1298 |
IF v_nrunning > 0 THEN |
---|
1299 |
RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running'; |
---|
1300 |
RETURN ; |
---|
1301 |
END IF; |
---|
1302 |
|
---|
1303 |
SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) |
---|
1304 |
FROM pg_stat_activity |
---|
1305 |
WHERE procpid = pg_backend_pid(); |
---|
1306 |
|
---|
1307 |
IF v_self IS NULL THEN |
---|
1308 |
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot self-identify'; |
---|
1309 |
END IF; |
---|
1310 |
|
---|
1311 |
v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_6hours'''; |
---|
1312 |
|
---|
1313 |
EXECUTE v_sql; |
---|
1314 |
|
---|
1315 |
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='6 hours' LOOP |
---|
1316 |
|
---|
1317 |
SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours' |
---|
1318 |
INTO v_min_whence; |
---|
1319 |
|
---|
1320 |
SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_6hours |
---|
1321 |
INTO v_max_rollup_6; |
---|
1322 |
|
---|
1323 |
-- Insert Log for 12 Hours rollup |
---|
1324 |
|
---|
1325 |
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/12)*12) * '1 hour'::interval and interval='12 hours' |
---|
1326 |
INTO v_whence; |
---|
1327 |
IF NOT FOUND THEN |
---|
1328 |
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/12)*12) * '1 hour'::interval,'12 hours'); |
---|
1329 |
END IF; |
---|
1330 |
|
---|
1331 |
|
---|
1332 |
IF v_min_whence <= v_max_rollup_6 THEN |
---|
1333 |
|
---|
1334 |
DELETE FROM stratcon.rollup_matrix_numeric_6hours |
---|
1335 |
WHERE rollup_time= v_min_whence; |
---|
1336 |
|
---|
1337 |
END IF; |
---|
1338 |
|
---|
1339 |
FOR rec IN |
---|
1340 |
SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value |
---|
1341 |
FROM stratcon.rollup_matrix_numeric_60m |
---|
1342 |
WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval |
---|
1343 |
GROUP BY sid,name |
---|
1344 |
LOOP |
---|
1345 |
|
---|
1346 |
|
---|
1347 |
INSERT INTO stratcon.rollup_matrix_numeric_6hours |
---|
1348 |
(sid,name,rollup_time,count_rows,avg_value) VALUES |
---|
1349 |
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); |
---|
1350 |
|
---|
1351 |
END LOOP; |
---|
1352 |
|
---|
1353 |
|
---|
1354 |
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours'; |
---|
1355 |
v_min_whence := NULL; |
---|
1356 |
v_max_rollup_6 := NULL; |
---|
1357 |
|
---|
1358 |
END LOOP; |
---|
1359 |
|
---|
1360 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours'; |
---|
1361 |
|
---|
1362 |
RETURN; |
---|
1363 |
|
---|
1364 |
EXCEPTION |
---|
1365 |
WHEN RAISE_EXCEPTION THEN |
---|
1366 |
RAISE EXCEPTION '%', SQLERRM; |
---|
1367 |
WHEN OTHERS THEN |
---|
1368 |
RAISE NOTICE '%', SQLERRM; |
---|
1369 |
END |
---|
1370 |
$$ |
---|
1371 |
LANGUAGE plpgsql; |
---|
1372 |
|
---|
1373 |
|
---|
1374 |
-- |
---|
1375 |
-- Name: update_config(inet, text, timestamp with time zone, xml); Type: FUNCTION; Schema: stratcon; Owner: - |
---|
1376 |
-- |
---|
1377 |
|
---|
1378 |
CREATE FUNCTION update_config(v_remote_address_in inet, v_node_type_in text, v_whence_in timestamp with time zone, v_config_in xml) RETURNS void |
---|
1379 |
AS $$ |
---|
1380 |
DECLARE |
---|
1381 |
v_config xml; |
---|
1382 |
BEGIN |
---|
1383 |
select config into v_config from stratcon.current_node_config |
---|
1384 |
where remote_address = v_remote_address_in |
---|
1385 |
and node_type = v_node_type_in; |
---|
1386 |
IF FOUND THEN |
---|
1387 |
IF v_config::text = v_config_in::text THEN |
---|
1388 |
RETURN; |
---|
1389 |
END IF; |
---|
1390 |
delete from stratcon.current_node_config |
---|
1391 |
where remote_address = v_remote_address_in |
---|
1392 |
and node_type = v_node_type_in; |
---|
1393 |
END IF; |
---|
1394 |
insert into stratcon.current_node_config |
---|
1395 |
(remote_address, node_type, whence, config) |
---|
1396 |
values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in); |
---|
1397 |
insert into stratcon.current_node_config_changelog |
---|
1398 |
(remote_address, node_type, whence, config) |
---|
1399 |
values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in); |
---|
1400 |
END |
---|
1401 |
$$ |
---|
1402 |
LANGUAGE plpgsql; |
---|
1403 |
|
---|
1404 |
|
---|
1405 |
-- |
---|
1406 |
-- Name: seq_sid; Type: SEQUENCE; Schema: stratcon; Owner: - |
---|
1407 |
-- |
---|
1408 |
|
---|
1409 |
CREATE SEQUENCE seq_sid |
---|
1410 |
INCREMENT BY 1 |
---|
1411 |
NO MAXVALUE |
---|
1412 |
NO MINVALUE |
---|
1413 |
CACHE 1; |
---|
1414 |
|
---|
1415 |
|
---|
1416 |
-- |
---|
1417 |
-- Name: current_node_config_changelog_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1418 |
-- |
---|
1419 |
|
---|
1420 |
ALTER TABLE ONLY current_node_config_changelog |
---|
1421 |
ADD CONSTRAINT current_node_config_changelog_pkey PRIMARY KEY (remote_address, node_type, whence); |
---|
1422 |
|
---|
1423 |
|
---|
1424 |
-- |
---|
1425 |
-- Name: current_node_config_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1426 |
-- |
---|
1427 |
|
---|
1428 |
ALTER TABLE ONLY current_node_config |
---|
1429 |
ADD CONSTRAINT current_node_config_pkey PRIMARY KEY (remote_address, node_type); |
---|
1430 |
|
---|
1431 |
|
---|
1432 |
-- |
---|
1433 |
-- Name: loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1434 |
-- |
---|
1435 |
|
---|
1436 |
ALTER TABLE ONLY loading_dock_check_s |
---|
1437 |
ADD CONSTRAINT loading_dock_check_s_pkey PRIMARY KEY (sid, id, whence); |
---|
1438 |
|
---|
1439 |
|
---|
1440 |
-- |
---|
1441 |
-- Name: loading_dock_metric_numeric_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1442 |
-- |
---|
1443 |
|
---|
1444 |
ALTER TABLE ONLY loading_dock_metric_numeric_s |
---|
1445 |
ADD CONSTRAINT loading_dock_metric_numeric_s_pkey PRIMARY KEY (whence, sid, name); |
---|
1446 |
|
---|
1447 |
ALTER TABLE loading_dock_metric_numeric_s CLUSTER ON loading_dock_metric_numeric_s_pkey; |
---|
1448 |
|
---|
1449 |
|
---|
1450 |
-- |
---|
1451 |
-- Name: loading_dock_metric_text_s_change_log_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1452 |
-- |
---|
1453 |
|
---|
1454 |
ALTER TABLE ONLY loading_dock_metric_text_s_change_log |
---|
1455 |
ADD CONSTRAINT loading_dock_metric_text_s_change_log_pkey PRIMARY KEY (whence, sid, name); |
---|
1456 |
|
---|
1457 |
|
---|
1458 |
-- |
---|
1459 |
-- Name: loading_dock_metric_text_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1460 |
-- |
---|
1461 |
|
---|
1462 |
ALTER TABLE ONLY loading_dock_metric_text_s |
---|
1463 |
ADD CONSTRAINT loading_dock_metric_text_s_pkey PRIMARY KEY (whence, sid, name); |
---|
1464 |
|
---|
1465 |
|
---|
1466 |
-- |
---|
1467 |
-- Name: loading_dock_status_s_change_log_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1468 |
-- |
---|
1469 |
|
---|
1470 |
ALTER TABLE ONLY loading_dock_status_s_change_log |
---|
1471 |
ADD CONSTRAINT loading_dock_status_s_change_log_pkey PRIMARY KEY (sid, whence); |
---|
1472 |
|
---|
1473 |
|
---|
1474 |
-- |
---|
1475 |
-- Name: loading_dock_status_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1476 |
-- |
---|
1477 |
|
---|
1478 |
ALTER TABLE ONLY loading_dock_status_s |
---|
1479 |
ADD CONSTRAINT loading_dock_status_s_pkey PRIMARY KEY (sid, whence); |
---|
1480 |
|
---|
1481 |
|
---|
1482 |
-- |
---|
1483 |
-- Name: log_whence_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1484 |
-- |
---|
1485 |
|
---|
1486 |
ALTER TABLE ONLY log_whence_s |
---|
1487 |
ADD CONSTRAINT log_whence_s_pkey PRIMARY KEY (whence, "interval"); |
---|
1488 |
|
---|
1489 |
|
---|
1490 |
-- |
---|
1491 |
-- Name: map_uuid_to_sid_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1492 |
-- |
---|
1493 |
|
---|
1494 |
ALTER TABLE ONLY map_uuid_to_sid |
---|
1495 |
ADD CONSTRAINT map_uuid_to_sid_pkey PRIMARY KEY (id, sid); |
---|
1496 |
|
---|
1497 |
|
---|
1498 |
-- |
---|
1499 |
-- Name: metric_name_summary_pk; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1500 |
-- |
---|
1501 |
|
---|
1502 |
ALTER TABLE ONLY metric_name_summary |
---|
1503 |
ADD CONSTRAINT metric_name_summary_pk UNIQUE (sid, metric_name, metric_type); |
---|
1504 |
|
---|
1505 |
|
---|
1506 |
-- |
---|
1507 |
-- Name: mv_loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1508 |
-- |
---|
1509 |
|
---|
1510 |
ALTER TABLE ONLY mv_loading_dock_check_s |
---|
1511 |
ADD CONSTRAINT mv_loading_dock_check_s_pkey PRIMARY KEY (sid); |
---|
1512 |
|
---|
1513 |
|
---|
1514 |
-- |
---|
1515 |
-- Name: rollup_matrix_numeric_12hours_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1516 |
-- |
---|
1517 |
|
---|
1518 |
ALTER TABLE ONLY rollup_matrix_numeric_12hours |
---|
1519 |
ADD CONSTRAINT rollup_matrix_numeric_12hours_pkey PRIMARY KEY (rollup_time, sid, name); |
---|
1520 |
|
---|
1521 |
ALTER TABLE rollup_matrix_numeric_12hours CLUSTER ON rollup_matrix_numeric_12hours_pkey; |
---|
1522 |
|
---|
1523 |
|
---|
1524 |
-- |
---|
1525 |
-- Name: rollup_matrix_numeric_20m_new_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1526 |
-- |
---|
1527 |
|
---|
1528 |
ALTER TABLE ONLY rollup_matrix_numeric_20m |
---|
1529 |
ADD CONSTRAINT rollup_matrix_numeric_20m_new_pkey PRIMARY KEY (rollup_time, sid, name); |
---|
1530 |
|
---|
1531 |
ALTER TABLE rollup_matrix_numeric_20m CLUSTER ON rollup_matrix_numeric_20m_new_pkey; |
---|
1532 |
|
---|
1533 |
|
---|
1534 |
-- |
---|
1535 |
-- Name: rollup_matrix_numeric_5m_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1536 |
-- |
---|
1537 |
|
---|
1538 |
ALTER TABLE ONLY rollup_matrix_numeric_5m |
---|
1539 |
ADD CONSTRAINT rollup_matrix_numeric_5m_pkey PRIMARY KEY (rollup_time, sid, name); |
---|
1540 |
|
---|
1541 |
ALTER TABLE rollup_matrix_numeric_5m CLUSTER ON rollup_matrix_numeric_5m_pkey; |
---|
1542 |
|
---|
1543 |
|
---|
1544 |
-- |
---|
1545 |
-- Name: rollup_matrix_numeric_60m_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1546 |
-- |
---|
1547 |
|
---|
1548 |
ALTER TABLE ONLY rollup_matrix_numeric_60m |
---|
1549 |
ADD CONSTRAINT rollup_matrix_numeric_60m_pkey PRIMARY KEY (rollup_time, sid, name); |
---|
1550 |
|
---|
1551 |
ALTER TABLE rollup_matrix_numeric_60m CLUSTER ON rollup_matrix_numeric_60m_pkey; |
---|
1552 |
|
---|
1553 |
|
---|
1554 |
-- |
---|
1555 |
-- Name: rollup_matrix_numeric_6hours_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: -; Tablespace: |
---|
1556 |
-- |
---|
1557 |
|
---|
1558 |
ALTER TABLE ONLY rollup_matrix_numeric_6hours |
---|
1559 |
ADD CONSTRAINT rollup_matrix_numeric_6hours_pkey PRIMARY KEY (rollup_time, sid, name); |
---|
1560 |
|
---|
1561 |
ALTER TABLE rollup_matrix_numeric_6hours CLUSTER ON rollup_matrix_numeric_6hours_pkey; |
---|
1562 |
|
---|
1563 |
|
---|
1564 |
-- |
---|
1565 |
-- Name: idx_mv_loading_dock_check_s_module; Type: INDEX; Schema: stratcon; Owner: -; Tablespace: |
---|
1566 |
-- |
---|
1567 |
|
---|
1568 |
CREATE INDEX idx_mv_loading_dock_check_s_module ON mv_loading_dock_check_s USING btree (module); |
---|
1569 |
|
---|
1570 |
|
---|
1571 |
-- |
---|
1572 |
-- Name: idx_mv_loading_dock_check_s_name; Type: INDEX; Schema: stratcon; Owner: -; Tablespace: |
---|
1573 |
-- |
---|
1574 |
|
---|
1575 |
CREATE INDEX idx_mv_loading_dock_check_s_name ON mv_loading_dock_check_s USING btree (name); |
---|
1576 |
|
---|
1577 |
|
---|
1578 |
-- |
---|
1579 |
-- Name: idx_mv_loading_dock_check_s_target; Type: INDEX; Schema: stratcon; Owner: -; Tablespace: |
---|
1580 |
-- |
---|
1581 |
|
---|
1582 |
CREATE INDEX idx_mv_loading_dock_check_s_target ON mv_loading_dock_check_s USING btree (target); |
---|
1583 |
|
---|
1584 |
|
---|
1585 |
-- |
---|
1586 |
-- Name: idx_rollup_matrix_numeric_20m_rollup_time; Type: INDEX; Schema: stratcon; Owner: -; Tablespace: |
---|
1587 |
-- |
---|
1588 |
|
---|
1589 |
CREATE INDEX idx_rollup_matrix_numeric_20m_rollup_time ON rollup_matrix_numeric_20m USING btree (date_hour(rollup_time)); |
---|
1590 |
|
---|
1591 |
|
---|
1592 |
-- |
---|
1593 |
-- Name: unq_mv_loading_dock_check_s_id; Type: INDEX; Schema: stratcon; Owner: -; Tablespace: |
---|
1594 |
-- |
---|
1595 |
|
---|
1596 |
CREATE UNIQUE INDEX unq_mv_loading_dock_check_s_id ON mv_loading_dock_check_s USING btree (id); |
---|
1597 |
|
---|
1598 |
|
---|
1599 |
-- |
---|
1600 |
-- Name: loading_dock_metric_numeric_s_whence_log; Type: TRIGGER; Schema: stratcon; Owner: - |
---|
1601 |
-- |
---|
1602 |
|
---|
1603 |
CREATE TRIGGER loading_dock_metric_numeric_s_whence_log |
---|
1604 |
AFTER INSERT ON loading_dock_metric_numeric_s |
---|
1605 |
FOR EACH ROW |
---|
1606 |
EXECUTE PROCEDURE loading_dock_metric_numeric_s_whence_log(); |
---|
1607 |
|
---|
1608 |
|
---|
1609 |
-- |
---|
1610 |
-- Name: loading_dock_metric_text_s_change_log; Type: TRIGGER; Schema: stratcon; Owner: - |
---|
1611 |
-- |
---|
1612 |
|
---|
1613 |
CREATE TRIGGER loading_dock_metric_text_s_change_log |
---|
1614 |
AFTER INSERT ON loading_dock_metric_text_s |
---|
1615 |
FOR EACH ROW |
---|
1616 |
EXECUTE PROCEDURE loading_dock_metric_text_s_change_log(); |
---|
1617 |
|
---|
1618 |
|
---|
1619 |
-- |
---|
1620 |
-- Name: loading_dock_status_s_change_log; Type: TRIGGER; Schema: stratcon; Owner: - |
---|
1621 |
-- |
---|
1622 |
|
---|
1623 |
CREATE TRIGGER loading_dock_status_s_change_log |
---|
1624 |
AFTER INSERT ON loading_dock_status_s |
---|
1625 |
FOR EACH ROW |
---|
1626 |
EXECUTE PROCEDURE loading_dock_status_s_change_log(); |
---|
1627 |
|
---|
1628 |
|
---|
1629 |
-- |
---|
1630 |
-- Name: mv_loading_dock_check_s; Type: TRIGGER; Schema: stratcon; Owner: - |
---|
1631 |
-- |
---|
1632 |
|
---|
1633 |
CREATE TRIGGER mv_loading_dock_check_s |
---|
1634 |
AFTER INSERT ON loading_dock_check_s |
---|
1635 |
FOR EACH ROW |
---|
1636 |
EXECUTE PROCEDURE mv_loading_dock_check_s(); |
---|
1637 |
|
---|
1638 |
|
---|
1639 |
-- |
---|
1640 |
-- Name: stratcon; Type: ACL; Schema: -; Owner: - |
---|
1641 |
-- |
---|
1642 |
|
---|
1643 |
REVOKE ALL ON SCHEMA stratcon FROM PUBLIC; |
---|
1644 |
REVOKE ALL ON SCHEMA stratcon FROM omniti; |
---|
1645 |
GRANT ALL ON SCHEMA stratcon TO omniti; |
---|
1646 |
GRANT USAGE ON SCHEMA stratcon TO stratcon; |
---|
1647 |
|
---|
1648 |
|
---|
1649 |
-- |
---|
1650 |
-- Name: loading_dock_check_s; Type: ACL; Schema: stratcon; Owner: - |
---|
1651 |
-- |
---|
1652 |
|
---|
1653 |
REVOKE ALL ON TABLE loading_dock_check_s FROM PUBLIC; |
---|
1654 |
REVOKE ALL ON TABLE loading_dock_check_s FROM omniti; |
---|
1655 |
GRANT ALL ON TABLE loading_dock_check_s TO omniti; |
---|
1656 |
GRANT SELECT,INSERT ON TABLE loading_dock_check_s TO stratcon; |
---|
1657 |
|
---|
1658 |
|
---|
1659 |
-- |
---|
1660 |
-- Name: loading_dock_metric_numeric_s; Type: ACL; Schema: stratcon; Owner: - |
---|
1661 |
-- |
---|
1662 |
|
---|
1663 |
REVOKE ALL ON TABLE loading_dock_metric_numeric_s FROM PUBLIC; |
---|
1664 |
REVOKE ALL ON TABLE loading_dock_metric_numeric_s FROM omniti; |
---|
1665 |
GRANT ALL ON TABLE loading_dock_metric_numeric_s TO omniti; |
---|
1666 |
GRANT SELECT,INSERT ON TABLE loading_dock_metric_numeric_s TO stratcon; |
---|
1667 |
|
---|
1668 |
|
---|
1669 |
-- |
---|
1670 |
-- Name: loading_dock_metric_text_s; Type: ACL; Schema: stratcon; Owner: - |
---|
1671 |
-- |
---|
1672 |
|
---|
1673 |
REVOKE ALL ON TABLE loading_dock_metric_text_s FROM PUBLIC; |
---|
1674 |
REVOKE ALL ON TABLE loading_dock_metric_text_s FROM omniti; |
---|
1675 |
GRANT ALL ON TABLE loading_dock_metric_text_s TO omniti; |
---|
1676 |
GRANT SELECT,INSERT,DELETE ON TABLE loading_dock_metric_text_s TO stratcon; |
---|
1677 |
|
---|
1678 |
|
---|
1679 |
-- |
---|
1680 |
-- Name: loading_dock_metric_text_s_change_log; Type: ACL; Schema: stratcon; Owner: - |
---|
1681 |
-- |
---|
1682 |
|
---|
1683 |
REVOKE ALL ON TABLE loading_dock_metric_text_s_change_log FROM PUBLIC; |
---|
1684 |
REVOKE ALL ON TABLE loading_dock_metric_text_s_change_log FROM omniti; |
---|
1685 |
GRANT ALL ON TABLE loading_dock_metric_text_s_change_log TO omniti; |
---|
1686 |
GRANT SELECT,INSERT ON TABLE loading_dock_metric_text_s_change_log TO stratcon; |
---|
1687 |
|
---|
1688 |
|
---|
1689 |
-- |
---|
1690 |
-- Name: loading_dock_status_s; Type: ACL; Schema: stratcon; Owner: - |
---|
1691 |
-- |
---|
1692 |
|
---|
1693 |
REVOKE ALL ON TABLE loading_dock_status_s FROM PUBLIC; |
---|
1694 |
REVOKE ALL ON TABLE loading_dock_status_s FROM omniti; |
---|
1695 |
GRANT ALL ON TABLE loading_dock_status_s TO omniti; |
---|
1696 |
GRANT SELECT,INSERT ON TABLE loading_dock_status_s TO stratcon; |
---|
1697 |
|
---|
1698 |
|
---|
1699 |
-- |
---|
1700 |
-- Name: loading_dock_status_s_change_log; Type: ACL; Schema: stratcon; Owner: - |
---|
1701 |
-- |
---|
1702 |
|
---|
1703 |
REVOKE ALL ON TABLE loading_dock_status_s_change_log FROM PUBLIC; |
---|
1704 |
REVOKE ALL ON TABLE loading_dock_status_s_change_log FROM omniti; |
---|
1705 |
GRANT ALL ON TABLE loading_dock_status_s_change_log TO omniti; |
---|
1706 |
GRANT SELECT,INSERT,DELETE ON TABLE loading_dock_status_s_change_log TO stratcon; |
---|
1707 |
|
---|
1708 |
|
---|
1709 |
-- |
---|
1710 |
-- Name: log_whence_s; Type: ACL; Schema: stratcon; Owner: - |
---|
1711 |
-- |
---|
1712 |
|
---|
1713 |
REVOKE ALL ON TABLE log_whence_s FROM PUBLIC; |
---|
1714 |
REVOKE ALL ON TABLE log_whence_s FROM omniti; |
---|
1715 |
GRANT ALL ON TABLE log_whence_s TO omniti; |
---|
1716 |
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE log_whence_s TO stratcon; |
---|
1717 |
|
---|
1718 |
|
---|
1719 |
-- |
---|
1720 |
-- Name: map_uuid_to_sid; Type: ACL; Schema: stratcon; Owner: - |
---|
1721 |
-- |
---|
1722 |
|
---|
1723 |
REVOKE ALL ON TABLE map_uuid_to_sid FROM PUBLIC; |
---|
1724 |
REVOKE ALL ON TABLE map_uuid_to_sid FROM omniti; |
---|
1725 |
GRANT ALL ON TABLE map_uuid_to_sid TO omniti; |
---|
1726 |
GRANT SELECT,INSERT ON TABLE map_uuid_to_sid TO stratcon; |
---|
1727 |
|
---|
1728 |
|
---|
1729 |
-- |
---|
1730 |
-- Name: metric_name_summary; Type: ACL; Schema: stratcon; Owner: - |
---|
1731 |
-- |
---|
1732 |
|
---|
1733 |
REVOKE ALL ON TABLE metric_name_summary FROM PUBLIC; |
---|
1734 |
REVOKE ALL ON TABLE metric_name_summary FROM omniti; |
---|
1735 |
GRANT ALL ON TABLE metric_name_summary TO omniti; |
---|
1736 |
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE metric_name_summary TO stratcon; |
---|
1737 |
|
---|
1738 |
|
---|
1739 |
-- |
---|
1740 |
-- Name: rollup_matrix_numeric_12hours; Type: ACL; Schema: stratcon; Owner: - |
---|
1741 |
-- |
---|
1742 |
|
---|
1743 |
REVOKE ALL ON TABLE rollup_matrix_numeric_12hours FROM PUBLIC; |
---|
1744 |
REVOKE ALL ON TABLE rollup_matrix_numeric_12hours FROM postgres; |
---|
1745 |
GRANT ALL ON TABLE rollup_matrix_numeric_12hours TO postgres; |
---|
1746 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_12hours TO stratcon; |
---|
1747 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_12hours TO omniti; |
---|
1748 |
|
---|
1749 |
|
---|
1750 |
-- |
---|
1751 |
-- Name: rollup_matrix_numeric_20m; Type: ACL; Schema: stratcon; Owner: - |
---|
1752 |
-- |
---|
1753 |
|
---|
1754 |
REVOKE ALL ON TABLE rollup_matrix_numeric_20m FROM PUBLIC; |
---|
1755 |
REVOKE ALL ON TABLE rollup_matrix_numeric_20m FROM omniti; |
---|
1756 |
GRANT ALL ON TABLE rollup_matrix_numeric_20m TO omniti; |
---|
1757 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_20m TO stratcon; |
---|
1758 |
|
---|
1759 |
|
---|
1760 |
-- |
---|
1761 |
-- Name: rollup_matrix_numeric_5m; Type: ACL; Schema: stratcon; Owner: - |
---|
1762 |
-- |
---|
1763 |
|
---|
1764 |
REVOKE ALL ON TABLE rollup_matrix_numeric_5m FROM PUBLIC; |
---|
1765 |
REVOKE ALL ON TABLE rollup_matrix_numeric_5m FROM omniti; |
---|
1766 |
GRANT ALL ON TABLE rollup_matrix_numeric_5m TO omniti; |
---|
1767 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_5m TO stratcon; |
---|
1768 |
|
---|
1769 |
|
---|
1770 |
-- |
---|
1771 |
-- Name: rollup_matrix_numeric_60m; Type: ACL; Schema: stratcon; Owner: - |
---|
1772 |
-- |
---|
1773 |
|
---|
1774 |
REVOKE ALL ON TABLE rollup_matrix_numeric_60m FROM PUBLIC; |
---|
1775 |
REVOKE ALL ON TABLE rollup_matrix_numeric_60m FROM omniti; |
---|
1776 |
GRANT ALL ON TABLE rollup_matrix_numeric_60m TO omniti; |
---|
1777 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_60m TO stratcon; |
---|
1778 |
|
---|
1779 |
|
---|
1780 |
-- |
---|
1781 |
-- Name: rollup_matrix_numeric_6hours; Type: ACL; Schema: stratcon; Owner: - |
---|
1782 |
-- |
---|
1783 |
|
---|
1784 |
REVOKE ALL ON TABLE rollup_matrix_numeric_6hours FROM PUBLIC; |
---|
1785 |
REVOKE ALL ON TABLE rollup_matrix_numeric_6hours FROM omniti; |
---|
1786 |
GRANT ALL ON TABLE rollup_matrix_numeric_6hours TO omniti; |
---|
1787 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_6hours TO stratcon; |
---|
1788 |
|
---|
1789 |
|
---|
1790 |
-- |
---|
1791 |
-- Name: rollup_runner; Type: ACL; Schema: stratcon; Owner: - |
---|
1792 |
-- |
---|
1793 |
|
---|
1794 |
REVOKE ALL ON TABLE rollup_runner FROM PUBLIC; |
---|
1795 |
REVOKE ALL ON TABLE rollup_runner FROM omniti; |
---|
1796 |
GRANT ALL ON TABLE rollup_runner TO omniti; |
---|
1797 |
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE rollup_runner TO stratcon; |
---|
1798 |
|
---|
1799 |
|
---|
1800 |
-- |
---|
1801 |
-- PostgreSQL database dump complete |
---|
1802 |
-- |
---|
1803 |
|
---|