root/sql/schema.sql

Revision a2c8a40a569ad8bc6677c21303849d5844ce5436, 33.4 kB (checked in by Denish Patel <denish@omniti.com>, 10 years ago)

Updated roll ups functions to process all available records from log_whence_s table by interval.

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