root/sql/schema.sql

Revision 5de3ee066898c7932e978dbe8224ca4e280635cb, 32.9 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 6 years ago)

fix the triggers.. the row itself is called nam, the summary is metric_name. Also, move the if not found's in the windowed functions to be immediately after window selection

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