root/sql/reconnoiter_ddl_dump.sql

Revision 5366a70920df98ecf37fed906dfe718d250c4ec1, 55.4 kB (checked in by Denish Patel <denish@omniti.com>, 6 years ago)

Exported Latest DDL dump

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