root/sql/schema.sql

Revision 87e2e42dc13b0cfe0cfafcbc70430174c89a04a1, 31.6 kB (checked in by Denish Patel <denish@omniti.com>, 7 years ago)

fixed 20m rollup function and table.

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