root/sql/reconnoiter_ddl_dump.sql

Revision 4aae95d137f38b64f49d27c9611b1fba6242666a, 54.2 kB (checked in by Denish Patel <denish@omniti.com>, 6 years ago)

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