root/sql/schema.sql

Revision 152d473243c4c0e838439fd654a698515e8288fc, 32.3 kB (checked in by Denish Patel <denish@omniti.com>, 6 years ago)

stddev_value removed.

  • Property mode set to 100644
Line 
1 BEGIN;
2
3 -- Tables
4
5 CREATE TABLE stratcon.loading_dock_check_s (
6     sid integer NOT NULL,
7     remote_address inet,
8     whence timestamp NOT NULL,
9     id uuid NOT NULL,
10     target text NOT NULL,
11     module text NOT NULL,
12     name text NOT NULL,
13     PRIMARY KEY(sid,id,whence)
14 );
15
16 CREATE TABLE stratcon.loading_dock_status_s (
17     sid integer NOT NULL,
18     whence timestamp NOT NULL,
19     state character(1) NOT NULL,
20     availability character(1) NOT NULL,
21     duration integer NOT NULL,
22     status text,
23     PRIMARY KEY(sid,whence)
24 );
25
26 CREATE TABLE stratcon.loading_dock_status_s_change_log (
27     sid integer NOT NULL,
28     whence timestamp NOT NULL,
29     state character(1) NOT NULL,
30     availability character(1) NOT NULL,
31     duration integer NOT NULL,
32     status text,
33     PRIMARY KEY(sid,whence)
34 );
35
36
37 CREATE TABLE stratcon.loading_dock_metric_text_s (
38     sid integer NOT NULL,
39     whence timestamp NOT NULL,
40     name text NOT NULL,
41     value text,
42     PRIMARY KEY(whence,sid,name)
43 );
44
45 CREATE TABLE stratcon.loading_dock_metric_text_s_change_log (
46     sid integer NOT NULL,
47     whence timestamp NOT NULL,
48     name text NOT NULL,
49     value text,
50     PRIMARY KEY(whence,sid,name)
51 );
52
53 CREATE TABLE stratcon.loading_dock_metric_numeric_s (
54     sid integer NOT NULL,
55     whence timestamp NOT NULL,
56     name text NOT NULL,
57     value numeric,
58     PRIMARY KEY(whence,sid,name)
59 );
60
61 CREATE TABLE stratcon.rollup_matrix_numeric_5m (
62     sid integer NOT NULL,
63     name text NOT NULL,
64     rollup_time timestamp NOT NULL,
65     count_rows integer,
66     avg_value numeric,
67     min_value numeric,
68     max_value numeric,
69     PRIMARY KEY (rollup_time,sid,name)
70 );
71
72 CREATE TABLE stratcon.rollup_matrix_numeric_20m (
73     sid integer NOT NULL,
74     name text NOT NULL,
75     rollup_time timestamp NOT NULL,
76     count_rows integer,
77     avg_value numeric,
78     min_value numeric,
79     max_value numeric,
80     PRIMARY KEY (rollup_time,sid,name)
81 );
82
83 CREATE TABLE stratcon.rollup_matrix_numeric_60m(
84    sid integer not null,
85    name text not null,
86    rollup_time timestamp not null,
87    count_rows integer,
88    avg_value numeric ,
89    min_value numeric ,
90    max_value numeric ,
91    PRIMARY KEY(rollup_time,sid,name));
92    
93 CREATE TABLE stratcon.rollup_matrix_numeric_6hours(
94    sid integer not null,
95    name text not null,
96    rollup_time timestamp not null,
97    count_rows integer,
98    avg_value numeric ,
99    min_value numeric ,
100    max_value numeric ,
101    PRIMARY KEY(rollup_time6,sid,name));   
102
103 CREATE TABLE stratcon.rollup_matrix_numeric_12hours(
104    sid integer not null,
105    name text not null,
106    rollup_time timestamp not null,
107    count_rows integer,
108    avg_value numeric ,
109    min_value numeric ,
110    max_value numeric ,
111    PRIMARY KEY(rollup_time12,sid,name));     
112  
113 CREATE TABLE stratcon.map_uuid_to_sid (
114     id uuid NOT NULL,
115     sid integer NOT NULL,
116     PRIMARY KEY(id,sid)
117 );
118
119 CREATE TABLE stratcon.log_whence_s (
120     whence timestamp NOT NULL,
121     interval varchar2(20,
122     PRIMARY KEY(whence,interval)
123 );
124
125 CREATE TABLE stratcon.rollup_runner (
126   rollup_table character varying(100),
127   runner character varying(22)
128 );
129
130 CREATE TABLE stratcon.metric_name_summary (
131   sid integer NOT NULL,
132   metric_name text NOT NULL,
133   metric_type character varying(22),
134   active boolean default 'true',
135   PRIMARY KEY (sid,name)
136 );
137 -- Schema Sequence
138
139 CREATE SEQUENCE stratcon.seq_sid
140     START WITH 50
141     INCREMENT BY 1
142     NO MAXVALUE
143     NO MINVALUE
144     CACHE 1;
145
146
147
148 -- GRANTS
149
150  GRANT SELECT,INSERT ON stratcon.loading_dock_status_s TO stratcon;
151  GRANT SELECT,INSERT ON stratcon.loading_dock_status_s_change_log TO stratcon;
152  GRANT SELECT,INSERT ON stratcon.loading_dock_check_s TO stratcon;
153  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric_s TO stratcon;
154  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s_change_log TO stratcon;
155  GRANT SELECT,INSERT,DELETE ON stratcon.log_whence_s TO stratcon;
156  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s TO stratcon;
157  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_60m TO stratcon;
158  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_5m TO stratcon;
159  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_20m TO stratcon;
160  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_6hours TO stratcon;
161  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_12hours TO stratcon;
162  GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon;
163  GRANT SELECT,INSERT,UPDATE,DELETE ON stratcon.rollup_runner TO stratcon;
164  GRANT SELECT,INSERT,UPDATE,DELETE ON stratcon.metric_name_summary TO stratcon;
165  ALTER TABLE stratcon.seq_sid OWNER TO stratcon;
166  
167  
168 -- Function To generate SID from ID
169
170 CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid)
171 RETURNS integer
172 AS $$
173 DECLARE
174    v_ex_sid integer;
175    v_new_sid integer;
176  
177 BEGIN
178
179 SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id
180   INTO v_ex_sid;
181
182  IF NOT FOUND THEN
183     SELECT nextval('stratcon.seq_sid')
184     INTO v_new_sid;
185
186     INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid);
187    
188     RETURN v_new_sid;
189  ELSE
190       RETURN v_ex_sid;
191  END IF;
192
193 END
194 $$ LANGUAGE plpgsql;
195
196 -- Trigger Function to log dock status Changes
197
198 CREATE  TRIGGER loading_dock_status_s_change_log
199     AFTER INSERT ON stratcon.loading_dock_status_s
200     FOR EACH ROW
201     EXECUTE PROCEDURE stratcon.loading_dock_status_s_change_log();
202
203
204 CREATE OR REPLACE FUNCTION stratcon.loading_dock_status_s_change_log() RETURNS trigger
205     AS $$
206 DECLARE
207     v_state CHAR(1);
208     v_avail CHAR(1);
209 BEGIN
210
211 IF TG_OP = 'INSERT' THEN
212     SELECT state,availability FROM  stratcon.loading_dock_status_s WHERE sid = NEW.sid
213         AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log
214                         WHERE  SID=NEW.sid and  WHENCE <> NEW.whence )
215     INTO v_state,v_avail;
216
217     IF v_state IS DISTINCT FROM NEW.state OR v_avail IS DISTINCT FROM NEW.availability THEN
218
219         INSERT INTO stratcon.loading_dock_status_s_change_log (sid,whence,state,availability,duration,status)
220             VALUES (NEW.sid,NEW.whence,NEW.state,NEW.availability,NEW.duration,NEW.status);
221
222     END IF;
223
224 ELSE
225         RAISE EXCEPTION 'Something wrong with stratcon.loading_dock_status_s_change_log';
226 END IF;
227
228     RETURN NULL;
229
230 END
231 $$
232     LANGUAGE plpgsql;
233
234
235 -- Trigger Function to log Metrix Text Changes
236
237 CREATE TRIGGER loading_dock_metric_text_s_change_log
238     AFTER INSERT ON stratcon.loading_dock_metric_text_s
239     FOR EACH ROW
240     EXECUTE PROCEDURE stratcon.loading_dock_metric_text_s_change_log();
241
242
243 CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger
244     AS $$
245 DECLARE
246     v_oldvalue TEXT;
247     v_sid integer;
248     v_name text;
249 BEGIN
250
251 IF TG_OP = 'INSERT' THEN
252     SELECT value FROM  stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name
253         AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log
254                         WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name )
255     INTO v_oldvalue;
256
257     IF v_oldvalue IS DISTINCT FROM NEW.value THEN
258
259         INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value)
260             VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);
261     END IF;
262    
263     SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name
264          INTO v_sid,v_name;
265        IF NOT FOUND THEN
266            INSERT INTO  stratcon.metric_name_summary(sid,metric_name,metric_type)  VALUES(NEW.sid,NEW.name,'text');
267     END IF;
268
269 ELSE
270         RAISE EXCEPTION 'something wrong with stratcon.loading_dock_metric_text_s_change_log ';
271 END IF;
272
273     RETURN NULL;
274
275 END
276 $$
277     LANGUAGE plpgsql;
278    
279 -- Trigger on Metrix Numeric to log last inserted timestamp
280
281 CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log()
282 RETURNS trigger
283 AS $$
284 DECLARE
285 v_whence timestamptz;
286 v_sid integer;
287 v_name text;
288 BEGIN
289 IF TG_OP = 'INSERT' THEN
290    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval and interval='5 minutes'
291      INTO v_whence;
292    IF NOT FOUND THEN
293        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval,'5 minutes');
294     END IF;
295
296    SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name
297      INTO v_sid,v_name;
298    IF NOT FOUND THEN
299        INSERT INTO  stratcon.metric_name_summary VALUES(NEW.sid,NEW.name,'numeric');
300     END IF;
301
302 END IF;
303
304     RETURN NULL;
305 END
306 $$
307     LANGUAGE plpgsql;
308
309 -- 5 minutes rollup
310
311 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m()
312 RETURNS void
313 AS $$
314 DECLARE
315  
316  rec stratcon.rollup_matrix_numeric_5m%rowtype;
317  v_sql TEXT;
318  v_min_whence TIMESTAMP;
319  v_max_rollup_5 TIMESTAMP;
320  v_whence TIMESTAMP;
321  rows INT;
322  v_nrunning INT;
323  v_self VARCHAR(22);
324  
325 BEGIN
326
327   SELECT COUNT(1) INTO v_nrunning
328     from stratcon.rollup_runner t, pg_stat_activity a
329    where rollup_table ='rollup_matrix_numeric_5m'
330      and runner = procpid || '.' || date_part('epoch',backend_start);
331
332   IF v_nrunning > 0 THEN
333     RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running';
334     RETURN ;
335   END IF;
336
337   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
338     FROM pg_stat_activity
339    WHERE procpid = pg_backend_pid();
340
341   IF v_self IS NULL THEN
342     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot self-identify';
343   END IF;
344
345   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_5m''';
346
347   EXECUTE v_sql;
348
349  SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes'
350         INTO v_min_whence;
351        
352  SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_5m
353          INTO v_max_rollup_5;       
354  
355  -- Insert Log for 20 minutes rollup
356    
357    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'
358            INTO v_whence;
359       IF NOT FOUND THEN
360        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');
361    END IF;
362    
363  IF v_min_whence <= v_max_rollup_5 THEN
364
365    DELETE FROM stratcon.rollup_matrix_numeric_5m
366                 WHERE rollup_time = v_min_whence;
367  
368  END IF;
369
370  FOR rec IN
371                 SELECT sid , name,v_min_whence as rollup_time,
372                       COUNT(1) as count_rows ,AVG(value) as avg_value,MIN(value) as min_value ,MAX(value) as max_value
373                       FROM stratcon.loading_dock_metric_numeric_s
374                       WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval
375                 GROUP BY rollup_time,sid,name
376  
377        LOOP
378    
379        
380         INSERT INTO stratcon.rollup_matrix_numeric_5m
381          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
382          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
383        
384    END LOOP;
385
386   -- Delete from whence log table
387  
388   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes';
389  
390   UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
391  
392 RETURN;
393
394 EXCEPTION
395     WHEN RAISE_EXCEPTION THEN
396        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
397        RAISE EXCEPTION '%', SQLERRM;
398     WHEN OTHERS THEN
399       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
400       RAISE NOTICE '%', SQLERRM;
401 END
402 $$ LANGUAGE plpgsql;
403
404
405 -- 20 minutes rollup
406
407 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_20m()
408 RETURNS void
409 AS $$
410 DECLARE
411  
412  rec stratcon.rollup_matrix_numeric_20m%rowtype;
413  v_sql TEXT;
414  v_min_whence TIMESTAMP;
415  v_max_rollup_20 TIMESTAMP;
416  v_whence TIMESTAMP;
417  rows INT;
418  v_nrunning INT;
419  v_self VARCHAR(22);
420
421 BEGIN
422
423   SELECT COUNT(1) INTO v_nrunning
424     from stratcon.rollup_runner t, pg_stat_activity a
425    where rollup_table ='rollup_matrix_numeric_20m'
426      and runner = procpid || '.' || date_part('epoch',backend_start);
427
428   IF v_nrunning > 0 THEN
429     RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running';
430     RETURN ;
431   END IF;
432
433   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
434     FROM pg_stat_activity
435    WHERE procpid = pg_backend_pid();
436
437   IF v_self IS NULL THEN
438     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot self-identify';
439   END IF;
440
441   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_20m''';
442
443   EXECUTE v_sql;
444
445  SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='20 minutes'
446         INTO v_min_whence;
447        
448  SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_20m
449          INTO v_max_rollup_20;       
450  
451  -- Insert Log for Hourly rollup
452    
453    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour'
454            INTO v_whence;
455       IF NOT FOUND THEN
456        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour');
457    END IF;
458    
459  IF v_min_whence <= v_max_rollup_20 THEN
460
461    DELETE FROM stratcon.rollup_matrix_numeric_20m
462                 WHERE rollup_time = v_min_whence;
463  
464  END IF;
465
466  FOR rec IN
467                 SELECT sid , name,v_min_whence as rollup_time,
468                        SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value,
469                        MIN(min_value) as min_value ,MAX(max_value) as max_value
470                        FROM stratcon.rollup_matrix_numeric_5m
471                       WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval
472                 GROUP BY sid,name
473  
474        LOOP
475    
476        
477         INSERT INTO stratcon.rollup_matrix_numeric_20m
478          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
479          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
480        
481    END LOOP;
482
483   -- Delete from whence log table
484  
485   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='20 minutes';
486  
487   UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
488  
489 RETURN;
490
491 EXCEPTION
492     WHEN RAISE_EXCEPTION THEN
493        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
494        RAISE EXCEPTION '%', SQLERRM;
495     WHEN OTHERS THEN
496       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
497       RAISE NOTICE '%', SQLERRM;
498 END
499 $$ LANGUAGE plpgsql;
500
501
502 -- 1 hour rollup
503
504
505 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m()
506 RETURNS void
507 AS $$
508 DECLARE
509   rec stratcon.rollup_matrix_numeric_60m%rowtype;
510   v_sql TEXT;
511   v_min_whence TIMESTAMP;
512   v_max_rollup_60 TIMESTAMP;
513   v_whence TIMESTAMP;
514   v_nrunning INT;
515   v_self VARCHAR(22);
516
517 BEGIN
518
519   SELECT COUNT(1) INTO v_nrunning
520     from stratcon.rollup_runner t, pg_stat_activity a
521    where rollup_table ='rollup_matrix_numeric_60m'
522      and runner = procpid || '.' || date_part('epoch',backend_start);
523
524   IF v_nrunning > 0 THEN
525     RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running';
526     RETURN ;
527   END IF;
528
529   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
530     FROM pg_stat_activity
531    WHERE procpid = pg_backend_pid();
532
533   IF v_self IS NULL THEN
534     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot self-identify';
535   END IF;
536
537   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_60m''';
538
539   EXECUTE v_sql;
540
541   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour'
542          INTO v_min_whence;
543          
544   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_60m
545          INTO v_max_rollup_60;   
546
547 -- Insert Log for 6 Hour rollup
548    
549    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'
550            INTO v_whence;
551       IF NOT FOUND THEN
552        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');
553    END IF;
554    
555    
556   IF v_min_whence <= v_max_rollup_60 THEN
557  
558   DELETE FROM stratcon.rollup_matrix_numeric_60m
559        WHERE rollup_time= v_min_whence;
560
561   END IF;
562  
563     FOR rec IN
564                 SELECT sid,name,date_trunc('hour',rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value,
565                          MIN(min_value) as min_value ,MAX(max_value) as max_value
566                          FROM stratcon.rollup_matrix_numeric_20m
567                            WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence)
568                    GROUP BY date_trunc('hour',rollup_time),sid,name
569         LOOP
570      
571           INSERT INTO stratcon.rollup_matrix_numeric_60m
572           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
573           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
574          
575      END LOOP;
576
577
578 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour';
579
580 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
581
582 RETURN;
583
584 EXCEPTION
585     WHEN RAISE_EXCEPTION THEN
586        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
587        RAISE EXCEPTION '%', SQLERRM;
588     WHEN OTHERS THEN
589       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
590       RAISE NOTICE '%', SQLERRM;
591 END
592 $$ LANGUAGE plpgsql;
593
594
595 -- 6 hours
596
597 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_6hours()
598 RETURNS void
599 AS $$
600 DECLARE
601   rec stratcon.rollup_matrix_numeric_6hours%rowtype;
602   v_sql TEXT;
603   v_min_whence TIMESTAMP;
604   v_max_rollup_6 TIMESTAMP;
605   v_whence TIMESTAMP;
606   v_nrunning INT;
607   v_self VARCHAR(22);
608    
609 BEGIN
610
611   SELECT COUNT(1) INTO v_nrunning
612     from stratcon.rollup_runner t, pg_stat_activity a
613     where rollup_table ='rollup_matrix_numeric_6hours'
614      and runner = procpid || '.' || date_part('epoch',backend_start);
615
616   IF v_nrunning > 0 THEN
617     RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running';
618     RETURN ;
619   END IF;
620
621   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
622     FROM pg_stat_activity
623      WHERE procpid = pg_backend_pid();
624
625   IF v_self IS NULL THEN
626     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot self-identify';
627    END IF;
628
629    v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_6hours''';
630
631   EXECUTE v_sql;
632
633   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours'
634          INTO v_min_whence;
635          
636   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_6hours
637          INTO v_max_rollup_6;   
638
639 -- Insert Log for 12 Hours rollup
640    
641    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'
642            INTO v_whence;
643       IF NOT FOUND THEN
644        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');
645    END IF;
646    
647    
648   IF v_min_whence <= v_max_rollup_6 THEN
649  
650   DELETE FROM stratcon.rollup_matrix_numeric_6hours
651        WHERE rollup_time= v_min_whence;
652
653   END IF;
654  
655     FOR rec IN
656                 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,
657                          MIN(min_value) as min_value ,MAX(max_value) as max_value
658                          FROM stratcon.rollup_matrix_numeric_60m
659                            WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval
660                    GROUP BY sid,name
661         LOOP
662      
663        
664           INSERT INTO stratcon.rollup_matrix_numeric_6hours
665           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
666           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
667          
668      END LOOP;
669
670
671 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours';
672
673 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
674
675 RETURN;
676
677 EXCEPTION
678     WHEN RAISE_EXCEPTION THEN
679        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
680        RAISE EXCEPTION '%', SQLERRM;
681     WHEN OTHERS THEN
682        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
683        RAISE NOTICE '%', SQLERRM;
684 END
685 $$ LANGUAGE plpgsql;
686
687
688 -- 12 hours
689
690 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_12hours()
691 RETURNS void
692 AS $$
693 DECLARE
694   rec stratcon.rollup_matrix_numeric_12hours%rowtype;
695   v_sql TEXT;
696   v_min_whence TIMESTAMP;
697   v_max_rollup_12 TIMESTAMP;
698   v_whence TIMESTAMP;
699   v_nrunning INT;
700   v_self VARCHAR(22);
701  
702  
703 BEGIN
704
705   SELECT COUNT(1) INTO v_nrunning
706     from stratcon.rollup_runner t, pg_stat_activity a
707     where rollup_table ='rollup_matrix_numeric_12hours'
708      and runner = procpid || '.' || date_part('epoch',backend_start);
709
710   IF v_nrunning > 0 THEN
711     RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running';
712     RETURN ;
713   END IF;
714
715   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
716     FROM pg_stat_activity
717      WHERE procpid = pg_backend_pid();
718
719   IF v_self IS NULL THEN
720     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot self-identify';
721    END IF;
722
723    v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_12hours''';
724
725   EXECUTE v_sql;
726
727   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours'
728          INTO v_min_whence;
729          
730   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_12hours
731          INTO v_max_rollup_12;   
732
733 /*-- Insert Log for 24 Hours rollup
734    
735    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'
736            INTO v_whence;
737       IF NOT FOUND THEN
738        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');
739    END IF;
740    */
741    
742   IF v_min_whence <= v_max_rollup_12 THEN
743  
744   DELETE FROM stratcon.rollup_matrix_numeric_12hours
745        WHERE rollup_time= v_min_whence;
746
747   END IF;
748  
749     FOR rec IN
750                 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,
751                          MIN(min_value) as min_value ,MAX(max_value) as max_value
752                          FROM stratcon.rollup_matrix_numeric_6hours
753                            WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval
754                    GROUP BY sid,name
755         LOOP
756      
757        
758           INSERT INTO stratcon.rollup_matrix_numeric_12hours
759           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
760           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
761          
762      END LOOP;
763
764
765 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours';
766
767 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_12hours';
768
769 RETURN;
770
771 EXCEPTION
772     WHEN RAISE_EXCEPTION THEN
773       UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours';
774       RAISE EXCEPTION '%', SQLERRM;
775     WHEN OTHERS THEN
776       UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours';
777       RAISE NOTICE '%', SQLERRM;
778 END
779 $$ LANGUAGE plpgsql;
780
781
782 create or replace function
783 stratcon.fetch_varset(in_check uuid,
784                        in_name text,
785                        in_start_time timestamp,
786                        in_end_time timestamp,
787                        in_hopeful_nperiods int)
788 returns setof stratcon.loading_dock_metric_text_s_change_log as
789 $$
790 declare
791   v_sid int;
792   v_target record;
793   v_start_adj timestamp;
794   v_start_text text;
795   v_next_text text;
796   v_end_adj timestamp;
797   v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype;
798 begin
799   -- Map out uuid to an sid.
800   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check;
801   if not found then
802     return;
803   end if;
804
805   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
806
807   select 'epoch'::timestamp +
808          ((floor(extract('epoch' from in_start_time) /
809                  extract('epoch' from v_target.period)) *
810            extract('epoch' from v_target.period)) || ' seconds') ::interval
811     into v_start_adj;
812
813   select 'epoch'::timestamp +
814          ((floor(extract('epoch' from in_end_time) /
815                  extract('epoch' from v_target.period)) *
816            extract('epoch' from v_target.period)) || ' seconds') ::interval
817     into v_end_adj;
818
819   for v_change_row in
820     select sid, 'epoch'::timestamp +
821          ((floor(extract('epoch' from whence) /
822                  extract('epoch' from v_target.period)) *
823            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
824            name, value
825       from stratcon.loading_dock_metric_text_s_change_log
826      where sid = v_sid
827        and name = in_name
828        and whence <= v_start_adj
829   order by 'epoch'::timestamp +
830          ((floor(extract('epoch' from whence) /
831                  extract('epoch' from v_target.period)) *
832            extract('epoch' from v_target.period)) || ' seconds') ::interval desc
833      limit 1
834   loop
835     v_start_text := coalesce(v_change_row.value, '[unset]');
836   end loop;
837
838   for v_change_row in
839     select v_sid as sid, whence, in_name as name, value from
840 --    (select v_start_adj::timestamp + t * v_target.period::interval as whence
841 --      from generate_series(1, v_target.nperiods) t) s
842 -- left join
843     (select 'epoch'::timestamp +
844          ((floor(extract('epoch' from whence) /
845                  extract('epoch' from v_target.period)) *
846            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
847            coalesce(value, '[unset]') as value
848       from stratcon.loading_dock_metric_text_s_change_log
849      where sid = v_sid
850        and name = in_name
851        and whence > v_start_adj
852        and whence <= v_end_adj) d
853 --    using (whence)
854   order by whence asc
855   loop
856     v_next_text := v_change_row.value;
857     if v_change_row.value is not null and
858        v_start_text != v_change_row.value then
859       v_change_row.value := coalesce(v_start_text, '[unset]') || ' -> ' || coalesce(v_change_row.value, '[unset]');
860     else
861       v_change_row.value := v_start_text;
862     end if;
863     if v_next_text is not null then
864       v_start_text := v_next_text;
865     end if;
866     return next v_change_row;
867   end loop;
868
869   return;
870 end
871 $$ language 'plpgsql';
872
873
874 create or replace function
875 stratcon.choose_window(in_start_time timestamp,
876                        in_end_time timestamp,
877                        in_hopeful_nperiods int,
878                        out tablename text,
879                        out period interval,
880                        out nperiods int)
881 returns setof record as
882 $$
883 declare
884   window record;
885 begin
886   -- Figure out which table we should be looking in
887   for window in
888     select atablename, aperiod, anperiods
889     from (select aperiod, iv/isec as anperiods, atablename,
890                  abs(case when iv/isec - in_hopeful_nperiods < 0
891                           then 10 * (in_hopeful_nperiods - iv/isec)
892                           else iv/isec - in_hopeful_nperiods
893                            end) as badness
894             from (select extract('epoch' from in_end_time) -
895                          extract('epoch' from in_start_time) as iv
896                  ) i,
897                  (   select 5*60 as isec, '5 minutes'::interval as aperiod,
898                             'rollup_matrix_numeric_5m' as atablename
899                   union all
900                      select 20*60 as isec, '20 minutes'::interval as aperiod,
901                             'rollup_matrix_numeric_20m' as atablename
902                   union all
903                      select 60*60 as isec, '1 hour'::interval as aperiod,
904                             'rollup_matrix_numeric_60m' as atablename
905                   union all
906                      select 6*60*60 as isec, '6 hours'::interval as aaperiod,
907                             'rollup_matrix_numeric_6hours' as atablename
908                   union all
909                      select 12*60*60 as isec, '12 hours'::interval as aperiod,
910                             'rollup_matrix_numeric_12hours' as atablename
911                  ) ivs
912          ) b
913  order by badness asc
914   limit 1
915   loop
916     tablename := window.atablename;
917     period := window.aperiod;
918     nperiods := window.anperiods;
919     return next;
920   end loop;
921   return;
922 end
923 $$ language 'plpgsql';
924
925 create or replace function
926 stratcon.fetch_dataset(in_check uuid,
927                        in_name text,
928                        in_start_time timestamp,
929                        in_end_time timestamp,
930                        in_hopeful_nperiods int,
931                        derive boolean)
932 returns setof stratcon.rollup_matrix_numeric_5m as
933 $$
934 declare
935   v_sql text;
936   v_sid int;
937   v_target record;
938   v_interval numeric;
939   v_start_adj timestamp;
940   v_end_adj timestamp;
941   v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
942   v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
943   v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
944 begin
945
946   -- Map out uuid to an sid.
947   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check;
948   if not found then
949     return;
950   end if;
951
952   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
953
954   if not found then
955     raise exception 'no target table';
956     return;
957   end if;
958
959   select 'epoch'::timestamp +
960          ((floor(extract('epoch' from in_start_time) /
961                  extract('epoch' from v_target.period)) *
962            extract('epoch' from v_target.period)) || ' seconds') ::interval
963     into v_start_adj;
964
965   select 'epoch'::timestamp +
966          ((floor(extract('epoch' from in_end_time) /
967                  extract('epoch' from v_target.period)) *
968            extract('epoch' from v_target.period)) || ' seconds') ::interval
969     into v_end_adj;
970
971   v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' ||
972            's.rollup_time, d.count_rows, d.avg_value, ' ||
973            'd.min_value, d.max_value ' ||
974            ' from ' ||
975            '(select ' || quote_literal(v_start_adj) || '::timestamp' ||
976                   ' + t * ' || quote_literal(v_target.period) || '::interval' ||
977                        ' as rollup_time' ||
978              ' from generate_series(1,' || v_target.nperiods || ') t) s ' ||
979            'left join ' ||
980            '(select * from stratcon.' || v_target.tablename ||
981            ' where sid = ' || v_sid ||
982              ' and name = ' || quote_literal(in_name) ||
983              ' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' ||
984                                  ' and ' || quote_literal(v_end_adj) || '::timestamp) d' ||
985            ' using(rollup_time)';
986
987   for v_rollup_row in execute v_sql loop
988     if derive is true then
989       v_r_rollup_row := v_rollup_row;
990       if v_l_rollup_row.count_rows is not null and
991          v_rollup_row.count_rows is not null then
992         v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time);
993         v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2;
994         v_r_rollup_row.avg_value :=
995           (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval;
996         v_r_rollup_row.min_value :=
997           (v_rollup_row.min_value - v_l_rollup_row.min_value) / v_interval;
998         v_r_rollup_row.max_value :=
999           (v_rollup_row.max_value - v_l_rollup_row.max_value) / v_interval;
1000       else
1001         v_r_rollup_row.count_rows = NULL;
1002         v_r_rollup_row.avg_value = NULL;
1003         v_r_rollup_row.min_value = NULL;
1004         v_r_rollup_row.max_value = NULL;
1005       end if;
1006     else
1007       v_r_rollup_row := v_rollup_row;
1008     end if;
1009     return next v_r_rollup_row;
1010     v_l_rollup_row := v_rollup_row;
1011   end loop;
1012   return;
1013 end
1014 $$ language 'plpgsql';
1015
1016 COMMIT;
Note: See TracBrowser for help on using the browser.