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: prism; Type: SCHEMA; Schema: -; Owner: prism |
---|
13 |
-- |
---|
14 |
|
---|
15 |
CREATE SCHEMA prism; |
---|
16 |
|
---|
17 |
|
---|
18 |
ALTER SCHEMA prism OWNER TO prism; |
---|
19 |
|
---|
20 |
-- |
---|
21 |
-- Name: stratcon; Type: SCHEMA; Schema: -; Owner: stratcon |
---|
22 |
-- |
---|
23 |
|
---|
24 |
CREATE SCHEMA stratcon; |
---|
25 |
|
---|
26 |
|
---|
27 |
ALTER SCHEMA stratcon OWNER TO stratcon; |
---|
28 |
|
---|
29 |
-- |
---|
30 |
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: omniti |
---|
31 |
-- |
---|
32 |
|
---|
33 |
CREATE PROCEDURAL LANGUAGE plpgsql; |
---|
34 |
|
---|
35 |
|
---|
36 |
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO omniti; |
---|
37 |
|
---|
38 |
SET search_path = prism, pg_catalog; |
---|
39 |
|
---|
40 |
SET default_tablespace = ''; |
---|
41 |
|
---|
42 |
SET default_with_oids = false; |
---|
43 |
|
---|
44 |
-- |
---|
45 |
-- Name: saved_graphs; Type: TABLE; Schema: prism; Owner: reconnoiter; Tablespace: |
---|
46 |
-- |
---|
47 |
|
---|
48 |
CREATE TABLE saved_graphs ( |
---|
49 |
graphid uuid NOT NULL, |
---|
50 |
json text NOT NULL, |
---|
51 |
saved boolean DEFAULT false NOT NULL, |
---|
52 |
title text, |
---|
53 |
last_update timestamp without time zone NOT NULL |
---|
54 |
); |
---|
55 |
|
---|
56 |
|
---|
57 |
ALTER TABLE prism.saved_graphs OWNER TO reconnoiter; |
---|
58 |
|
---|
59 |
-- |
---|
60 |
-- Name: saved_graphs_dep; Type: TABLE; Schema: prism; Owner: reconnoiter; Tablespace: |
---|
61 |
-- |
---|
62 |
|
---|
63 |
CREATE TABLE saved_graphs_dep ( |
---|
64 |
graphid uuid NOT NULL, |
---|
65 |
sid integer NOT NULL, |
---|
66 |
metric_name text NOT NULL, |
---|
67 |
metric_type character varying(22) |
---|
68 |
); |
---|
69 |
|
---|
70 |
|
---|
71 |
ALTER TABLE prism.saved_graphs_dep OWNER TO reconnoiter; |
---|
72 |
|
---|
73 |
SET search_path = public, pg_catalog; |
---|
74 |
|
---|
75 |
SET default_with_oids = true; |
---|
76 |
|
---|
77 |
-- |
---|
78 |
-- Name: pga_diagrams; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace: |
---|
79 |
-- |
---|
80 |
|
---|
81 |
CREATE TABLE pga_diagrams ( |
---|
82 |
diagramname character varying(64) NOT NULL, |
---|
83 |
diagramtables text, |
---|
84 |
diagramlinks text |
---|
85 |
); |
---|
86 |
|
---|
87 |
|
---|
88 |
ALTER TABLE public.pga_diagrams OWNER TO reconnoiter; |
---|
89 |
|
---|
90 |
-- |
---|
91 |
-- Name: pga_forms; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace: |
---|
92 |
-- |
---|
93 |
|
---|
94 |
CREATE TABLE pga_forms ( |
---|
95 |
formname character varying(64) NOT NULL, |
---|
96 |
formsource text |
---|
97 |
); |
---|
98 |
|
---|
99 |
|
---|
100 |
ALTER TABLE public.pga_forms OWNER TO reconnoiter; |
---|
101 |
|
---|
102 |
-- |
---|
103 |
-- Name: pga_graphs; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace: |
---|
104 |
-- |
---|
105 |
|
---|
106 |
CREATE TABLE pga_graphs ( |
---|
107 |
graphname character varying(64) NOT NULL, |
---|
108 |
graphsource text, |
---|
109 |
graphcode text |
---|
110 |
); |
---|
111 |
|
---|
112 |
|
---|
113 |
ALTER TABLE public.pga_graphs OWNER TO reconnoiter; |
---|
114 |
|
---|
115 |
-- |
---|
116 |
-- Name: pga_images; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace: |
---|
117 |
-- |
---|
118 |
|
---|
119 |
CREATE TABLE pga_images ( |
---|
120 |
imagename character varying(64) NOT NULL, |
---|
121 |
imagesource text |
---|
122 |
); |
---|
123 |
|
---|
124 |
|
---|
125 |
ALTER TABLE public.pga_images OWNER TO reconnoiter; |
---|
126 |
|
---|
127 |
-- |
---|
128 |
-- Name: pga_layout; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace: |
---|
129 |
-- |
---|
130 |
|
---|
131 |
CREATE TABLE pga_layout ( |
---|
132 |
tablename character varying(64) NOT NULL, |
---|
133 |
nrcols smallint, |
---|
134 |
colnames text, |
---|
135 |
colwidth text |
---|
136 |
); |
---|
137 |
|
---|
138 |
|
---|
139 |
ALTER TABLE public.pga_layout OWNER TO reconnoiter; |
---|
140 |
|
---|
141 |
-- |
---|
142 |
-- Name: pga_queries; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace: |
---|
143 |
-- |
---|
144 |
|
---|
145 |
CREATE TABLE pga_queries ( |
---|
146 |
queryname character varying(64) NOT NULL, |
---|
147 |
querytype character(1), |
---|
148 |
querycommand text, |
---|
149 |
querytables text, |
---|
150 |
querylinks text, |
---|
151 |
queryresults text, |
---|
152 |
querycomments text |
---|
153 |
); |
---|
154 |
|
---|
155 |
|
---|
156 |
ALTER TABLE public.pga_queries OWNER TO reconnoiter; |
---|
157 |
|
---|
158 |
-- |
---|
159 |
-- Name: pga_reports; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace: |
---|
160 |
-- |
---|
161 |
|
---|
162 |
CREATE TABLE pga_reports ( |
---|
163 |
reportname character varying(64) NOT NULL, |
---|
164 |
reportsource text, |
---|
165 |
reportbody text, |
---|
166 |
reportprocs text, |
---|
167 |
reportoptions text |
---|
168 |
); |
---|
169 |
|
---|
170 |
|
---|
171 |
ALTER TABLE public.pga_reports OWNER TO reconnoiter; |
---|
172 |
|
---|
173 |
-- |
---|
174 |
-- Name: pga_scripts; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace: |
---|
175 |
-- |
---|
176 |
|
---|
177 |
CREATE TABLE pga_scripts ( |
---|
178 |
scriptname character varying(64) NOT NULL, |
---|
179 |
scriptsource text |
---|
180 |
); |
---|
181 |
|
---|
182 |
|
---|
183 |
ALTER TABLE public.pga_scripts OWNER TO reconnoiter; |
---|
184 |
|
---|
185 |
SET default_with_oids = false; |
---|
186 |
|
---|
187 |
-- |
---|
188 |
-- Name: varnish_huh; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace: |
---|
189 |
-- |
---|
190 |
|
---|
191 |
CREATE TABLE varnish_huh ( |
---|
192 |
sid integer, |
---|
193 |
whence timestamp with time zone, |
---|
194 |
name text, |
---|
195 |
value numeric |
---|
196 |
); |
---|
197 |
|
---|
198 |
|
---|
199 |
ALTER TABLE public.varnish_huh OWNER TO reconnoiter; |
---|
200 |
|
---|
201 |
-- |
---|
202 |
-- Name: varnish_huh2; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace: |
---|
203 |
-- |
---|
204 |
|
---|
205 |
CREATE TABLE varnish_huh2 ( |
---|
206 |
sid integer, |
---|
207 |
whence timestamp with time zone, |
---|
208 |
name text, |
---|
209 |
value numeric |
---|
210 |
); |
---|
211 |
|
---|
212 |
|
---|
213 |
ALTER TABLE public.varnish_huh2 OWNER TO reconnoiter; |
---|
214 |
|
---|
215 |
SET search_path = stratcon, pg_catalog; |
---|
216 |
|
---|
217 |
-- |
---|
218 |
-- Name: current_metric_text; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
219 |
-- |
---|
220 |
|
---|
221 |
CREATE TABLE current_metric_text ( |
---|
222 |
sid integer NOT NULL, |
---|
223 |
whence timestamp with time zone NOT NULL, |
---|
224 |
name text NOT NULL, |
---|
225 |
value text |
---|
226 |
); |
---|
227 |
|
---|
228 |
|
---|
229 |
ALTER TABLE stratcon.current_metric_text OWNER TO reconnoiter; |
---|
230 |
|
---|
231 |
-- |
---|
232 |
-- Name: current_node_config; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
233 |
-- |
---|
234 |
|
---|
235 |
CREATE TABLE current_node_config ( |
---|
236 |
remote_address inet NOT NULL, |
---|
237 |
node_type text NOT NULL, |
---|
238 |
whence timestamp with time zone NOT NULL, |
---|
239 |
config xml NOT NULL |
---|
240 |
); |
---|
241 |
|
---|
242 |
|
---|
243 |
ALTER TABLE stratcon.current_node_config OWNER TO reconnoiter; |
---|
244 |
|
---|
245 |
-- |
---|
246 |
-- Name: current_node_config_changelog; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
247 |
-- |
---|
248 |
|
---|
249 |
CREATE TABLE current_node_config_changelog ( |
---|
250 |
remote_address inet NOT NULL, |
---|
251 |
node_type text NOT NULL, |
---|
252 |
whence timestamp with time zone NOT NULL, |
---|
253 |
config xml NOT NULL |
---|
254 |
); |
---|
255 |
|
---|
256 |
|
---|
257 |
ALTER TABLE stratcon.current_node_config_changelog OWNER TO reconnoiter; |
---|
258 |
|
---|
259 |
-- |
---|
260 |
-- Name: loading_dock_check_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
261 |
-- |
---|
262 |
|
---|
263 |
CREATE TABLE loading_dock_check_s ( |
---|
264 |
sid integer NOT NULL, |
---|
265 |
remote_address inet, |
---|
266 |
whence timestamp with time zone NOT NULL, |
---|
267 |
id uuid NOT NULL, |
---|
268 |
target text NOT NULL, |
---|
269 |
module text NOT NULL, |
---|
270 |
name text NOT NULL |
---|
271 |
); |
---|
272 |
|
---|
273 |
|
---|
274 |
ALTER TABLE stratcon.loading_dock_check_s OWNER TO reconnoiter; |
---|
275 |
|
---|
276 |
-- |
---|
277 |
-- Name: loading_dock_metric_numeric_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
278 |
-- |
---|
279 |
|
---|
280 |
CREATE TABLE loading_dock_metric_numeric_s ( |
---|
281 |
sid integer NOT NULL, |
---|
282 |
whence timestamp with time zone NOT NULL, |
---|
283 |
name text NOT NULL, |
---|
284 |
value numeric |
---|
285 |
); |
---|
286 |
|
---|
287 |
|
---|
288 |
ALTER TABLE stratcon.loading_dock_metric_numeric_s OWNER TO reconnoiter; |
---|
289 |
|
---|
290 |
-- |
---|
291 |
-- Name: loading_dock_metric_text_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
292 |
-- |
---|
293 |
|
---|
294 |
CREATE TABLE loading_dock_metric_text_s ( |
---|
295 |
sid integer NOT NULL, |
---|
296 |
whence timestamp with time zone NOT NULL, |
---|
297 |
name text NOT NULL, |
---|
298 |
value text |
---|
299 |
); |
---|
300 |
|
---|
301 |
|
---|
302 |
ALTER TABLE stratcon.loading_dock_metric_text_s OWNER TO reconnoiter; |
---|
303 |
|
---|
304 |
-- |
---|
305 |
-- Name: loading_dock_metric_text_s_change_log; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
306 |
-- |
---|
307 |
|
---|
308 |
CREATE TABLE loading_dock_metric_text_s_change_log ( |
---|
309 |
sid integer NOT NULL, |
---|
310 |
whence timestamp with time zone NOT NULL, |
---|
311 |
name text NOT NULL, |
---|
312 |
value text |
---|
313 |
); |
---|
314 |
|
---|
315 |
|
---|
316 |
ALTER TABLE stratcon.loading_dock_metric_text_s_change_log OWNER TO reconnoiter; |
---|
317 |
|
---|
318 |
-- |
---|
319 |
-- Name: loading_dock_status_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
320 |
-- |
---|
321 |
|
---|
322 |
CREATE TABLE loading_dock_status_s ( |
---|
323 |
sid integer NOT NULL, |
---|
324 |
whence timestamp with time zone NOT NULL, |
---|
325 |
state character(1) NOT NULL, |
---|
326 |
availability character(1) NOT NULL, |
---|
327 |
duration integer NOT NULL, |
---|
328 |
status text |
---|
329 |
); |
---|
330 |
|
---|
331 |
|
---|
332 |
ALTER TABLE stratcon.loading_dock_status_s OWNER TO reconnoiter; |
---|
333 |
|
---|
334 |
-- |
---|
335 |
-- Name: loading_dock_status_s_change_log; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
336 |
-- |
---|
337 |
|
---|
338 |
CREATE TABLE loading_dock_status_s_change_log ( |
---|
339 |
sid integer NOT NULL, |
---|
340 |
whence timestamp with time zone NOT NULL, |
---|
341 |
state character(1) NOT NULL, |
---|
342 |
availability character(1) NOT NULL, |
---|
343 |
duration integer NOT NULL, |
---|
344 |
status text |
---|
345 |
); |
---|
346 |
|
---|
347 |
|
---|
348 |
ALTER TABLE stratcon.loading_dock_status_s_change_log OWNER TO reconnoiter; |
---|
349 |
|
---|
350 |
-- |
---|
351 |
-- Name: log_whence_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
352 |
-- |
---|
353 |
|
---|
354 |
CREATE TABLE log_whence_s ( |
---|
355 |
whence timestamp with time zone NOT NULL, |
---|
356 |
"interval" character varying(20) NOT NULL |
---|
357 |
); |
---|
358 |
|
---|
359 |
|
---|
360 |
ALTER TABLE stratcon.log_whence_s OWNER TO reconnoiter; |
---|
361 |
|
---|
362 |
-- |
---|
363 |
-- Name: map_uuid_to_sid; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
364 |
-- |
---|
365 |
|
---|
366 |
CREATE TABLE map_uuid_to_sid ( |
---|
367 |
id uuid NOT NULL, |
---|
368 |
sid integer NOT NULL |
---|
369 |
); |
---|
370 |
|
---|
371 |
|
---|
372 |
ALTER TABLE stratcon.map_uuid_to_sid OWNER TO reconnoiter; |
---|
373 |
|
---|
374 |
-- |
---|
375 |
-- Name: metric_name_summary; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
376 |
-- |
---|
377 |
|
---|
378 |
CREATE TABLE metric_name_summary ( |
---|
379 |
sid integer NOT NULL, |
---|
380 |
metric_name text NOT NULL, |
---|
381 |
metric_type character varying(22), |
---|
382 |
active boolean DEFAULT true |
---|
383 |
); |
---|
384 |
|
---|
385 |
|
---|
386 |
ALTER TABLE stratcon.metric_name_summary OWNER TO reconnoiter; |
---|
387 |
|
---|
388 |
-- |
---|
389 |
-- Name: mv_loading_dock_check_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
390 |
-- |
---|
391 |
|
---|
392 |
CREATE TABLE mv_loading_dock_check_s ( |
---|
393 |
sid integer NOT NULL, |
---|
394 |
remote_address inet, |
---|
395 |
whence timestamp with time zone NOT NULL, |
---|
396 |
id uuid NOT NULL, |
---|
397 |
target text NOT NULL, |
---|
398 |
module text NOT NULL, |
---|
399 |
name text NOT NULL |
---|
400 |
); |
---|
401 |
|
---|
402 |
|
---|
403 |
ALTER TABLE stratcon.mv_loading_dock_check_s OWNER TO reconnoiter; |
---|
404 |
|
---|
405 |
-- |
---|
406 |
-- Name: rollup_matrix_numeric_12hours; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
407 |
-- |
---|
408 |
|
---|
409 |
CREATE TABLE rollup_matrix_numeric_12hours ( |
---|
410 |
sid integer NOT NULL, |
---|
411 |
name text NOT NULL, |
---|
412 |
rollup_time timestamp with time zone NOT NULL, |
---|
413 |
count_rows integer, |
---|
414 |
avg_value numeric |
---|
415 |
); |
---|
416 |
|
---|
417 |
|
---|
418 |
ALTER TABLE stratcon.rollup_matrix_numeric_12hours OWNER TO reconnoiter; |
---|
419 |
|
---|
420 |
-- |
---|
421 |
-- Name: rollup_matrix_numeric_20m; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
422 |
-- |
---|
423 |
|
---|
424 |
CREATE TABLE rollup_matrix_numeric_20m ( |
---|
425 |
sid integer NOT NULL, |
---|
426 |
name text NOT NULL, |
---|
427 |
rollup_time timestamp with time zone NOT NULL, |
---|
428 |
count_rows integer, |
---|
429 |
avg_value numeric |
---|
430 |
); |
---|
431 |
|
---|
432 |
|
---|
433 |
ALTER TABLE stratcon.rollup_matrix_numeric_20m OWNER TO reconnoiter; |
---|
434 |
|
---|
435 |
-- |
---|
436 |
-- Name: rollup_matrix_numeric_5m; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
437 |
-- |
---|
438 |
|
---|
439 |
CREATE TABLE rollup_matrix_numeric_5m ( |
---|
440 |
sid integer NOT NULL, |
---|
441 |
name text NOT NULL, |
---|
442 |
rollup_time timestamp with time zone NOT NULL, |
---|
443 |
count_rows integer, |
---|
444 |
avg_value numeric |
---|
445 |
); |
---|
446 |
|
---|
447 |
|
---|
448 |
ALTER TABLE stratcon.rollup_matrix_numeric_5m OWNER TO reconnoiter; |
---|
449 |
|
---|
450 |
-- |
---|
451 |
-- Name: rollup_matrix_numeric_60m; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
452 |
-- |
---|
453 |
|
---|
454 |
CREATE TABLE rollup_matrix_numeric_60m ( |
---|
455 |
sid integer NOT NULL, |
---|
456 |
name text NOT NULL, |
---|
457 |
rollup_time timestamp with time zone NOT NULL, |
---|
458 |
count_rows integer, |
---|
459 |
avg_value numeric |
---|
460 |
); |
---|
461 |
|
---|
462 |
|
---|
463 |
ALTER TABLE stratcon.rollup_matrix_numeric_60m OWNER TO reconnoiter; |
---|
464 |
|
---|
465 |
-- |
---|
466 |
-- Name: rollup_matrix_numeric_6hours; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
467 |
-- |
---|
468 |
|
---|
469 |
CREATE TABLE rollup_matrix_numeric_6hours ( |
---|
470 |
sid integer NOT NULL, |
---|
471 |
name text NOT NULL, |
---|
472 |
rollup_time timestamp with time zone NOT NULL, |
---|
473 |
count_rows integer, |
---|
474 |
avg_value numeric |
---|
475 |
); |
---|
476 |
|
---|
477 |
|
---|
478 |
ALTER TABLE stratcon.rollup_matrix_numeric_6hours OWNER TO reconnoiter; |
---|
479 |
|
---|
480 |
-- |
---|
481 |
-- Name: rollup_runner; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
482 |
-- |
---|
483 |
|
---|
484 |
CREATE TABLE rollup_runner ( |
---|
485 |
rollup_table character varying(100), |
---|
486 |
runner character varying(22) |
---|
487 |
); |
---|
488 |
|
---|
489 |
|
---|
490 |
ALTER TABLE stratcon.rollup_runner OWNER TO reconnoiter; |
---|
491 |
|
---|
492 |
SET search_path = prism, pg_catalog; |
---|
493 |
|
---|
494 |
-- |
---|
495 |
-- Name: check_name_saved_graphs(); Type: FUNCTION; Schema: prism; Owner: reconnoiter |
---|
496 |
-- |
---|
497 |
|
---|
498 |
CREATE FUNCTION check_name_saved_graphs() RETURNS trigger |
---|
499 |
AS $$ |
---|
500 |
DECLARE |
---|
501 |
BEGIN |
---|
502 |
IF NEW.saved IS true AND NEW.title IS null THEN |
---|
503 |
RAISE EXCEPTION 'You must name graph to save'; |
---|
504 |
END IF; |
---|
505 |
RETURN NEW; |
---|
506 |
END |
---|
507 |
$$ |
---|
508 |
LANGUAGE plpgsql; |
---|
509 |
|
---|
510 |
|
---|
511 |
ALTER FUNCTION prism.check_name_saved_graphs() OWNER TO reconnoiter; |
---|
512 |
|
---|
513 |
SET search_path = public, pg_catalog; |
---|
514 |
|
---|
515 |
-- |
---|
516 |
-- Name: date_hour(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: reconnoiter |
---|
517 |
-- |
---|
518 |
|
---|
519 |
CREATE FUNCTION date_hour(timestamp with time zone) RETURNS timestamp with time zone |
---|
520 |
AS $_$ |
---|
521 |
SELECT date_trunc('hour',$1); |
---|
522 |
$_$ |
---|
523 |
LANGUAGE sql IMMUTABLE STRICT; |
---|
524 |
|
---|
525 |
|
---|
526 |
ALTER FUNCTION public.date_hour(timestamp with time zone) OWNER TO reconnoiter; |
---|
527 |
|
---|
528 |
SET search_path = stratcon, pg_catalog; |
---|
529 |
|
---|
530 |
-- |
---|
531 |
-- Name: choose_window(timestamp without time zone, timestamp without time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
532 |
-- |
---|
533 |
|
---|
534 |
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 |
---|
535 |
AS $$ |
---|
536 |
declare |
---|
537 |
window record; |
---|
538 |
begin |
---|
539 |
-- Figure out which table we should be looking in |
---|
540 |
for window in |
---|
541 |
select atablename, aperiod, anperiods |
---|
542 |
from (select aperiod, iv/isec as anperiods, atablename, |
---|
543 |
abs(case when iv/isec - in_hopeful_nperiods < 0 |
---|
544 |
then 10 * (in_hopeful_nperiods - iv/isec) |
---|
545 |
else iv/isec - in_hopeful_nperiods |
---|
546 |
end) as badness |
---|
547 |
from (select extract('epoch' from in_end_time) - |
---|
548 |
extract('epoch' from in_start_time) as iv |
---|
549 |
) i, |
---|
550 |
( select 5*60 as isec, '5 minutes'::interval as aperiod, |
---|
551 |
'rollup_matrix_numeric_5m' as atablename |
---|
552 |
union all |
---|
553 |
select 20*60 as isec, '20 minutes'::interval as aperiod, |
---|
554 |
'rollup_matrix_numeric_20m' as atablename |
---|
555 |
union all |
---|
556 |
select 60*60 as isec, '1 hour'::interval as aperiod, |
---|
557 |
'rollup_matrix_numeric_60m' as atablename |
---|
558 |
union all |
---|
559 |
select 6*60*60 as isec, '6 hours'::interval as aaperiod, |
---|
560 |
'rollup_matrix_numeric_6hours' as atablename |
---|
561 |
union all |
---|
562 |
select 12*60*60 as isec, '12 hours'::interval as aperiod, |
---|
563 |
'rollup_matrix_numeric_12hours' as atablename |
---|
564 |
) ivs |
---|
565 |
) b |
---|
566 |
order by badness asc |
---|
567 |
limit 1 |
---|
568 |
loop |
---|
569 |
tablename := window.atablename; |
---|
570 |
period := window.aperiod; |
---|
571 |
nperiods := window.anperiods; |
---|
572 |
return next; |
---|
573 |
end loop; |
---|
574 |
return; |
---|
575 |
end |
---|
576 |
$$ |
---|
577 |
LANGUAGE plpgsql; |
---|
578 |
|
---|
579 |
|
---|
580 |
ALTER FUNCTION stratcon.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) OWNER TO reconnoiter; |
---|
581 |
|
---|
582 |
-- |
---|
583 |
-- Name: choose_window(timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
584 |
-- |
---|
585 |
|
---|
586 |
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 |
---|
587 |
AS $$ |
---|
588 |
declare |
---|
589 |
window record; |
---|
590 |
begin |
---|
591 |
-- Figure out which table we should be looking in |
---|
592 |
for window in |
---|
593 |
select atablename, aperiod, anperiods |
---|
594 |
from (select aperiod, round(iv/isec) ::integer as anperiods, atablename, |
---|
595 |
abs(case when iv/isec - in_hopeful_nperiods < 0 |
---|
596 |
then 10 * (in_hopeful_nperiods - iv/isec) |
---|
597 |
else iv/isec - in_hopeful_nperiods |
---|
598 |
end) as badness |
---|
599 |
from (select extract('epoch' from in_end_time) - |
---|
600 |
extract('epoch' from in_start_time) as iv |
---|
601 |
) i, |
---|
602 |
( select 5*60 as isec, '5 minutes'::interval as aperiod, |
---|
603 |
'rollup_matrix_numeric_5m' as atablename |
---|
604 |
union all |
---|
605 |
select 20*60 as isec, '20 minutes'::interval as aperiod, |
---|
606 |
'rollup_matrix_numeric_20m' as atablename |
---|
607 |
union all |
---|
608 |
select 60*60 as isec, '1 hour'::interval as aperiod, |
---|
609 |
'rollup_matrix_numeric_60m' as atablename |
---|
610 |
union all |
---|
611 |
select 6*60*60 as isec, '6 hours'::interval as aaperiod, |
---|
612 |
'rollup_matrix_numeric_6hours' as atablename |
---|
613 |
union all |
---|
614 |
select 12*60*60 as isec, '12 hours'::interval as aperiod, |
---|
615 |
'rollup_matrix_numeric_12hours' as atablename |
---|
616 |
) ivs |
---|
617 |
) b |
---|
618 |
order by badness asc |
---|
619 |
limit 1 |
---|
620 |
loop |
---|
621 |
tablename := window.atablename; |
---|
622 |
period := window.aperiod; |
---|
623 |
nperiods := window.anperiods; |
---|
624 |
return next; |
---|
625 |
end loop; |
---|
626 |
return; |
---|
627 |
end |
---|
628 |
$$ |
---|
629 |
LANGUAGE plpgsql; |
---|
630 |
|
---|
631 |
|
---|
632 |
ALTER FUNCTION stratcon.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) OWNER TO reconnoiter; |
---|
633 |
|
---|
634 |
-- |
---|
635 |
-- Name: date_hour(timestamp with time zone); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
636 |
-- |
---|
637 |
|
---|
638 |
CREATE FUNCTION date_hour(timestamp with time zone) RETURNS timestamp with time zone |
---|
639 |
AS $_$ |
---|
640 |
SELECT date_trunc('hour',$1); |
---|
641 |
$_$ |
---|
642 |
LANGUAGE sql IMMUTABLE STRICT; |
---|
643 |
|
---|
644 |
|
---|
645 |
ALTER FUNCTION stratcon.date_hour(timestamp with time zone) OWNER TO reconnoiter; |
---|
646 |
|
---|
647 |
-- |
---|
648 |
-- Name: fetch_dataset(uuid, text, timestamp with time zone, timestamp with time zone, integer, boolean); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
649 |
-- |
---|
650 |
|
---|
651 |
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 |
---|
652 |
AS $$ |
---|
653 |
declare |
---|
654 |
v_sid int; |
---|
655 |
v_record stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
656 |
begin |
---|
657 |
select sid into v_sid from stratcon.map_uuid_to_sid where id = in_uuid; |
---|
658 |
if not found then |
---|
659 |
return; |
---|
660 |
end if; |
---|
661 |
|
---|
662 |
for v_record in select sid, name, rollup_time, count_rows, avg_value from stratcon.fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive) loop |
---|
663 |
return next v_record; |
---|
664 |
end loop; |
---|
665 |
|
---|
666 |
-- return query select sid, name, rollup_time, count_rows, avg_value from stratcon.fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive); |
---|
667 |
return; |
---|
668 |
end |
---|
669 |
$$ |
---|
670 |
LANGUAGE plpgsql; |
---|
671 |
|
---|
672 |
|
---|
673 |
ALTER FUNCTION stratcon.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) OWNER TO reconnoiter; |
---|
674 |
|
---|
675 |
-- |
---|
676 |
-- Name: fetch_dataset(integer, text, timestamp with time zone, timestamp with time zone, integer, boolean); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
677 |
-- |
---|
678 |
|
---|
679 |
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 |
---|
680 |
AS $$ |
---|
681 |
declare |
---|
682 |
v_sql text; |
---|
683 |
v_sid int; |
---|
684 |
v_target record; |
---|
685 |
v_interval numeric; |
---|
686 |
v_start_adj timestamptz; |
---|
687 |
v_end_adj timestamptz; |
---|
688 |
v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
689 |
v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
690 |
v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
691 |
begin |
---|
692 |
|
---|
693 |
-- Map out uuid to an sid. |
---|
694 |
v_sid := in_sid; |
---|
695 |
|
---|
696 |
select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods); |
---|
697 |
|
---|
698 |
if not found then |
---|
699 |
raise exception 'no target table'; |
---|
700 |
return; |
---|
701 |
end if; |
---|
702 |
|
---|
703 |
select 'epoch'::timestamp + |
---|
704 |
((floor(extract('epoch' from in_start_time) / |
---|
705 |
extract('epoch' from v_target.period)) * |
---|
706 |
extract('epoch' from v_target.period)) || ' seconds') ::interval |
---|
707 |
into v_start_adj; |
---|
708 |
|
---|
709 |
select 'epoch'::timestamp + |
---|
710 |
((floor(extract('epoch' from in_end_time) / |
---|
711 |
extract('epoch' from v_target.period)) * |
---|
712 |
extract('epoch' from v_target.period)) || ' seconds') ::interval |
---|
713 |
into v_end_adj; |
---|
714 |
|
---|
715 |
v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' || |
---|
716 |
's.rollup_time, d.count_rows, d.avg_value ' || |
---|
717 |
' from ' || |
---|
718 |
'(select ' || quote_literal(v_start_adj) || '::timestamp' || |
---|
719 |
' + t * ' || quote_literal(v_target.period) || '::interval' || |
---|
720 |
' as rollup_time' || |
---|
721 |
' from generate_series(1,' || v_target.nperiods || ') t) s ' || |
---|
722 |
'left join ' || |
---|
723 |
'(select * from stratcon.' || v_target.tablename || |
---|
724 |
' where sid = ' || v_sid || |
---|
725 |
' and name = ' || quote_literal(in_name) || |
---|
726 |
' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' || |
---|
727 |
' and ' || quote_literal(v_end_adj) || '::timestamp) d' || |
---|
728 |
' using(rollup_time)'; |
---|
729 |
|
---|
730 |
for v_rollup_row in execute v_sql loop |
---|
731 |
if derive is true then |
---|
732 |
v_r_rollup_row := v_rollup_row; |
---|
733 |
if v_l_rollup_row.count_rows is not null and |
---|
734 |
v_rollup_row.count_rows is not null then |
---|
735 |
v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time); |
---|
736 |
v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2; |
---|
737 |
v_r_rollup_row.avg_value := |
---|
738 |
(v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval; |
---|
739 |
else |
---|
740 |
v_r_rollup_row.count_rows = NULL; |
---|
741 |
v_r_rollup_row.avg_value = NULL; |
---|
742 |
|
---|
743 |
end if; |
---|
744 |
else |
---|
745 |
v_r_rollup_row := v_rollup_row; |
---|
746 |
end if; |
---|
747 |
return next v_r_rollup_row; |
---|
748 |
v_l_rollup_row := v_rollup_row; |
---|
749 |
end loop; |
---|
750 |
return; |
---|
751 |
end |
---|
752 |
$$ |
---|
753 |
LANGUAGE plpgsql; |
---|
754 |
|
---|
755 |
|
---|
756 |
ALTER FUNCTION stratcon.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) OWNER TO reconnoiter; |
---|
757 |
|
---|
758 |
-- |
---|
759 |
-- Name: fetch_varset(uuid, text, timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
760 |
-- |
---|
761 |
|
---|
762 |
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 |
---|
763 |
AS $$ |
---|
764 |
declare |
---|
765 |
v_sid int; |
---|
766 |
begin |
---|
767 |
-- Map out uuid to an sid. |
---|
768 |
select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check; |
---|
769 |
if not found then |
---|
770 |
return; |
---|
771 |
end if; |
---|
772 |
|
---|
773 |
return query select * from stratcon.fetch_varset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods); |
---|
774 |
end |
---|
775 |
$$ |
---|
776 |
LANGUAGE plpgsql; |
---|
777 |
|
---|
778 |
|
---|
779 |
ALTER FUNCTION stratcon.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) OWNER TO reconnoiter; |
---|
780 |
|
---|
781 |
-- |
---|
782 |
-- Name: fetch_varset(integer, text, timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
783 |
-- |
---|
784 |
|
---|
785 |
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 |
---|
786 |
AS $$ |
---|
787 |
declare |
---|
788 |
v_sid int; |
---|
789 |
v_target record; |
---|
790 |
v_start_adj timestamptz; |
---|
791 |
v_start_text text; |
---|
792 |
v_next_text text; |
---|
793 |
v_end_adj timestamptz; |
---|
794 |
v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype; |
---|
795 |
begin |
---|
796 |
-- Map out uuid to an sid. |
---|
797 |
v_sid := in_sid; |
---|
798 |
|
---|
799 |
select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods); |
---|
800 |
|
---|
801 |
select 'epoch'::timestamp + |
---|
802 |
((floor(extract('epoch' from in_start_time) / |
---|
803 |
extract('epoch' from v_target.period)) * |
---|
804 |
extract('epoch' from v_target.period)) || ' seconds') ::interval |
---|
805 |
into v_start_adj; |
---|
806 |
|
---|
807 |
select 'epoch'::timestamp + |
---|
808 |
((floor(extract('epoch' from in_end_time) / |
---|
809 |
extract('epoch' from v_target.period)) * |
---|
810 |
extract('epoch' from v_target.period)) || ' seconds') ::interval |
---|
811 |
into v_end_adj; |
---|
812 |
|
---|
813 |
for v_change_row in |
---|
814 |
select sid, 'epoch'::timestamp + |
---|
815 |
((floor(extract('epoch' from whence) / |
---|
816 |
extract('epoch' from v_target.period)) * |
---|
817 |
extract('epoch' from v_target.period)) || ' seconds') ::interval as whence, |
---|
818 |
name, value |
---|
819 |
from stratcon.loading_dock_metric_text_s_change_log |
---|
820 |
where sid = v_sid |
---|
821 |
and name = in_name |
---|
822 |
and whence <= v_start_adj |
---|
823 |
order by 'epoch'::timestamp + |
---|
824 |
((floor(extract('epoch' from whence) / |
---|
825 |
extract('epoch' from v_target.period)) * |
---|
826 |
extract('epoch' from v_target.period)) || ' seconds') ::interval desc |
---|
827 |
limit 1 |
---|
828 |
loop |
---|
829 |
v_start_text := coalesce(v_change_row.value, '[unset]'); |
---|
830 |
end loop; |
---|
831 |
|
---|
832 |
for v_change_row in |
---|
833 |
select v_sid as sid, whence, in_name as name, value from |
---|
834 |
-- (select v_start_adj::timestamp + t * v_target.period::interval as whence |
---|
835 |
-- from generate_series(1, v_target.nperiods) t) s |
---|
836 |
-- left join |
---|
837 |
(select 'epoch'::timestamp + |
---|
838 |
((floor(extract('epoch' from whence) / |
---|
839 |
extract('epoch' from v_target.period)) * |
---|
840 |
extract('epoch' from v_target.period)) || ' seconds') ::interval as whence, |
---|
841 |
coalesce(value, '[unset]') as value |
---|
842 |
from stratcon.loading_dock_metric_text_s_change_log |
---|
843 |
where sid = v_sid |
---|
844 |
and name = in_name |
---|
845 |
and whence > v_start_adj |
---|
846 |
and whence <= v_end_adj) d |
---|
847 |
-- using (whence) |
---|
848 |
order by whence asc |
---|
849 |
loop |
---|
850 |
v_next_text := v_change_row.value; |
---|
851 |
if v_change_row.value is not null and |
---|
852 |
v_start_text != v_change_row.value then |
---|
853 |
v_change_row.value := coalesce(v_start_text, '[unset]') || ' -> ' || coalesce(v_change_row.value, '[unset]'); |
---|
854 |
else |
---|
855 |
v_change_row.value := v_start_text; |
---|
856 |
end if; |
---|
857 |
if v_next_text is not null then |
---|
858 |
v_start_text := v_next_text; |
---|
859 |
end if; |
---|
860 |
return next v_change_row; |
---|
861 |
end loop; |
---|
862 |
|
---|
863 |
return; |
---|
864 |
end |
---|
865 |
$$ |
---|
866 |
LANGUAGE plpgsql; |
---|
867 |
|
---|
868 |
|
---|
869 |
ALTER FUNCTION stratcon.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) OWNER TO reconnoiter; |
---|
870 |
|
---|
871 |
-- |
---|
872 |
-- Name: generate_sid_from_id(uuid); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
873 |
-- |
---|
874 |
|
---|
875 |
CREATE FUNCTION generate_sid_from_id(v_in_id uuid) RETURNS integer |
---|
876 |
AS $$ |
---|
877 |
DECLARE |
---|
878 |
v_ex_sid integer; |
---|
879 |
v_new_sid integer; |
---|
880 |
|
---|
881 |
BEGIN |
---|
882 |
|
---|
883 |
SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id |
---|
884 |
INTO v_ex_sid; |
---|
885 |
|
---|
886 |
IF NOT FOUND THEN |
---|
887 |
SELECT nextval('stratcon.seq_sid') |
---|
888 |
INTO v_new_sid; |
---|
889 |
|
---|
890 |
INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid); |
---|
891 |
|
---|
892 |
|
---|
893 |
RETURN v_new_sid; |
---|
894 |
ELSE |
---|
895 |
RETURN v_ex_sid; |
---|
896 |
END IF; |
---|
897 |
|
---|
898 |
END |
---|
899 |
$$ |
---|
900 |
LANGUAGE plpgsql; |
---|
901 |
|
---|
902 |
|
---|
903 |
ALTER FUNCTION stratcon.generate_sid_from_id(v_in_id uuid) OWNER TO reconnoiter; |
---|
904 |
|
---|
905 |
-- |
---|
906 |
-- Name: loading_dock_metric_numeric_s_whence_log(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
907 |
-- |
---|
908 |
|
---|
909 |
CREATE FUNCTION loading_dock_metric_numeric_s_whence_log() RETURNS trigger |
---|
910 |
AS $$ |
---|
911 |
DECLARE |
---|
912 |
v_whence timestamptz; |
---|
913 |
v_whence_5 timestamptz; |
---|
914 |
v_sid integer; |
---|
915 |
v_name text; |
---|
916 |
BEGIN |
---|
917 |
IF TG_OP = 'INSERT' THEN |
---|
918 |
|
---|
919 |
v_whence_5:=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval; |
---|
920 |
|
---|
921 |
SELECT whence FROM stratcon.log_whence_s WHERE whence=v_whence_5 and interval='5 minutes' |
---|
922 |
INTO v_whence; |
---|
923 |
|
---|
924 |
IF NOT FOUND THEN |
---|
925 |
BEGIN |
---|
926 |
INSERT INTO stratcon.log_whence_s VALUES(v_whence_5,'5 minutes'); |
---|
927 |
EXCEPTION |
---|
928 |
WHEN UNIQUE_VIOLATION THEN |
---|
929 |
-- do nothing |
---|
930 |
END; |
---|
931 |
END IF; |
---|
932 |
|
---|
933 |
SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid and metric_name=NEW.name |
---|
934 |
INTO v_sid,v_name; |
---|
935 |
IF NOT FOUND THEN |
---|
936 |
INSERT INTO stratcon.metric_name_summary VALUES(NEW.sid,NEW.name,'numeric'); |
---|
937 |
END IF; |
---|
938 |
|
---|
939 |
END IF; |
---|
940 |
RETURN NULL; |
---|
941 |
END |
---|
942 |
$$ |
---|
943 |
LANGUAGE plpgsql; |
---|
944 |
|
---|
945 |
|
---|
946 |
ALTER FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() OWNER TO reconnoiter; |
---|
947 |
|
---|
948 |
-- |
---|
949 |
-- Name: loading_dock_metric_text_s_change_log(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
950 |
-- |
---|
951 |
|
---|
952 |
CREATE FUNCTION loading_dock_metric_text_s_change_log() RETURNS trigger |
---|
953 |
AS $$ |
---|
954 |
DECLARE |
---|
955 |
v_oldvalue text; |
---|
956 |
v_sid integer; |
---|
957 |
v_name text; |
---|
958 |
v_value text; |
---|
959 |
v_whence timestamptz; |
---|
960 |
v_old_whence timestamptz; |
---|
961 |
v_old_name text; |
---|
962 |
v_old_sid integer; |
---|
963 |
v_old_value text; |
---|
964 |
v_max_whence timestamptz; |
---|
965 |
BEGIN |
---|
966 |
|
---|
967 |
IF TG_OP = 'INSERT' THEN |
---|
968 |
|
---|
969 |
SELECT value FROM stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name |
---|
970 |
AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log |
---|
971 |
WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name ) |
---|
972 |
INTO v_oldvalue; |
---|
973 |
|
---|
974 |
IF v_oldvalue IS DISTINCT FROM NEW.value THEN |
---|
975 |
|
---|
976 |
INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value) |
---|
977 |
VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value); |
---|
978 |
DELETE FROM stratcon.current_metric_text |
---|
979 |
WHERE sid = NEW.sid and name = NEW.name; |
---|
980 |
INSERT INTO stratcon.current_metric_text (sid,whence,name,value) |
---|
981 |
VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value); |
---|
982 |
END IF; |
---|
983 |
|
---|
984 |
|
---|
985 |
SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid and metric_name=NEW.name |
---|
986 |
INTO v_sid,v_name; |
---|
987 |
IF NOT FOUND THEN |
---|
988 |
INSERT INTO stratcon.metric_name_summary(sid,metric_name,metric_type) VALUES(NEW.sid,NEW.name,'text'); |
---|
989 |
END IF; |
---|
990 |
|
---|
991 |
ELSE |
---|
992 |
RAISE EXCEPTION 'something wrong with stratcon.loading_dock_metric_text_s_change_log '; |
---|
993 |
END IF; |
---|
994 |
|
---|
995 |
RETURN NULL; |
---|
996 |
|
---|
997 |
END |
---|
998 |
$$ |
---|
999 |
LANGUAGE plpgsql; |
---|
1000 |
|
---|
1001 |
|
---|
1002 |
ALTER FUNCTION stratcon.loading_dock_metric_text_s_change_log() OWNER TO reconnoiter; |
---|
1003 |
|
---|
1004 |
-- |
---|
1005 |
-- Name: loading_dock_status_s_change_log(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
1006 |
-- |
---|
1007 |
|
---|
1008 |
CREATE FUNCTION loading_dock_status_s_change_log() RETURNS trigger |
---|
1009 |
AS $$ |
---|
1010 |
DECLARE |
---|
1011 |
v_state CHAR(1); |
---|
1012 |
v_avail CHAR(1); |
---|
1013 |
BEGIN |
---|
1014 |
|
---|
1015 |
IF TG_OP = 'INSERT' THEN |
---|
1016 |
SELECT state,availability FROM stratcon.loading_dock_status_s WHERE sid = NEW.sid |
---|
1017 |
AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_status_s_change_log |
---|
1018 |
WHERE SID=NEW.sid and WHENCE <> NEW.whence ) |
---|
1019 |
INTO v_state,v_avail; |
---|
1020 |
|
---|
1021 |
IF v_state IS DISTINCT FROM NEW.state OR v_avail IS DISTINCT FROM NEW.availability THEN |
---|
1022 |
|
---|
1023 |
INSERT INTO stratcon.loading_dock_status_s_change_log (sid,whence,state,availability,duration,status) |
---|
1024 |
VALUES (NEW.sid,NEW.whence,NEW.state,NEW.availability,NEW.duration,NEW.status); |
---|
1025 |
|
---|
1026 |
END IF; |
---|
1027 |
|
---|
1028 |
ELSE |
---|
1029 |
RAISE EXCEPTION 'Something wrong with stratcon.loading_dock_status_s_change_log'; |
---|
1030 |
END IF; |
---|
1031 |
|
---|
1032 |
RETURN NULL; |
---|
1033 |
|
---|
1034 |
END |
---|
1035 |
$$ |
---|
1036 |
LANGUAGE plpgsql; |
---|
1037 |
|
---|
1038 |
|
---|
1039 |
ALTER FUNCTION stratcon.loading_dock_status_s_change_log() OWNER TO reconnoiter; |
---|
1040 |
|
---|
1041 |
-- |
---|
1042 |
-- Name: mv_loading_dock_check_s(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
1043 |
-- |
---|
1044 |
|
---|
1045 |
CREATE FUNCTION mv_loading_dock_check_s() RETURNS trigger |
---|
1046 |
AS $$ |
---|
1047 |
DECLARE |
---|
1048 |
v_remote_address INET; |
---|
1049 |
v_target TEXT; |
---|
1050 |
v_module TEXT; |
---|
1051 |
v_name TEXT; |
---|
1052 |
BEGIN |
---|
1053 |
|
---|
1054 |
IF TG_OP = 'INSERT' THEN |
---|
1055 |
SELECT remote_address,target,module,name FROM stratcon.mv_loading_dock_check_s WHERE sid = NEW.sid AND id=NEW.id |
---|
1056 |
INTO v_remote_address,v_target,v_module,v_name; |
---|
1057 |
|
---|
1058 |
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 |
---|
1059 |
|
---|
1060 |
DELETE from stratcon.mv_loading_dock_check_s WHERE sid = NEW.sid AND id=NEW.id; |
---|
1061 |
|
---|
1062 |
INSERT INTO stratcon.mv_loading_dock_check_s (sid,remote_address,whence,id,target,module,name) |
---|
1063 |
VALUES (NEW.sid,NEW.remote_address,NEW.whence,NEW.id,NEW.target,NEW.module,NEW.name); |
---|
1064 |
|
---|
1065 |
END IF; |
---|
1066 |
|
---|
1067 |
ELSE |
---|
1068 |
RAISE EXCEPTION 'Something wrong with stratcon.mv_loading_dock_check_s'; |
---|
1069 |
END IF; |
---|
1070 |
|
---|
1071 |
RETURN NULL; |
---|
1072 |
|
---|
1073 |
END |
---|
1074 |
$$ |
---|
1075 |
LANGUAGE plpgsql; |
---|
1076 |
|
---|
1077 |
|
---|
1078 |
ALTER FUNCTION stratcon.mv_loading_dock_check_s() OWNER TO reconnoiter; |
---|
1079 |
|
---|
1080 |
-- |
---|
1081 |
-- Name: remove_metric(uuid, text, text); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
1082 |
-- |
---|
1083 |
|
---|
1084 |
CREATE FUNCTION remove_metric(in_uuid uuid, in_metric_name text, v_debug text, OUT v_out text) RETURNS text |
---|
1085 |
AS $$ |
---|
1086 |
DECLARE |
---|
1087 |
v_del_sid INT; |
---|
1088 |
v_del_metric_name TEXT; |
---|
1089 |
v_del_metric_type TEXT; |
---|
1090 |
deleted_t INT; |
---|
1091 |
deleted_tc INT; |
---|
1092 |
deleted_n INT; |
---|
1093 |
deleted_5 INT; |
---|
1094 |
deleted_20 INT; |
---|
1095 |
deleted_60 INT; |
---|
1096 |
deleted_6h INT; |
---|
1097 |
deleted_12h INT; |
---|
1098 |
deleted_sum INT; |
---|
1099 |
|
---|
1100 |
BEGIN |
---|
1101 |
SELECT s.sid,m.metric_name,m.metric_type |
---|
1102 |
FROM |
---|
1103 |
stratcon.map_uuid_to_sid s, |
---|
1104 |
stratcon.metric_name_summary m |
---|
1105 |
WHERE s.id=in_uuid |
---|
1106 |
and s.sid=m.sid |
---|
1107 |
and m.metric_name=in_metric_name |
---|
1108 |
INTO v_del_sid,v_del_metric_name,v_del_metric_type; |
---|
1109 |
IF NOT FOUND THEN |
---|
1110 |
IF v_debug = 'DEBUG' THEN |
---|
1111 |
RAISE NOTICE 'Given UUID can not map to SID,Metric Name: %,%',in_uuid,in_metric_name; |
---|
1112 |
END IF; |
---|
1113 |
v_out:='Please Supply Valid UUID,Metric Name Combination :'||in_uuid||','||in_metric_name; |
---|
1114 |
RETURN; |
---|
1115 |
END IF; |
---|
1116 |
IF v_debug = 'DEBUG' THEN |
---|
1117 |
RAISE NOTICE 'Delete In Progress For: %,%,%',v_del_sid,v_del_metric_name,v_del_metric_type; |
---|
1118 |
END IF; |
---|
1119 |
|
---|
1120 |
-- Check of Text or Numeric Type |
---|
1121 |
IF v_del_metric_type ='text' THEN |
---|
1122 |
-- Delete from Metrix Tex table |
---|
1123 |
DELETE FROM stratcon.loading_dock_metric_text_s WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
1124 |
GET DIAGNOSTICS deleted_t = ROW_COUNT; |
---|
1125 |
IF v_debug = 'DEBUG' THEN |
---|
1126 |
RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s : %',deleted; |
---|
1127 |
END IF; |
---|
1128 |
-- Delete from Metrix Change Log table |
---|
1129 |
DELETE FROM stratcon.loading_dock_metric_text_s_change_log WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
1130 |
GET DIAGNOSTICS deleted_tc = ROW_COUNT; |
---|
1131 |
IF v_debug = 'DEBUG' THEN |
---|
1132 |
RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s_change_log : %',deleted; |
---|
1133 |
END IF; |
---|
1134 |
ELSE |
---|
1135 |
-- Delete from Metrix Numeric table |
---|
1136 |
DELETE FROM stratcon.loading_dock_metric_numeric_s WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
1137 |
GET DIAGNOSTICS deleted_n = ROW_COUNT; |
---|
1138 |
IF v_debug = 'DEBUG' THEN |
---|
1139 |
RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_numeric_s : %',deleted; |
---|
1140 |
END IF; |
---|
1141 |
-- Delete from Rollup tables |
---|
1142 |
DELETE FROM stratcon.rollup_matrix_numeric_5m WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
1143 |
GET DIAGNOSTICS deleted_5 = ROW_COUNT; |
---|
1144 |
IF v_debug = 'DEBUG' THEN |
---|
1145 |
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_5m : %',deleted; |
---|
1146 |
END IF; |
---|
1147 |
DELETE FROM stratcon.rollup_matrix_numeric_20m WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
1148 |
GET DIAGNOSTICS deleted_20= ROW_COUNT; |
---|
1149 |
IF v_debug = 'DEBUG' THEN |
---|
1150 |
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_20m : %',deleted; |
---|
1151 |
END IF; |
---|
1152 |
DELETE FROM stratcon.rollup_matrix_numeric_60m WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
1153 |
GET DIAGNOSTICS deleted_60 = ROW_COUNT; |
---|
1154 |
IF v_debug = 'DEBUG' THEN |
---|
1155 |
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_60m : %',deleted; |
---|
1156 |
END IF; |
---|
1157 |
DELETE FROM stratcon.rollup_matrix_numeric_6hours WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
1158 |
GET DIAGNOSTICS deleted_6h = ROW_COUNT; |
---|
1159 |
IF v_debug = 'DEBUG' THEN |
---|
1160 |
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_6hours : %',deleted; |
---|
1161 |
END IF; |
---|
1162 |
DELETE FROM stratcon.rollup_matrix_numeric_12hours WHERE sid=v_del_sid AND name=v_del_metric_name; |
---|
1163 |
GET DIAGNOSTICS deleted_12h = ROW_COUNT; |
---|
1164 |
IF v_debug = 'DEBUG' THEN |
---|
1165 |
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_12hours : %',deleted; |
---|
1166 |
END IF; |
---|
1167 |
END IF; |
---|
1168 |
-- Delete from metrix summary table |
---|
1169 |
DELETE FROM stratcon.metrix_name_summary WHERE sid=v_del_sid AND metric_name=v_del_metric_name; |
---|
1170 |
GET DIAGNOSTICS deleted_sum= ROW_COUNT; |
---|
1171 |
IF v_debug = 'DEBUG' THEN |
---|
1172 |
RAISE NOTICE 'DELELTED ROWS FROM metric_name_summary : %',deleted; |
---|
1173 |
END IF; |
---|
1174 |
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; |
---|
1175 |
RETURN; |
---|
1176 |
EXCEPTION |
---|
1177 |
WHEN RAISE_EXCEPTION THEN |
---|
1178 |
RAISE EXCEPTION '%', SQLERRM; |
---|
1179 |
WHEN OTHERS THEN |
---|
1180 |
RAISE NOTICE '%', SQLERRM; |
---|
1181 |
END |
---|
1182 |
$$ |
---|
1183 |
LANGUAGE plpgsql; |
---|
1184 |
|
---|
1185 |
|
---|
1186 |
ALTER FUNCTION stratcon.remove_metric(in_uuid uuid, in_metric_name text, v_debug text, OUT v_out text) OWNER TO reconnoiter; |
---|
1187 |
|
---|
1188 |
-- |
---|
1189 |
-- Name: rollup_matrix_numeric_12hours(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
1190 |
-- |
---|
1191 |
|
---|
1192 |
CREATE FUNCTION rollup_matrix_numeric_12hours() RETURNS void |
---|
1193 |
AS $$ |
---|
1194 |
DECLARE |
---|
1195 |
rec stratcon.rollup_matrix_numeric_12hours%rowtype; |
---|
1196 |
v_sql TEXT; |
---|
1197 |
v_min_whence TIMESTAMPTZ; |
---|
1198 |
v_max_rollup_12 TIMESTAMPTZ; |
---|
1199 |
v_whence TIMESTAMPTZ; |
---|
1200 |
v_nrunning INT; |
---|
1201 |
v_self VARCHAR(22); |
---|
1202 |
whenceint RECORD; |
---|
1203 |
|
---|
1204 |
BEGIN |
---|
1205 |
|
---|
1206 |
SELECT COUNT(1) INTO v_nrunning |
---|
1207 |
from stratcon.rollup_runner t, pg_stat_activity a |
---|
1208 |
where rollup_table ='rollup_matrix_numeric_12hours' |
---|
1209 |
and runner = procpid || '.' || date_part('epoch',backend_start); |
---|
1210 |
|
---|
1211 |
IF v_nrunning > 0 THEN |
---|
1212 |
RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running'; |
---|
1213 |
RETURN ; |
---|
1214 |
END IF; |
---|
1215 |
|
---|
1216 |
SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) |
---|
1217 |
FROM pg_stat_activity |
---|
1218 |
WHERE procpid = pg_backend_pid(); |
---|
1219 |
|
---|
1220 |
IF v_self IS NULL THEN |
---|
1221 |
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot self-identify'; |
---|
1222 |
END IF; |
---|
1223 |
|
---|
1224 |
v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_12hours'''; |
---|
1225 |
|
---|
1226 |
EXECUTE v_sql; |
---|
1227 |
|
---|
1228 |
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='12 hours' LOOP |
---|
1229 |
|
---|
1230 |
SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours' |
---|
1231 |
INTO v_min_whence; |
---|
1232 |
|
---|
1233 |
SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_12hours |
---|
1234 |
INTO v_max_rollup_12; |
---|
1235 |
|
---|
1236 |
/*-- Insert Log for 24 Hours rollup |
---|
1237 |
|
---|
1238 |
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' |
---|
1239 |
INTO v_whence; |
---|
1240 |
IF NOT FOUND THEN |
---|
1241 |
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'); |
---|
1242 |
END IF; |
---|
1243 |
*/ |
---|
1244 |
|
---|
1245 |
IF v_min_whence <= v_max_rollup_12 THEN |
---|
1246 |
|
---|
1247 |
DELETE FROM stratcon.rollup_matrix_numeric_12hours |
---|
1248 |
WHERE rollup_time= v_min_whence; |
---|
1249 |
|
---|
1250 |
END IF; |
---|
1251 |
|
---|
1252 |
FOR rec IN |
---|
1253 |
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 |
---|
1254 |
FROM stratcon.rollup_matrix_numeric_6hours |
---|
1255 |
WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval |
---|
1256 |
GROUP BY sid,name |
---|
1257 |
LOOP |
---|
1258 |
|
---|
1259 |
|
---|
1260 |
INSERT INTO stratcon.rollup_matrix_numeric_12hours |
---|
1261 |
(sid,name,rollup_time,count_rows,avg_value) VALUES |
---|
1262 |
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); |
---|
1263 |
|
---|
1264 |
END LOOP; |
---|
1265 |
|
---|
1266 |
|
---|
1267 |
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours'; |
---|
1268 |
|
---|
1269 |
v_min_whence := NULL; |
---|
1270 |
v_max_rollup_12 := NULL; |
---|
1271 |
|
---|
1272 |
END LOOP; |
---|
1273 |
|
---|
1274 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_12hours'; |
---|
1275 |
|
---|
1276 |
RETURN; |
---|
1277 |
EXCEPTION |
---|
1278 |
WHEN RAISE_EXCEPTION THEN |
---|
1279 |
UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours'; |
---|
1280 |
RAISE EXCEPTION '%', SQLERRM; |
---|
1281 |
WHEN OTHERS THEN |
---|
1282 |
RAISE NOTICE '%', SQLERRM; |
---|
1283 |
END |
---|
1284 |
$$ |
---|
1285 |
LANGUAGE plpgsql; |
---|
1286 |
|
---|
1287 |
|
---|
1288 |
ALTER FUNCTION stratcon.rollup_matrix_numeric_12hours() OWNER TO reconnoiter; |
---|
1289 |
|
---|
1290 |
-- |
---|
1291 |
-- Name: rollup_matrix_numeric_20m(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
1292 |
-- |
---|
1293 |
|
---|
1294 |
CREATE FUNCTION rollup_matrix_numeric_20m() RETURNS void |
---|
1295 |
AS $$ |
---|
1296 |
DECLARE |
---|
1297 |
|
---|
1298 |
rec stratcon.rollup_matrix_numeric_20m%rowtype; |
---|
1299 |
v_sql TEXT; |
---|
1300 |
v_min_whence TIMESTAMPTZ; |
---|
1301 |
v_max_rollup_20 TIMESTAMPTZ; |
---|
1302 |
v_whence TIMESTAMPTZ; |
---|
1303 |
rows INT; |
---|
1304 |
v_nrunning INT; |
---|
1305 |
v_self VARCHAR(22); |
---|
1306 |
whenceint RECORD; |
---|
1307 |
BEGIN |
---|
1308 |
|
---|
1309 |
SELECT COUNT(1) INTO v_nrunning |
---|
1310 |
from stratcon.rollup_runner t, pg_stat_activity a |
---|
1311 |
where rollup_table ='rollup_matrix_numeric_20m' |
---|
1312 |
and runner = procpid || '.' || date_part('epoch',backend_start); |
---|
1313 |
|
---|
1314 |
IF v_nrunning > 0 THEN |
---|
1315 |
RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running'; |
---|
1316 |
RETURN ; |
---|
1317 |
END IF; |
---|
1318 |
|
---|
1319 |
SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) |
---|
1320 |
FROM pg_stat_activity |
---|
1321 |
WHERE procpid = pg_backend_pid(); |
---|
1322 |
|
---|
1323 |
IF v_self IS NULL THEN |
---|
1324 |
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot self-identify'; |
---|
1325 |
END IF; |
---|
1326 |
|
---|
1327 |
v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_20m'''; |
---|
1328 |
|
---|
1329 |
EXECUTE v_sql; |
---|
1330 |
|
---|
1331 |
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='20 minutes' LOOP |
---|
1332 |
|
---|
1333 |
SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='20 minutes' |
---|
1334 |
INTO v_min_whence; |
---|
1335 |
|
---|
1336 |
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_20m |
---|
1337 |
INTO v_max_rollup_20; |
---|
1338 |
|
---|
1339 |
-- Insert Log for Hourly rollup |
---|
1340 |
|
---|
1341 |
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour' |
---|
1342 |
INTO v_whence; |
---|
1343 |
IF NOT FOUND THEN |
---|
1344 |
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour'); |
---|
1345 |
END IF; |
---|
1346 |
|
---|
1347 |
IF v_min_whence <= v_max_rollup_20 THEN |
---|
1348 |
|
---|
1349 |
DELETE FROM stratcon.rollup_matrix_numeric_20m |
---|
1350 |
WHERE rollup_time = v_min_whence; |
---|
1351 |
|
---|
1352 |
END IF; |
---|
1353 |
|
---|
1354 |
FOR rec IN |
---|
1355 |
SELECT sid , name,v_min_whence as rollup_time, |
---|
1356 |
SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value |
---|
1357 |
FROM stratcon.rollup_matrix_numeric_5m |
---|
1358 |
WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval |
---|
1359 |
GROUP BY sid,name |
---|
1360 |
|
---|
1361 |
LOOP |
---|
1362 |
|
---|
1363 |
|
---|
1364 |
INSERT INTO stratcon.rollup_matrix_numeric_20m |
---|
1365 |
(sid,name,rollup_time,count_rows,avg_value) VALUES |
---|
1366 |
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); |
---|
1367 |
|
---|
1368 |
END LOOP; |
---|
1369 |
|
---|
1370 |
-- Delete from whence log table |
---|
1371 |
|
---|
1372 |
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='20 minutes'; |
---|
1373 |
|
---|
1374 |
v_min_whence:= NULL; |
---|
1375 |
v_max_rollup_20:= NULL; |
---|
1376 |
|
---|
1377 |
END LOOP; |
---|
1378 |
|
---|
1379 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; |
---|
1380 |
|
---|
1381 |
RETURN; |
---|
1382 |
|
---|
1383 |
EXCEPTION |
---|
1384 |
WHEN RAISE_EXCEPTION THEN |
---|
1385 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; |
---|
1386 |
RAISE EXCEPTION '%', SQLERRM; |
---|
1387 |
WHEN OTHERS THEN |
---|
1388 |
RAISE NOTICE '%', SQLERRM; |
---|
1389 |
END |
---|
1390 |
$$ |
---|
1391 |
LANGUAGE plpgsql; |
---|
1392 |
|
---|
1393 |
|
---|
1394 |
ALTER FUNCTION stratcon.rollup_matrix_numeric_20m() OWNER TO reconnoiter; |
---|
1395 |
|
---|
1396 |
-- |
---|
1397 |
-- Name: rollup_matrix_numeric_5m(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
1398 |
-- |
---|
1399 |
|
---|
1400 |
CREATE FUNCTION rollup_matrix_numeric_5m() RETURNS void |
---|
1401 |
AS $$ |
---|
1402 |
DECLARE |
---|
1403 |
|
---|
1404 |
rec stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
1405 |
v_sql TEXT; |
---|
1406 |
v_min_whence TIMESTAMPTZ; |
---|
1407 |
v_max_rollup_5 TIMESTAMPTZ; |
---|
1408 |
v_whence TIMESTAMPTZ; |
---|
1409 |
rows INT; |
---|
1410 |
v_nrunning INT; |
---|
1411 |
v_self VARCHAR(22); |
---|
1412 |
whenceint RECORD; |
---|
1413 |
BEGIN |
---|
1414 |
|
---|
1415 |
SELECT COUNT(1) INTO v_nrunning |
---|
1416 |
from stratcon.rollup_runner t, pg_stat_activity a |
---|
1417 |
where rollup_table ='rollup_matrix_numeric_5m' |
---|
1418 |
and runner = procpid || '.' || date_part('epoch',backend_start); |
---|
1419 |
|
---|
1420 |
IF v_nrunning > 0 THEN |
---|
1421 |
RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running'; |
---|
1422 |
RETURN ; |
---|
1423 |
END IF; |
---|
1424 |
|
---|
1425 |
SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) |
---|
1426 |
FROM pg_stat_activity |
---|
1427 |
WHERE procpid = pg_backend_pid(); |
---|
1428 |
|
---|
1429 |
IF v_self IS NULL THEN |
---|
1430 |
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot self-identify'; |
---|
1431 |
END IF; |
---|
1432 |
|
---|
1433 |
v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_5m'''; |
---|
1434 |
|
---|
1435 |
EXECUTE v_sql; |
---|
1436 |
|
---|
1437 |
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='5 minutes' LOOP |
---|
1438 |
|
---|
1439 |
|
---|
1440 |
SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes' |
---|
1441 |
INTO v_min_whence; |
---|
1442 |
|
---|
1443 |
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_5m |
---|
1444 |
INTO v_max_rollup_5; |
---|
1445 |
|
---|
1446 |
-- Insert Log for 20 minutes rollup |
---|
1447 |
|
---|
1448 |
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' |
---|
1449 |
INTO v_whence; |
---|
1450 |
IF NOT FOUND THEN |
---|
1451 |
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'); |
---|
1452 |
END IF; |
---|
1453 |
IF v_min_whence <= v_max_rollup_5 THEN |
---|
1454 |
|
---|
1455 |
DELETE FROM stratcon.rollup_matrix_numeric_5m |
---|
1456 |
WHERE rollup_time = v_min_whence; |
---|
1457 |
|
---|
1458 |
END IF; |
---|
1459 |
|
---|
1460 |
FOR rec IN |
---|
1461 |
SELECT sid , name,v_min_whence as rollup_time, |
---|
1462 |
COUNT(1) as count_rows ,AVG(value) as avg_value |
---|
1463 |
FROM stratcon.loading_dock_metric_numeric_s |
---|
1464 |
WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval |
---|
1465 |
GROUP BY rollup_time,sid,name |
---|
1466 |
|
---|
1467 |
LOOP |
---|
1468 |
|
---|
1469 |
|
---|
1470 |
INSERT INTO stratcon.rollup_matrix_numeric_5m |
---|
1471 |
(sid,name,rollup_time,count_rows,avg_value) VALUES |
---|
1472 |
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); |
---|
1473 |
|
---|
1474 |
END LOOP; |
---|
1475 |
|
---|
1476 |
-- Delete from whence log table |
---|
1477 |
|
---|
1478 |
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes'; |
---|
1479 |
|
---|
1480 |
v_min_whence:= NULL; |
---|
1481 |
v_max_rollup_5:= NULL; |
---|
1482 |
|
---|
1483 |
END LOOP; |
---|
1484 |
|
---|
1485 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; |
---|
1486 |
|
---|
1487 |
RETURN; |
---|
1488 |
|
---|
1489 |
EXCEPTION |
---|
1490 |
WHEN RAISE_EXCEPTION THEN |
---|
1491 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; |
---|
1492 |
RAISE EXCEPTION '%', SQLERRM; |
---|
1493 |
WHEN OTHERS THEN |
---|
1494 |
RAISE NOTICE '%', SQLERRM; |
---|
1495 |
END |
---|
1496 |
$$ |
---|
1497 |
LANGUAGE plpgsql; |
---|
1498 |
|
---|
1499 |
|
---|
1500 |
ALTER FUNCTION stratcon.rollup_matrix_numeric_5m() OWNER TO reconnoiter; |
---|
1501 |
|
---|
1502 |
-- |
---|
1503 |
-- Name: rollup_matrix_numeric_60m(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
1504 |
-- |
---|
1505 |
|
---|
1506 |
CREATE FUNCTION rollup_matrix_numeric_60m() RETURNS void |
---|
1507 |
AS $$ |
---|
1508 |
DECLARE |
---|
1509 |
rec stratcon.rollup_matrix_numeric_60m%rowtype; |
---|
1510 |
v_sql TEXT; |
---|
1511 |
v_min_whence TIMESTAMPTZ; |
---|
1512 |
v_max_rollup_60 TIMESTAMPTZ; |
---|
1513 |
v_whence TIMESTAMPTZ; |
---|
1514 |
v_nrunning INT; |
---|
1515 |
v_self VARCHAR(22); |
---|
1516 |
whenceint RECORD; |
---|
1517 |
BEGIN |
---|
1518 |
|
---|
1519 |
SELECT COUNT(1) INTO v_nrunning |
---|
1520 |
from stratcon.rollup_runner t, pg_stat_activity a |
---|
1521 |
where rollup_table ='rollup_matrix_numeric_60m' |
---|
1522 |
and runner = procpid || '.' || date_part('epoch',backend_start); |
---|
1523 |
|
---|
1524 |
IF v_nrunning > 0 THEN |
---|
1525 |
RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running'; |
---|
1526 |
RETURN ; |
---|
1527 |
END IF; |
---|
1528 |
|
---|
1529 |
SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) |
---|
1530 |
FROM pg_stat_activity |
---|
1531 |
WHERE procpid = pg_backend_pid(); |
---|
1532 |
|
---|
1533 |
IF v_self IS NULL THEN |
---|
1534 |
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot self-identify'; |
---|
1535 |
END IF; |
---|
1536 |
|
---|
1537 |
v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_60m'''; |
---|
1538 |
|
---|
1539 |
EXECUTE v_sql; |
---|
1540 |
|
---|
1541 |
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='1 hour' LOOP |
---|
1542 |
|
---|
1543 |
SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour' |
---|
1544 |
INTO v_min_whence; |
---|
1545 |
|
---|
1546 |
SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_60m |
---|
1547 |
INTO v_max_rollup_60; |
---|
1548 |
|
---|
1549 |
-- Insert Log for 6 Hour rollup |
---|
1550 |
|
---|
1551 |
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' |
---|
1552 |
INTO v_whence; |
---|
1553 |
IF NOT FOUND THEN |
---|
1554 |
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'); |
---|
1555 |
END IF; |
---|
1556 |
|
---|
1557 |
|
---|
1558 |
IF v_min_whence <= v_max_rollup_60 THEN |
---|
1559 |
|
---|
1560 |
DELETE FROM stratcon.rollup_matrix_numeric_60m |
---|
1561 |
WHERE rollup_time= v_min_whence; |
---|
1562 |
|
---|
1563 |
END IF; |
---|
1564 |
|
---|
1565 |
FOR rec IN |
---|
1566 |
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 |
---|
1567 |
FROM stratcon.rollup_matrix_numeric_20m |
---|
1568 |
WHERE date_hour(rollup_time)= v_min_whence |
---|
1569 |
GROUP BY date_hour(rollup_time),sid,name |
---|
1570 |
LOOP |
---|
1571 |
|
---|
1572 |
INSERT INTO stratcon.rollup_matrix_numeric_60m |
---|
1573 |
(sid,name,rollup_time,count_rows,avg_value) VALUES |
---|
1574 |
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); |
---|
1575 |
|
---|
1576 |
END LOOP; |
---|
1577 |
|
---|
1578 |
|
---|
1579 |
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour'; |
---|
1580 |
|
---|
1581 |
v_min_whence := NULL; |
---|
1582 |
v_max_rollup_60 := NULL; |
---|
1583 |
|
---|
1584 |
END LOOP; |
---|
1585 |
|
---|
1586 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; |
---|
1587 |
|
---|
1588 |
RETURN; |
---|
1589 |
|
---|
1590 |
EXCEPTION |
---|
1591 |
WHEN RAISE_EXCEPTION THEN |
---|
1592 |
RAISE EXCEPTION '%', SQLERRM; |
---|
1593 |
WHEN OTHERS THEN |
---|
1594 |
RAISE NOTICE '%', SQLERRM; |
---|
1595 |
END |
---|
1596 |
$$ |
---|
1597 |
LANGUAGE plpgsql; |
---|
1598 |
|
---|
1599 |
|
---|
1600 |
ALTER FUNCTION stratcon.rollup_matrix_numeric_60m() OWNER TO reconnoiter; |
---|
1601 |
|
---|
1602 |
-- |
---|
1603 |
-- Name: rollup_matrix_numeric_6hours(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
1604 |
-- |
---|
1605 |
|
---|
1606 |
CREATE FUNCTION rollup_matrix_numeric_6hours() RETURNS void |
---|
1607 |
AS $$ |
---|
1608 |
DECLARE |
---|
1609 |
rec stratcon.rollup_matrix_numeric_6hours%rowtype; |
---|
1610 |
v_sql TEXT; |
---|
1611 |
v_min_whence TIMESTAMPTZ; |
---|
1612 |
v_max_rollup_6 TIMESTAMPTZ; |
---|
1613 |
v_whence TIMESTAMPTZ; |
---|
1614 |
v_nrunning INT; |
---|
1615 |
v_self VARCHAR(22); |
---|
1616 |
whenceint RECORD; |
---|
1617 |
BEGIN |
---|
1618 |
|
---|
1619 |
SELECT COUNT(1) INTO v_nrunning |
---|
1620 |
from stratcon.rollup_runner t, pg_stat_activity a |
---|
1621 |
where rollup_table ='rollup_matrix_numeric_6hours' |
---|
1622 |
and runner = procpid || '.' || date_part('epoch',backend_start); |
---|
1623 |
|
---|
1624 |
IF v_nrunning > 0 THEN |
---|
1625 |
RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running'; |
---|
1626 |
RETURN ; |
---|
1627 |
END IF; |
---|
1628 |
|
---|
1629 |
SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) |
---|
1630 |
FROM pg_stat_activity |
---|
1631 |
WHERE procpid = pg_backend_pid(); |
---|
1632 |
|
---|
1633 |
IF v_self IS NULL THEN |
---|
1634 |
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot self-identify'; |
---|
1635 |
END IF; |
---|
1636 |
|
---|
1637 |
v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_6hours'''; |
---|
1638 |
|
---|
1639 |
EXECUTE v_sql; |
---|
1640 |
|
---|
1641 |
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='6 hours' LOOP |
---|
1642 |
|
---|
1643 |
SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours' |
---|
1644 |
INTO v_min_whence; |
---|
1645 |
|
---|
1646 |
SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_6hours |
---|
1647 |
INTO v_max_rollup_6; |
---|
1648 |
|
---|
1649 |
-- Insert Log for 12 Hours rollup |
---|
1650 |
|
---|
1651 |
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' |
---|
1652 |
INTO v_whence; |
---|
1653 |
IF NOT FOUND THEN |
---|
1654 |
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'); |
---|
1655 |
END IF; |
---|
1656 |
|
---|
1657 |
|
---|
1658 |
IF v_min_whence <= v_max_rollup_6 THEN |
---|
1659 |
|
---|
1660 |
DELETE FROM stratcon.rollup_matrix_numeric_6hours |
---|
1661 |
WHERE rollup_time= v_min_whence; |
---|
1662 |
|
---|
1663 |
END IF; |
---|
1664 |
|
---|
1665 |
FOR rec IN |
---|
1666 |
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 |
---|
1667 |
FROM stratcon.rollup_matrix_numeric_60m |
---|
1668 |
WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval |
---|
1669 |
GROUP BY sid,name |
---|
1670 |
LOOP |
---|
1671 |
|
---|
1672 |
|
---|
1673 |
INSERT INTO stratcon.rollup_matrix_numeric_6hours |
---|
1674 |
(sid,name,rollup_time,count_rows,avg_value) VALUES |
---|
1675 |
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); |
---|
1676 |
|
---|
1677 |
END LOOP; |
---|
1678 |
|
---|
1679 |
|
---|
1680 |
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours'; |
---|
1681 |
v_min_whence := NULL; |
---|
1682 |
v_max_rollup_6 := NULL; |
---|
1683 |
|
---|
1684 |
END LOOP; |
---|
1685 |
|
---|
1686 |
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours'; |
---|
1687 |
|
---|
1688 |
RETURN; |
---|
1689 |
|
---|
1690 |
EXCEPTION |
---|
1691 |
WHEN RAISE_EXCEPTION THEN |
---|
1692 |
RAISE EXCEPTION '%', SQLERRM; |
---|
1693 |
WHEN OTHERS THEN |
---|
1694 |
RAISE NOTICE '%', SQLERRM; |
---|
1695 |
END |
---|
1696 |
$$ |
---|
1697 |
LANGUAGE plpgsql; |
---|
1698 |
|
---|
1699 |
|
---|
1700 |
ALTER FUNCTION stratcon.rollup_matrix_numeric_6hours() OWNER TO reconnoiter; |
---|
1701 |
|
---|
1702 |
-- |
---|
1703 |
-- Name: update_config(inet, text, timestamp with time zone, xml); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter |
---|
1704 |
-- |
---|
1705 |
|
---|
1706 |
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 |
---|
1707 |
AS $$ |
---|
1708 |
DECLARE |
---|
1709 |
v_config xml; |
---|
1710 |
BEGIN |
---|
1711 |
select config into v_config from stratcon.current_node_config |
---|
1712 |
where remote_address = v_remote_address_in |
---|
1713 |
and node_type = v_node_type_in; |
---|
1714 |
IF FOUND THEN |
---|
1715 |
IF v_config::text = v_config_in::text THEN |
---|
1716 |
RETURN; |
---|
1717 |
END IF; |
---|
1718 |
delete from stratcon.current_node_config |
---|
1719 |
where remote_address = v_remote_address_in |
---|
1720 |
and node_type = v_node_type_in; |
---|
1721 |
END IF; |
---|
1722 |
insert into stratcon.current_node_config |
---|
1723 |
(remote_address, node_type, whence, config) |
---|
1724 |
values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in); |
---|
1725 |
insert into stratcon.current_node_config_changelog |
---|
1726 |
(remote_address, node_type, whence, config) |
---|
1727 |
values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in); |
---|
1728 |
END |
---|
1729 |
$$ |
---|
1730 |
LANGUAGE plpgsql; |
---|
1731 |
|
---|
1732 |
|
---|
1733 |
ALTER FUNCTION stratcon.update_config(v_remote_address_in inet, v_node_type_in text, v_whence_in timestamp with time zone, v_config_in xml) OWNER TO reconnoiter; |
---|
1734 |
|
---|
1735 |
-- |
---|
1736 |
-- Name: seq_sid; Type: SEQUENCE; Schema: stratcon; Owner: reconnoiter |
---|
1737 |
-- |
---|
1738 |
|
---|
1739 |
CREATE SEQUENCE seq_sid |
---|
1740 |
INCREMENT BY 1 |
---|
1741 |
NO MAXVALUE |
---|
1742 |
NO MINVALUE |
---|
1743 |
CACHE 1; |
---|
1744 |
|
---|
1745 |
|
---|
1746 |
ALTER TABLE stratcon.seq_sid OWNER TO reconnoiter; |
---|
1747 |
|
---|
1748 |
SET search_path = prism, pg_catalog; |
---|
1749 |
|
---|
1750 |
-- |
---|
1751 |
-- Name: saved_graphs_dep_pkey; Type: CONSTRAINT; Schema: prism; Owner: reconnoiter; Tablespace: |
---|
1752 |
-- |
---|
1753 |
|
---|
1754 |
ALTER TABLE ONLY saved_graphs_dep |
---|
1755 |
ADD CONSTRAINT saved_graphs_dep_pkey PRIMARY KEY (graphid, sid, metric_name); |
---|
1756 |
|
---|
1757 |
|
---|
1758 |
-- |
---|
1759 |
-- Name: saved_graphs_pkey; Type: CONSTRAINT; Schema: prism; Owner: reconnoiter; Tablespace: |
---|
1760 |
-- |
---|
1761 |
|
---|
1762 |
ALTER TABLE ONLY saved_graphs |
---|
1763 |
ADD CONSTRAINT saved_graphs_pkey PRIMARY KEY (graphid); |
---|
1764 |
|
---|
1765 |
|
---|
1766 |
SET search_path = public, pg_catalog; |
---|
1767 |
|
---|
1768 |
-- |
---|
1769 |
-- Name: pga_diagrams_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace: |
---|
1770 |
-- |
---|
1771 |
|
---|
1772 |
ALTER TABLE ONLY pga_diagrams |
---|
1773 |
ADD CONSTRAINT pga_diagrams_pkey PRIMARY KEY (diagramname); |
---|
1774 |
|
---|
1775 |
|
---|
1776 |
-- |
---|
1777 |
-- Name: pga_forms_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace: |
---|
1778 |
-- |
---|
1779 |
|
---|
1780 |
ALTER TABLE ONLY pga_forms |
---|
1781 |
ADD CONSTRAINT pga_forms_pkey PRIMARY KEY (formname); |
---|
1782 |
|
---|
1783 |
|
---|
1784 |
-- |
---|
1785 |
-- Name: pga_graphs_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace: |
---|
1786 |
-- |
---|
1787 |
|
---|
1788 |
ALTER TABLE ONLY pga_graphs |
---|
1789 |
ADD CONSTRAINT pga_graphs_pkey PRIMARY KEY (graphname); |
---|
1790 |
|
---|
1791 |
|
---|
1792 |
-- |
---|
1793 |
-- Name: pga_images_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace: |
---|
1794 |
-- |
---|
1795 |
|
---|
1796 |
ALTER TABLE ONLY pga_images |
---|
1797 |
ADD CONSTRAINT pga_images_pkey PRIMARY KEY (imagename); |
---|
1798 |
|
---|
1799 |
|
---|
1800 |
-- |
---|
1801 |
-- Name: pga_layout_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace: |
---|
1802 |
-- |
---|
1803 |
|
---|
1804 |
ALTER TABLE ONLY pga_layout |
---|
1805 |
ADD CONSTRAINT pga_layout_pkey PRIMARY KEY (tablename); |
---|
1806 |
|
---|
1807 |
|
---|
1808 |
-- |
---|
1809 |
-- Name: pga_queries_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace: |
---|
1810 |
-- |
---|
1811 |
|
---|
1812 |
ALTER TABLE ONLY pga_queries |
---|
1813 |
ADD CONSTRAINT pga_queries_pkey PRIMARY KEY (queryname); |
---|
1814 |
|
---|
1815 |
|
---|
1816 |
-- |
---|
1817 |
-- Name: pga_reports_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace: |
---|
1818 |
-- |
---|
1819 |
|
---|
1820 |
ALTER TABLE ONLY pga_reports |
---|
1821 |
ADD CONSTRAINT pga_reports_pkey PRIMARY KEY (reportname); |
---|
1822 |
|
---|
1823 |
|
---|
1824 |
-- |
---|
1825 |
-- Name: pga_scripts_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace: |
---|
1826 |
-- |
---|
1827 |
|
---|
1828 |
ALTER TABLE ONLY pga_scripts |
---|
1829 |
ADD CONSTRAINT pga_scripts_pkey PRIMARY KEY (scriptname); |
---|
1830 |
|
---|
1831 |
|
---|
1832 |
SET search_path = stratcon, pg_catalog; |
---|
1833 |
|
---|
1834 |
-- |
---|
1835 |
-- Name: current_metric_text_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1836 |
-- |
---|
1837 |
|
---|
1838 |
ALTER TABLE ONLY current_metric_text |
---|
1839 |
ADD CONSTRAINT current_metric_text_pkey PRIMARY KEY (sid, name); |
---|
1840 |
|
---|
1841 |
|
---|
1842 |
-- |
---|
1843 |
-- Name: current_node_config_changelog_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1844 |
-- |
---|
1845 |
|
---|
1846 |
ALTER TABLE ONLY current_node_config_changelog |
---|
1847 |
ADD CONSTRAINT current_node_config_changelog_pkey PRIMARY KEY (remote_address, node_type, whence); |
---|
1848 |
|
---|
1849 |
|
---|
1850 |
-- |
---|
1851 |
-- Name: current_node_config_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1852 |
-- |
---|
1853 |
|
---|
1854 |
ALTER TABLE ONLY current_node_config |
---|
1855 |
ADD CONSTRAINT current_node_config_pkey PRIMARY KEY (remote_address, node_type); |
---|
1856 |
|
---|
1857 |
|
---|
1858 |
-- |
---|
1859 |
-- Name: loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1860 |
-- |
---|
1861 |
|
---|
1862 |
ALTER TABLE ONLY loading_dock_check_s |
---|
1863 |
ADD CONSTRAINT loading_dock_check_s_pkey PRIMARY KEY (sid, id, whence); |
---|
1864 |
|
---|
1865 |
|
---|
1866 |
-- |
---|
1867 |
-- Name: loading_dock_metric_numeric_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1868 |
-- |
---|
1869 |
|
---|
1870 |
ALTER TABLE ONLY loading_dock_metric_numeric_s |
---|
1871 |
ADD CONSTRAINT loading_dock_metric_numeric_s_pkey PRIMARY KEY (whence, sid, name); |
---|
1872 |
|
---|
1873 |
ALTER TABLE loading_dock_metric_numeric_s CLUSTER ON loading_dock_metric_numeric_s_pkey; |
---|
1874 |
|
---|
1875 |
|
---|
1876 |
-- |
---|
1877 |
-- Name: loading_dock_metric_text_s_change_log_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1878 |
-- |
---|
1879 |
|
---|
1880 |
ALTER TABLE ONLY loading_dock_metric_text_s_change_log |
---|
1881 |
ADD CONSTRAINT loading_dock_metric_text_s_change_log_pkey PRIMARY KEY (whence, sid, name); |
---|
1882 |
|
---|
1883 |
|
---|
1884 |
-- |
---|
1885 |
-- Name: loading_dock_metric_text_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1886 |
-- |
---|
1887 |
|
---|
1888 |
ALTER TABLE ONLY loading_dock_metric_text_s |
---|
1889 |
ADD CONSTRAINT loading_dock_metric_text_s_pkey PRIMARY KEY (whence, sid, name); |
---|
1890 |
|
---|
1891 |
|
---|
1892 |
-- |
---|
1893 |
-- Name: loading_dock_status_s_change_log_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1894 |
-- |
---|
1895 |
|
---|
1896 |
ALTER TABLE ONLY loading_dock_status_s_change_log |
---|
1897 |
ADD CONSTRAINT loading_dock_status_s_change_log_pkey PRIMARY KEY (sid, whence); |
---|
1898 |
|
---|
1899 |
|
---|
1900 |
-- |
---|
1901 |
-- Name: loading_dock_status_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1902 |
-- |
---|
1903 |
|
---|
1904 |
ALTER TABLE ONLY loading_dock_status_s |
---|
1905 |
ADD CONSTRAINT loading_dock_status_s_pkey PRIMARY KEY (sid, whence); |
---|
1906 |
|
---|
1907 |
|
---|
1908 |
-- |
---|
1909 |
-- Name: log_whence_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1910 |
-- |
---|
1911 |
|
---|
1912 |
ALTER TABLE ONLY log_whence_s |
---|
1913 |
ADD CONSTRAINT log_whence_s_pkey PRIMARY KEY (whence, "interval"); |
---|
1914 |
|
---|
1915 |
|
---|
1916 |
-- |
---|
1917 |
-- Name: map_uuid_to_sid_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1918 |
-- |
---|
1919 |
|
---|
1920 |
ALTER TABLE ONLY map_uuid_to_sid |
---|
1921 |
ADD CONSTRAINT map_uuid_to_sid_pkey PRIMARY KEY (id, sid); |
---|
1922 |
|
---|
1923 |
|
---|
1924 |
-- |
---|
1925 |
-- Name: metric_name_summary_pk; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1926 |
-- |
---|
1927 |
|
---|
1928 |
ALTER TABLE ONLY metric_name_summary |
---|
1929 |
ADD CONSTRAINT metric_name_summary_pk UNIQUE (sid, metric_name, metric_type); |
---|
1930 |
|
---|
1931 |
|
---|
1932 |
-- |
---|
1933 |
-- Name: mv_loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1934 |
-- |
---|
1935 |
|
---|
1936 |
ALTER TABLE ONLY mv_loading_dock_check_s |
---|
1937 |
ADD CONSTRAINT mv_loading_dock_check_s_pkey PRIMARY KEY (sid); |
---|
1938 |
|
---|
1939 |
|
---|
1940 |
-- |
---|
1941 |
-- Name: rollup_matrix_numeric_12hours_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1942 |
-- |
---|
1943 |
|
---|
1944 |
ALTER TABLE ONLY rollup_matrix_numeric_12hours |
---|
1945 |
ADD CONSTRAINT rollup_matrix_numeric_12hours_pkey PRIMARY KEY (rollup_time, sid, name); |
---|
1946 |
|
---|
1947 |
ALTER TABLE rollup_matrix_numeric_12hours CLUSTER ON rollup_matrix_numeric_12hours_pkey; |
---|
1948 |
|
---|
1949 |
|
---|
1950 |
-- |
---|
1951 |
-- Name: rollup_matrix_numeric_20m_new_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1952 |
-- |
---|
1953 |
|
---|
1954 |
ALTER TABLE ONLY rollup_matrix_numeric_20m |
---|
1955 |
ADD CONSTRAINT rollup_matrix_numeric_20m_new_pkey PRIMARY KEY (rollup_time, sid, name); |
---|
1956 |
|
---|
1957 |
ALTER TABLE rollup_matrix_numeric_20m CLUSTER ON rollup_matrix_numeric_20m_new_pkey; |
---|
1958 |
|
---|
1959 |
|
---|
1960 |
-- |
---|
1961 |
-- Name: rollup_matrix_numeric_5m_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1962 |
-- |
---|
1963 |
|
---|
1964 |
ALTER TABLE ONLY rollup_matrix_numeric_5m |
---|
1965 |
ADD CONSTRAINT rollup_matrix_numeric_5m_pkey PRIMARY KEY (rollup_time, sid, name); |
---|
1966 |
|
---|
1967 |
ALTER TABLE rollup_matrix_numeric_5m CLUSTER ON rollup_matrix_numeric_5m_pkey; |
---|
1968 |
|
---|
1969 |
|
---|
1970 |
-- |
---|
1971 |
-- Name: rollup_matrix_numeric_60m_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1972 |
-- |
---|
1973 |
|
---|
1974 |
ALTER TABLE ONLY rollup_matrix_numeric_60m |
---|
1975 |
ADD CONSTRAINT rollup_matrix_numeric_60m_pkey PRIMARY KEY (rollup_time, sid, name); |
---|
1976 |
|
---|
1977 |
ALTER TABLE rollup_matrix_numeric_60m CLUSTER ON rollup_matrix_numeric_60m_pkey; |
---|
1978 |
|
---|
1979 |
|
---|
1980 |
-- |
---|
1981 |
-- Name: rollup_matrix_numeric_6hours_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1982 |
-- |
---|
1983 |
|
---|
1984 |
ALTER TABLE ONLY rollup_matrix_numeric_6hours |
---|
1985 |
ADD CONSTRAINT rollup_matrix_numeric_6hours_pkey PRIMARY KEY (rollup_time, sid, name); |
---|
1986 |
|
---|
1987 |
ALTER TABLE rollup_matrix_numeric_6hours CLUSTER ON rollup_matrix_numeric_6hours_pkey; |
---|
1988 |
|
---|
1989 |
|
---|
1990 |
-- |
---|
1991 |
-- Name: idx_mv_loading_dock_check_s_module; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1992 |
-- |
---|
1993 |
|
---|
1994 |
CREATE INDEX idx_mv_loading_dock_check_s_module ON mv_loading_dock_check_s USING btree (module); |
---|
1995 |
|
---|
1996 |
|
---|
1997 |
-- |
---|
1998 |
-- Name: idx_mv_loading_dock_check_s_name; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
1999 |
-- |
---|
2000 |
|
---|
2001 |
CREATE INDEX idx_mv_loading_dock_check_s_name ON mv_loading_dock_check_s USING btree (name); |
---|
2002 |
|
---|
2003 |
|
---|
2004 |
-- |
---|
2005 |
-- Name: idx_mv_loading_dock_check_s_target; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
2006 |
-- |
---|
2007 |
|
---|
2008 |
CREATE INDEX idx_mv_loading_dock_check_s_target ON mv_loading_dock_check_s USING btree (target); |
---|
2009 |
|
---|
2010 |
|
---|
2011 |
-- |
---|
2012 |
-- Name: idx_rollup_matrix_numeric_20m_rollup_time; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
2013 |
-- |
---|
2014 |
|
---|
2015 |
CREATE INDEX idx_rollup_matrix_numeric_20m_rollup_time ON rollup_matrix_numeric_20m USING btree (date_hour(rollup_time)); |
---|
2016 |
|
---|
2017 |
|
---|
2018 |
-- |
---|
2019 |
-- Name: unq_mv_loading_dock_check_s_id; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace: |
---|
2020 |
-- |
---|
2021 |
|
---|
2022 |
CREATE UNIQUE INDEX unq_mv_loading_dock_check_s_id ON mv_loading_dock_check_s USING btree (id); |
---|
2023 |
|
---|
2024 |
|
---|
2025 |
SET search_path = prism, pg_catalog; |
---|
2026 |
|
---|
2027 |
-- |
---|
2028 |
-- Name: check_name_saved_graphs; Type: TRIGGER; Schema: prism; Owner: reconnoiter |
---|
2029 |
-- |
---|
2030 |
|
---|
2031 |
CREATE TRIGGER check_name_saved_graphs |
---|
2032 |
BEFORE INSERT OR UPDATE ON saved_graphs |
---|
2033 |
FOR EACH ROW |
---|
2034 |
EXECUTE PROCEDURE check_name_saved_graphs(); |
---|
2035 |
|
---|
2036 |
|
---|
2037 |
SET search_path = stratcon, pg_catalog; |
---|
2038 |
|
---|
2039 |
-- |
---|
2040 |
-- Name: loading_dock_metric_numeric_s_whence_log; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter |
---|
2041 |
-- |
---|
2042 |
|
---|
2043 |
CREATE TRIGGER loading_dock_metric_numeric_s_whence_log |
---|
2044 |
AFTER INSERT ON loading_dock_metric_numeric_s |
---|
2045 |
FOR EACH ROW |
---|
2046 |
EXECUTE PROCEDURE loading_dock_metric_numeric_s_whence_log(); |
---|
2047 |
|
---|
2048 |
|
---|
2049 |
-- |
---|
2050 |
-- Name: loading_dock_metric_text_s_change_log; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter |
---|
2051 |
-- |
---|
2052 |
|
---|
2053 |
CREATE TRIGGER loading_dock_metric_text_s_change_log |
---|
2054 |
AFTER INSERT ON loading_dock_metric_text_s |
---|
2055 |
FOR EACH ROW |
---|
2056 |
EXECUTE PROCEDURE loading_dock_metric_text_s_change_log(); |
---|
2057 |
|
---|
2058 |
|
---|
2059 |
-- |
---|
2060 |
-- Name: loading_dock_status_s_change_log; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter |
---|
2061 |
-- |
---|
2062 |
|
---|
2063 |
CREATE TRIGGER loading_dock_status_s_change_log |
---|
2064 |
AFTER INSERT ON loading_dock_status_s |
---|
2065 |
FOR EACH ROW |
---|
2066 |
EXECUTE PROCEDURE loading_dock_status_s_change_log(); |
---|
2067 |
|
---|
2068 |
|
---|
2069 |
-- |
---|
2070 |
-- Name: mv_loading_dock_check_s; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter |
---|
2071 |
-- |
---|
2072 |
|
---|
2073 |
CREATE TRIGGER mv_loading_dock_check_s |
---|
2074 |
AFTER INSERT ON loading_dock_check_s |
---|
2075 |
FOR EACH ROW |
---|
2076 |
EXECUTE PROCEDURE mv_loading_dock_check_s(); |
---|
2077 |
|
---|
2078 |
|
---|
2079 |
SET search_path = prism, pg_catalog; |
---|
2080 |
|
---|
2081 |
-- |
---|
2082 |
-- Name: graphid_fk; Type: FK CONSTRAINT; Schema: prism; Owner: reconnoiter |
---|
2083 |
-- |
---|
2084 |
|
---|
2085 |
ALTER TABLE ONLY saved_graphs_dep |
---|
2086 |
ADD CONSTRAINT graphid_fk FOREIGN KEY (graphid) REFERENCES saved_graphs(graphid); |
---|
2087 |
|
---|
2088 |
|
---|
2089 |
-- |
---|
2090 |
-- Name: saved_graphs_dep_sid_fkey; Type: FK CONSTRAINT; Schema: prism; Owner: reconnoiter |
---|
2091 |
-- |
---|
2092 |
|
---|
2093 |
ALTER TABLE ONLY saved_graphs_dep |
---|
2094 |
ADD CONSTRAINT saved_graphs_dep_sid_fkey FOREIGN KEY (sid, metric_name, metric_type) REFERENCES stratcon.metric_name_summary(sid, metric_name, metric_type); |
---|
2095 |
|
---|
2096 |
|
---|
2097 |
-- |
---|
2098 |
-- Name: public; Type: ACL; Schema: -; Owner: postgres |
---|
2099 |
-- |
---|
2100 |
|
---|
2101 |
REVOKE ALL ON SCHEMA public FROM PUBLIC; |
---|
2102 |
REVOKE ALL ON SCHEMA public FROM postgres; |
---|
2103 |
GRANT ALL ON SCHEMA public TO postgres; |
---|
2104 |
GRANT ALL ON SCHEMA public TO PUBLIC; |
---|
2105 |
|
---|
2106 |
|
---|
2107 |
-- |
---|
2108 |
-- Name: stratcon; Type: ACL; Schema: -; Owner: stratcon |
---|
2109 |
-- |
---|
2110 |
|
---|
2111 |
REVOKE ALL ON SCHEMA stratcon FROM PUBLIC; |
---|
2112 |
REVOKE ALL ON SCHEMA stratcon FROM stratcon; |
---|
2113 |
GRANT ALL ON SCHEMA stratcon TO stratcon; |
---|
2114 |
GRANT USAGE ON SCHEMA stratcon TO prism; |
---|
2115 |
|
---|
2116 |
|
---|
2117 |
-- |
---|
2118 |
-- Name: saved_graphs; Type: ACL; Schema: prism; Owner: reconnoiter |
---|
2119 |
-- |
---|
2120 |
|
---|
2121 |
REVOKE ALL ON TABLE saved_graphs FROM PUBLIC; |
---|
2122 |
REVOKE ALL ON TABLE saved_graphs FROM reconnoiter; |
---|
2123 |
GRANT ALL ON TABLE saved_graphs TO reconnoiter; |
---|
2124 |
GRANT ALL ON TABLE saved_graphs TO prism; |
---|
2125 |
|
---|
2126 |
|
---|
2127 |
-- |
---|
2128 |
-- Name: saved_graphs_dep; Type: ACL; Schema: prism; Owner: reconnoiter |
---|
2129 |
-- |
---|
2130 |
|
---|
2131 |
REVOKE ALL ON TABLE saved_graphs_dep FROM PUBLIC; |
---|
2132 |
REVOKE ALL ON TABLE saved_graphs_dep FROM reconnoiter; |
---|
2133 |
GRANT ALL ON TABLE saved_graphs_dep TO reconnoiter; |
---|
2134 |
GRANT ALL ON TABLE saved_graphs_dep TO prism; |
---|
2135 |
|
---|
2136 |
|
---|
2137 |
SET search_path = stratcon, pg_catalog; |
---|
2138 |
|
---|
2139 |
-- |
---|
2140 |
-- Name: current_metric_text; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2141 |
-- |
---|
2142 |
|
---|
2143 |
REVOKE ALL ON TABLE current_metric_text FROM PUBLIC; |
---|
2144 |
REVOKE ALL ON TABLE current_metric_text FROM reconnoiter; |
---|
2145 |
GRANT ALL ON TABLE current_metric_text TO reconnoiter; |
---|
2146 |
GRANT SELECT ON TABLE current_metric_text TO prism; |
---|
2147 |
GRANT SELECT,INSERT,UPDATE ON TABLE current_metric_text TO stratcon; |
---|
2148 |
|
---|
2149 |
|
---|
2150 |
-- |
---|
2151 |
-- Name: current_node_config; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2152 |
-- |
---|
2153 |
|
---|
2154 |
REVOKE ALL ON TABLE current_node_config FROM PUBLIC; |
---|
2155 |
REVOKE ALL ON TABLE current_node_config FROM reconnoiter; |
---|
2156 |
GRANT ALL ON TABLE current_node_config TO reconnoiter; |
---|
2157 |
GRANT SELECT ON TABLE current_node_config TO prism; |
---|
2158 |
GRANT SELECT,INSERT,UPDATE ON TABLE current_node_config TO stratcon; |
---|
2159 |
|
---|
2160 |
|
---|
2161 |
-- |
---|
2162 |
-- Name: current_node_config_changelog; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2163 |
-- |
---|
2164 |
|
---|
2165 |
REVOKE ALL ON TABLE current_node_config_changelog FROM PUBLIC; |
---|
2166 |
REVOKE ALL ON TABLE current_node_config_changelog FROM reconnoiter; |
---|
2167 |
GRANT ALL ON TABLE current_node_config_changelog TO reconnoiter; |
---|
2168 |
GRANT SELECT ON TABLE current_node_config_changelog TO prism; |
---|
2169 |
GRANT SELECT,INSERT,UPDATE ON TABLE current_node_config_changelog TO stratcon; |
---|
2170 |
|
---|
2171 |
|
---|
2172 |
-- |
---|
2173 |
-- Name: loading_dock_check_s; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2174 |
-- |
---|
2175 |
|
---|
2176 |
REVOKE ALL ON TABLE loading_dock_check_s FROM PUBLIC; |
---|
2177 |
REVOKE ALL ON TABLE loading_dock_check_s FROM reconnoiter; |
---|
2178 |
GRANT ALL ON TABLE loading_dock_check_s TO reconnoiter; |
---|
2179 |
GRANT SELECT,INSERT,UPDATE ON TABLE loading_dock_check_s TO stratcon; |
---|
2180 |
GRANT SELECT ON TABLE loading_dock_check_s TO prism; |
---|
2181 |
|
---|
2182 |
|
---|
2183 |
-- |
---|
2184 |
-- Name: loading_dock_metric_numeric_s; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2185 |
-- |
---|
2186 |
|
---|
2187 |
REVOKE ALL ON TABLE loading_dock_metric_numeric_s FROM PUBLIC; |
---|
2188 |
REVOKE ALL ON TABLE loading_dock_metric_numeric_s FROM reconnoiter; |
---|
2189 |
GRANT ALL ON TABLE loading_dock_metric_numeric_s TO reconnoiter; |
---|
2190 |
GRANT SELECT,INSERT,UPDATE ON TABLE loading_dock_metric_numeric_s TO stratcon; |
---|
2191 |
GRANT SELECT ON TABLE loading_dock_metric_numeric_s TO prism; |
---|
2192 |
|
---|
2193 |
|
---|
2194 |
-- |
---|
2195 |
-- Name: loading_dock_metric_text_s; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2196 |
-- |
---|
2197 |
|
---|
2198 |
REVOKE ALL ON TABLE loading_dock_metric_text_s FROM PUBLIC; |
---|
2199 |
REVOKE ALL ON TABLE loading_dock_metric_text_s FROM reconnoiter; |
---|
2200 |
GRANT ALL ON TABLE loading_dock_metric_text_s TO reconnoiter; |
---|
2201 |
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE loading_dock_metric_text_s TO stratcon; |
---|
2202 |
GRANT SELECT ON TABLE loading_dock_metric_text_s TO prism; |
---|
2203 |
|
---|
2204 |
|
---|
2205 |
-- |
---|
2206 |
-- Name: loading_dock_metric_text_s_change_log; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2207 |
-- |
---|
2208 |
|
---|
2209 |
REVOKE ALL ON TABLE loading_dock_metric_text_s_change_log FROM PUBLIC; |
---|
2210 |
REVOKE ALL ON TABLE loading_dock_metric_text_s_change_log FROM reconnoiter; |
---|
2211 |
GRANT ALL ON TABLE loading_dock_metric_text_s_change_log TO reconnoiter; |
---|
2212 |
GRANT SELECT,INSERT,UPDATE ON TABLE loading_dock_metric_text_s_change_log TO stratcon; |
---|
2213 |
GRANT SELECT ON TABLE loading_dock_metric_text_s_change_log TO prism; |
---|
2214 |
|
---|
2215 |
|
---|
2216 |
-- |
---|
2217 |
-- Name: loading_dock_status_s; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2218 |
-- |
---|
2219 |
|
---|
2220 |
REVOKE ALL ON TABLE loading_dock_status_s FROM PUBLIC; |
---|
2221 |
REVOKE ALL ON TABLE loading_dock_status_s FROM reconnoiter; |
---|
2222 |
GRANT ALL ON TABLE loading_dock_status_s TO reconnoiter; |
---|
2223 |
GRANT SELECT,INSERT,UPDATE ON TABLE loading_dock_status_s TO stratcon; |
---|
2224 |
GRANT SELECT ON TABLE loading_dock_status_s TO prism; |
---|
2225 |
|
---|
2226 |
|
---|
2227 |
-- |
---|
2228 |
-- Name: loading_dock_status_s_change_log; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2229 |
-- |
---|
2230 |
|
---|
2231 |
REVOKE ALL ON TABLE loading_dock_status_s_change_log FROM PUBLIC; |
---|
2232 |
REVOKE ALL ON TABLE loading_dock_status_s_change_log FROM reconnoiter; |
---|
2233 |
GRANT ALL ON TABLE loading_dock_status_s_change_log TO reconnoiter; |
---|
2234 |
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE loading_dock_status_s_change_log TO stratcon; |
---|
2235 |
GRANT SELECT ON TABLE loading_dock_status_s_change_log TO prism; |
---|
2236 |
|
---|
2237 |
|
---|
2238 |
-- |
---|
2239 |
-- Name: log_whence_s; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2240 |
-- |
---|
2241 |
|
---|
2242 |
REVOKE ALL ON TABLE log_whence_s FROM PUBLIC; |
---|
2243 |
REVOKE ALL ON TABLE log_whence_s FROM reconnoiter; |
---|
2244 |
GRANT ALL ON TABLE log_whence_s TO reconnoiter; |
---|
2245 |
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE log_whence_s TO stratcon; |
---|
2246 |
GRANT SELECT ON TABLE log_whence_s TO prism; |
---|
2247 |
|
---|
2248 |
|
---|
2249 |
-- |
---|
2250 |
-- Name: map_uuid_to_sid; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2251 |
-- |
---|
2252 |
|
---|
2253 |
REVOKE ALL ON TABLE map_uuid_to_sid FROM PUBLIC; |
---|
2254 |
REVOKE ALL ON TABLE map_uuid_to_sid FROM reconnoiter; |
---|
2255 |
GRANT ALL ON TABLE map_uuid_to_sid TO reconnoiter; |
---|
2256 |
GRANT SELECT,INSERT,UPDATE ON TABLE map_uuid_to_sid TO stratcon; |
---|
2257 |
GRANT SELECT ON TABLE map_uuid_to_sid TO prism; |
---|
2258 |
|
---|
2259 |
|
---|
2260 |
-- |
---|
2261 |
-- Name: metric_name_summary; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2262 |
-- |
---|
2263 |
|
---|
2264 |
REVOKE ALL ON TABLE metric_name_summary FROM PUBLIC; |
---|
2265 |
REVOKE ALL ON TABLE metric_name_summary FROM reconnoiter; |
---|
2266 |
GRANT ALL ON TABLE metric_name_summary TO reconnoiter; |
---|
2267 |
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE metric_name_summary TO stratcon; |
---|
2268 |
GRANT SELECT ON TABLE metric_name_summary TO prism; |
---|
2269 |
|
---|
2270 |
|
---|
2271 |
-- |
---|
2272 |
-- Name: mv_loading_dock_check_s; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2273 |
-- |
---|
2274 |
|
---|
2275 |
REVOKE ALL ON TABLE mv_loading_dock_check_s FROM PUBLIC; |
---|
2276 |
REVOKE ALL ON TABLE mv_loading_dock_check_s FROM reconnoiter; |
---|
2277 |
GRANT ALL ON TABLE mv_loading_dock_check_s TO reconnoiter; |
---|
2278 |
GRANT SELECT ON TABLE mv_loading_dock_check_s TO prism; |
---|
2279 |
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE mv_loading_dock_check_s TO stratcon; |
---|
2280 |
|
---|
2281 |
|
---|
2282 |
-- |
---|
2283 |
-- Name: rollup_matrix_numeric_12hours; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2284 |
-- |
---|
2285 |
|
---|
2286 |
REVOKE ALL ON TABLE rollup_matrix_numeric_12hours FROM PUBLIC; |
---|
2287 |
REVOKE ALL ON TABLE rollup_matrix_numeric_12hours FROM reconnoiter; |
---|
2288 |
GRANT ALL ON TABLE rollup_matrix_numeric_12hours TO reconnoiter; |
---|
2289 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_12hours TO stratcon; |
---|
2290 |
GRANT SELECT ON TABLE rollup_matrix_numeric_12hours TO prism; |
---|
2291 |
|
---|
2292 |
|
---|
2293 |
-- |
---|
2294 |
-- Name: rollup_matrix_numeric_20m; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2295 |
-- |
---|
2296 |
|
---|
2297 |
REVOKE ALL ON TABLE rollup_matrix_numeric_20m FROM PUBLIC; |
---|
2298 |
REVOKE ALL ON TABLE rollup_matrix_numeric_20m FROM reconnoiter; |
---|
2299 |
GRANT ALL ON TABLE rollup_matrix_numeric_20m TO reconnoiter; |
---|
2300 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_20m TO stratcon; |
---|
2301 |
GRANT SELECT ON TABLE rollup_matrix_numeric_20m TO prism; |
---|
2302 |
|
---|
2303 |
|
---|
2304 |
-- |
---|
2305 |
-- Name: rollup_matrix_numeric_5m; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2306 |
-- |
---|
2307 |
|
---|
2308 |
REVOKE ALL ON TABLE rollup_matrix_numeric_5m FROM PUBLIC; |
---|
2309 |
REVOKE ALL ON TABLE rollup_matrix_numeric_5m FROM reconnoiter; |
---|
2310 |
GRANT ALL ON TABLE rollup_matrix_numeric_5m TO reconnoiter; |
---|
2311 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_5m TO stratcon; |
---|
2312 |
GRANT SELECT ON TABLE rollup_matrix_numeric_5m TO prism; |
---|
2313 |
|
---|
2314 |
|
---|
2315 |
-- |
---|
2316 |
-- Name: rollup_matrix_numeric_60m; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2317 |
-- |
---|
2318 |
|
---|
2319 |
REVOKE ALL ON TABLE rollup_matrix_numeric_60m FROM PUBLIC; |
---|
2320 |
REVOKE ALL ON TABLE rollup_matrix_numeric_60m FROM reconnoiter; |
---|
2321 |
GRANT ALL ON TABLE rollup_matrix_numeric_60m TO reconnoiter; |
---|
2322 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_60m TO stratcon; |
---|
2323 |
GRANT SELECT ON TABLE rollup_matrix_numeric_60m TO prism; |
---|
2324 |
|
---|
2325 |
|
---|
2326 |
-- |
---|
2327 |
-- Name: rollup_matrix_numeric_6hours; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2328 |
-- |
---|
2329 |
|
---|
2330 |
REVOKE ALL ON TABLE rollup_matrix_numeric_6hours FROM PUBLIC; |
---|
2331 |
REVOKE ALL ON TABLE rollup_matrix_numeric_6hours FROM reconnoiter; |
---|
2332 |
GRANT ALL ON TABLE rollup_matrix_numeric_6hours TO reconnoiter; |
---|
2333 |
GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_6hours TO stratcon; |
---|
2334 |
GRANT SELECT ON TABLE rollup_matrix_numeric_6hours TO prism; |
---|
2335 |
|
---|
2336 |
|
---|
2337 |
-- |
---|
2338 |
-- Name: rollup_runner; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2339 |
-- |
---|
2340 |
|
---|
2341 |
REVOKE ALL ON TABLE rollup_runner FROM PUBLIC; |
---|
2342 |
REVOKE ALL ON TABLE rollup_runner FROM reconnoiter; |
---|
2343 |
GRANT ALL ON TABLE rollup_runner TO reconnoiter; |
---|
2344 |
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE rollup_runner TO stratcon; |
---|
2345 |
GRANT SELECT ON TABLE rollup_runner TO prism; |
---|
2346 |
|
---|
2347 |
|
---|
2348 |
-- |
---|
2349 |
-- Name: seq_sid; Type: ACL; Schema: stratcon; Owner: reconnoiter |
---|
2350 |
-- |
---|
2351 |
|
---|
2352 |
REVOKE ALL ON SEQUENCE seq_sid FROM PUBLIC; |
---|
2353 |
REVOKE ALL ON SEQUENCE seq_sid FROM reconnoiter; |
---|
2354 |
GRANT ALL ON SEQUENCE seq_sid TO reconnoiter; |
---|
2355 |
GRANT ALL ON SEQUENCE seq_sid TO stratcon; |
---|
2356 |
|
---|
2357 |
|
---|
2358 |
-- |
---|
2359 |
-- PostgreSQL database dump complete |
---|
2360 |
-- |
---|
2361 |
|
---|