root/sql/schema.sql

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

corrected error warning for stratcon.mv_loading_dock_check_s()

  • 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.mv_loading_dock_check_s (
17     sid integer NOT NULL,
18     remote_address inet,
19     whence timestamptz NOT NULL,
20     id uuid NOT NULL,
21     target text NOT NULL,
22     module text NOT NULL,
23     name text NOT NULL,
24     PRIMARY KEY(sid)
25 );
26
27 create unique index unq_mv_loading_dock_check_s_id on mv_loading_dock_check_s(id);
28 create index idx_mv_loading_dock_check_s_target on mv_loading_dock_check_s(target);
29 create index idx_mv_loading_dock_check_s_module on  mv_loading_dock_check_s(module);
30 create index idx_mv_loading_dock_check_s_name on mv_loading_dock_check_s(name);
31
32 CREATE TABLE stratcon.loading_dock_status_s (
33     sid integer NOT NULL,
34     whence timestamptz NOT NULL,
35     state character(1) NOT NULL,
36     availability character(1) NOT NULL,
37     duration integer NOT NULL,
38     status text,
39     PRIMARY KEY(sid,whence)
40 );
41
42 CREATE TABLE stratcon.loading_dock_status_s_change_log (
43     sid integer NOT NULL,
44     whence timestamptz NOT NULL,
45     state character(1) NOT NULL,
46     availability character(1) NOT NULL,
47     duration integer NOT NULL,
48     status text,
49     PRIMARY KEY(sid,whence)
50 );
51
52
53 CREATE TABLE stratcon.loading_dock_metric_text_s (
54     sid integer NOT NULL,
55     whence timestamptz NOT NULL,
56     name text NOT NULL,
57     value text,
58     PRIMARY KEY(whence,sid,name)
59 );
60
61 CREATE TABLE stratcon.loading_dock_metric_text_s_change_log (
62     sid integer NOT NULL,
63     whence timestamptz NOT NULL,
64     name text NOT NULL,
65     value text,
66     PRIMARY KEY(whence,sid,name)
67 );
68
69 CREATE TABLE stratcon.loading_dock_metric_numeric_s (
70     sid integer NOT NULL,
71     whence timestamptz NOT NULL,
72     name text NOT NULL,
73     value numeric,
74     PRIMARY KEY(whence,sid,name)
75 );
76
77 CREATE TABLE stratcon.rollup_matrix_numeric_5m (
78     sid integer NOT NULL,
79     name text NOT NULL,
80     rollup_time timestamptz NOT NULL,
81     count_rows integer,
82     avg_value numeric
83     PRIMARY KEY (rollup_time,sid,name)
84 );
85
86 CREATE TABLE stratcon.rollup_matrix_numeric_20m (
87     sid integer NOT NULL,
88     name text NOT NULL,
89     rollup_time timestamptz NOT NULL,
90     count_rows integer,
91     avg_value numeric
92     PRIMARY KEY (rollup_time,sid,name)
93 );
94
95 CREATE OR REPLACE FUNCTION stratcon.date_hour(timestamptz)
96  RETURNS timestamptz as $BODY$
97  SELECT date_trunc('hour',$1);
98  $BODY$
99  language 'sql'
100  IMMUTABLE STRICT;
101
102 CREATE INDEX idx_rollup_matrix_numeric_20m_rollup_time
103               ON stratcon.rollup_matrix_numeric_20m(date_hour(rollup_time));
104
105 CREATE TABLE stratcon.rollup_matrix_numeric_60m(
106    sid integer not null,
107    name text not null,
108    rollup_time timestamptz not null,
109    count_rows integer,
110    avg_value numeric
111    PRIMARY KEY(rollup_time,sid,name));
112    
113 CREATE TABLE stratcon.rollup_matrix_numeric_6hours(
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    PRIMARY KEY(rollup_time6,sid,name));   
120
121 CREATE TABLE stratcon.rollup_matrix_numeric_12hours(
122    sid integer not null,
123    name text not null,
124    rollup_time timestamptz not null,
125    count_rows integer,
126    avg_value numeric ,
127    PRIMARY KEY(rollup_time12,sid,name));     
128  
129 CREATE TABLE stratcon.map_uuid_to_sid (
130     id uuid NOT NULL,
131     sid integer NOT NULL,
132     PRIMARY KEY(id,sid)
133 );
134
135 CREATE TABLE stratcon.log_whence_s (
136     whence timestamptz NOT NULL,
137     interval varchar2(20,
138     PRIMARY KEY(whence,interval)
139 );
140
141 CREATE TABLE stratcon.rollup_runner (
142   rollup_table character varying(100),
143   runner character varying(22)
144 );
145
146 CREATE TABLE stratcon.metric_name_summary (
147   sid integer NOT NULL,
148   metric_name text NOT NULL,
149   metric_type character varying(22),
150   active boolean default 'true',
151   PRIMARY KEY (sid,metric_name,metric_type)
152 );
153
154 CREATE TABLE stratcon.current_node_config (
155   remote_address inet not null,
156   node_type text not null,
157   whence timestamptz not null,
158   config xml not null,
159   PRIMARY KEY (remote_address,node_type)
160 );
161
162 CREATE TABLE stratcon.current_node_config_changelog (
163   remote_address inet not null,
164   node_type text not null,
165   whence timestamptz not null,
166   config xml not null,
167   PRIMARY KEY (remote_address,node_type,whence)
168 );
169
170 -- Schema Sequence
171
172 CREATE SEQUENCE stratcon.seq_sid
173     START WITH 1
174     INCREMENT BY 1
175     NO MAXVALUE
176     NO MINVALUE
177     CACHE 1;
178
179
180
181 -- GRANTS
182
183  GRANT SELECT,INSERT ON stratcon.loading_dock_status_s TO stratcon;
184  GRANT SELECT,INSERT,DELETE ON stratcon.loading_dock_status_s_change_log TO stratcon;
185  GRANT SELECT,INSERT ON stratcon.loading_dock_check_s TO stratcon;
186  GRANT SELECT,INSERT,DELETE ON stratcon.loading_dock_metric_numeric_s TO stratcon;
187  GRANT SELECT,INSERT,DELETE ON stratcon.loading_dock_metric_text_s_change_log TO stratcon;
188  GRANT SELECT,INSERT,DELETE ON stratcon.log_whence_s TO stratcon;
189  GRANT SELECT,INSERT,DELETE ON stratcon.loading_dock_metric_text_s TO stratcon;
190  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_60m TO stratcon;
191  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_5m TO stratcon;
192  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_20m TO stratcon;
193  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_6hours TO stratcon;
194  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_12hours TO stratcon;
195  GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon;
196  GRANT SELECT,INSERT,UPDATE,DELETE ON stratcon.rollup_runner TO stratcon;
197  GRANT SELECT,INSERT,UPDATE,DELETE ON stratcon.metric_name_summary TO stratcon;
198  ALTER TABLE stratcon.seq_sid OWNER TO stratcon;
199  
200  
201 -- Function To generate SID from ID
202
203 CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid)
204 RETURNS integer
205 AS $$
206 DECLARE
207    v_ex_sid integer;
208    v_new_sid integer;
209  
210 BEGIN
211
212 SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id
213   INTO v_ex_sid;
214
215  IF NOT FOUND THEN
216     SELECT nextval('stratcon.seq_sid')
217     INTO v_new_sid;
218
219     INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid);
220    
221     RETURN v_new_sid;
222  ELSE
223       RETURN v_ex_sid;
224  END IF;
225
226 END
227 $$ LANGUAGE plpgsql;
228
229
230
231 CREATE OR REPLACE FUNCTION stratcon.update_config(
232     v_remote_address_in inet,
233     v_node_type_in text,
234     v_whence_in timestamptz,
235     v_config_in xml) RETURNS void
236 AS $$
237 DECLARE
238     v_config xml;
239 BEGIN
240     select config into v_config from stratcon.current_node_config
241      where remote_address = v_remote_address_in
242        and node_type = v_node_type_in;
243     IF FOUND THEN
244         IF v_config::text = v_config_in::text THEN
245             RETURN;
246         END IF;
247         delete from stratcon.current_node_config
248               where remote_address = v_remote_address_in
249                 and node_type = v_node_type_in;
250     END IF;
251     insert into stratcon.current_node_config
252                 (remote_address, node_type, whence, config)
253          values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in);
254     insert into stratcon.current_node_config_changelog
255                 (remote_address, node_type, whence, config)
256          values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in);
257 END
258 $$ LANGUAGE plpgsql;
259
260 -- Trigger Function to log dock status Changes
261
262 CREATE  TRIGGER loading_dock_status_s_change_log
263     AFTER INSERT ON stratcon.loading_dock_status_s
264     FOR EACH ROW
265     EXECUTE PROCEDURE stratcon.loading_dock_status_s_change_log();
266
267
268 CREATE OR REPLACE FUNCTION stratcon.loading_dock_status_s_change_log() RETURNS trigger
269     AS $$
270 DECLARE
271     v_state CHAR(1);
272     v_avail CHAR(1);
273 BEGIN
274
275 IF TG_OP = 'INSERT' THEN
276     SELECT state,availability FROM  stratcon.loading_dock_status_s WHERE sid = NEW.sid
277         AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_status_s_change_log
278                         WHERE  SID=NEW.sid and  WHENCE <> NEW.whence )
279     INTO v_state,v_avail;
280
281     IF v_state IS DISTINCT FROM NEW.state OR v_avail IS DISTINCT FROM NEW.availability THEN
282
283         INSERT INTO stratcon.loading_dock_status_s_change_log (sid,whence,state,availability,duration,status)
284             VALUES (NEW.sid,NEW.whence,NEW.state,NEW.availability,NEW.duration,NEW.status);
285
286     END IF;
287
288 ELSE
289         RAISE EXCEPTION 'Something wrong with stratcon.loading_dock_status_s_change_log';
290 END IF;
291
292     RETURN NULL;
293
294 END
295 $$
296     LANGUAGE plpgsql;
297
298 -- Trigger Function to log dock MV
299
300 CREATE  TRIGGER mv_loading_dock_check_s
301     AFTER INSERT ON stratcon.loading_dock_check_s
302     FOR EACH ROW
303     EXECUTE PROCEDURE stratcon.mv_loading_dock_check_s();
304
305
306 CREATE OR REPLACE FUNCTION stratcon.mv_loading_dock_check_s() RETURNS trigger
307     AS $$
308 DECLARE
309     v_remote_address INET;
310     v_target TEXT;
311     v_module TEXT;
312     v_name TEXT;
313 BEGIN
314
315 IF TG_OP = 'INSERT' THEN
316     SELECT remote_address,target,module,name FROM  stratcon.mv_loading_dock_check_s WHERE sid = NEW.sid AND id=NEW.id
317         INTO v_remote_address,v_target,v_module,v_name;
318
319     IF v_remote_address IS DISTINCT FROM NEW.remote_address OR v_target IS DISTINCT FROM NEW.target OR v_name IS DISTINCT FROM NEW.name   THEN
320        
321         DELETE from stratcon.mv_loading_dock_check_s WHERE sid = NEW.sid AND id=NEW.id;
322        
323         INSERT INTO stratcon.mv_loading_dock_check_s (sid,remote_address,whence,id,target,module,name)
324             VALUES (NEW.sid,NEW.remote_address,NEW.whence,NEW.id,NEW.target,NEW.module,NEW.name);
325
326     END IF;
327
328 ELSE
329         RAISE EXCEPTION 'Something wrong with stratcon.mv_loading_dock_check_s';
330 END IF;
331
332     RETURN NULL;
333
334 END
335 $$
336     LANGUAGE plpgsql;
337    
338 -- Trigger Function to log Metrix Text Changes
339
340 CREATE TRIGGER loading_dock_metric_text_s_change_log
341     AFTER INSERT ON stratcon.loading_dock_metric_text_s
342     FOR EACH ROW
343     EXECUTE PROCEDURE stratcon.loading_dock_metric_text_s_change_log();
344
345
346 CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger
347     AS $$
348 DECLARE
349     v_oldvalue text;
350     v_sid integer;
351     v_name text;
352     v_value text;
353     v_whence timestamptz;
354     v_old_whence timestamptz;
355     v_old_name text;
356     v_old_sid integer;
357     v_old_value text;
358     v_max_whence timestamptz;
359 BEGIN
360
361 IF TG_OP = 'INSERT' THEN
362
363              SELECT value FROM  stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name
364                  AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log
365                                  WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name )
366                      INTO v_oldvalue;
367
368                     IF v_oldvalue IS DISTINCT FROM NEW.value THEN
369                
370                         INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value)
371                             VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);
372                     END IF;
373
374
375 SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name
376         INTO v_sid,v_name;
377      IF NOT FOUND THEN
378           INSERT INTO  stratcon.metric_name_summary(sid,metric_name,metric_type)  VALUES(NEW.sid,NEW.name,'text');
379      END IF;
380
381 ELSE
382         RAISE EXCEPTION 'something wrong with stratcon.loading_dock_metric_text_s_change_log ';
383 END IF;
384
385     RETURN NULL;
386
387 END
388 $$
389     LANGUAGE plpgsql;
390    
391 -- Trigger on Metrix Numeric to log last inserted timestamp
392
393 CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log()
394 RETURNS trigger
395 AS $$
396 DECLARE
397 v_whence timestamptz;
398 v_whence_5 timestamptz;
399 v_sid integer;
400 v_name text;
401 BEGIN
402 IF TG_OP = 'INSERT' THEN
403  
404  v_whence_5:=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval;
405  
406    SELECT whence FROM stratcon.log_whence_s WHERE whence=v_whence_5 and interval='5 minutes'
407      INTO v_whence;
408      
409    IF NOT FOUND THEN
410       BEGIN
411        INSERT INTO  stratcon.log_whence_s VALUES(v_whence_5,'5 minutes');
412        EXCEPTION
413         WHEN UNIQUE_VIOLATION THEN
414         -- do nothing
415       END;
416     END IF;
417
418    SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name
419      INTO v_sid,v_name;
420    IF NOT FOUND THEN
421        INSERT INTO  stratcon.metric_name_summary VALUES(NEW.sid,NEW.name,'numeric');
422     END IF;
423
424 END IF;
425     RETURN NULL;
426 END
427 $$    LANGUAGE plpgsql;
428
429 -- 5 minutes rollup
430
431 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m()
432 RETURNS void
433 AS $$
434 DECLARE
435  
436  rec stratcon.rollup_matrix_numeric_5m%rowtype;
437  v_sql TEXT;
438  v_min_whence TIMESTAMPTZ;
439  v_max_rollup_5 TIMESTAMPTZ;
440  v_whence TIMESTAMPTZ;
441  rows INT;
442  v_nrunning INT;
443  v_self VARCHAR(22);
444  whenceint RECORD;
445 BEGIN
446
447   SELECT COUNT(1) INTO v_nrunning
448     from stratcon.rollup_runner t, pg_stat_activity a
449    where rollup_table ='rollup_matrix_numeric_5m'
450      and runner = procpid || '.' || date_part('epoch',backend_start);
451
452   IF v_nrunning > 0 THEN
453     RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running';
454     RETURN ;
455   END IF;
456
457   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
458     FROM pg_stat_activity
459    WHERE procpid = pg_backend_pid();
460
461   IF v_self IS NULL THEN
462     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot self-identify';
463   END IF;
464
465   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_5m''';
466
467   EXECUTE v_sql;
468
469 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='5 minutes' LOOP
470        
471
472  SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes'
473         INTO v_min_whence;
474        
475  SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_5m
476          INTO v_max_rollup_5;       
477  
478  -- Insert Log for 20 minutes rollup
479    
480    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'
481            INTO v_whence;
482       IF NOT FOUND THEN
483        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');
484    END IF;
485    
486  IF v_min_whence <= v_max_rollup_5 THEN
487
488    DELETE FROM stratcon.rollup_matrix_numeric_5m
489                 WHERE rollup_time = v_min_whence;
490  
491  END IF;
492
493  FOR rec IN
494                 SELECT sid , name,v_min_whence as rollup_time,
495                       COUNT(1) as count_rows ,AVG(value) as avg_value
496                       FROM stratcon.loading_dock_metric_numeric_s
497                       WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval
498                 GROUP BY rollup_time,sid,name
499  
500        LOOP
501    
502        
503         INSERT INTO stratcon.rollup_matrix_numeric_5m
504          (sid,name,rollup_time,count_rows,avg_value) VALUES
505          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value);
506        
507    END LOOP;
508
509   -- Delete from whence log table
510  
511   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes';
512  
513  v_min_whence:= NULL;
514  v_max_rollup_5:= NULL;
515  
516  END LOOP;
517  
518   UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
519  
520 RETURN;
521
522 EXCEPTION
523     WHEN RAISE_EXCEPTION THEN
524        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
525        RAISE EXCEPTION '%', SQLERRM;
526     WHEN OTHERS THEN
527          RAISE NOTICE '%', SQLERRM;
528 END
529 $$ LANGUAGE plpgsql;
530
531
532 -- 20 minutes rollup
533
534 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_20m()
535 RETURNS void
536 AS $$
537 DECLARE
538  
539  rec stratcon.rollup_matrix_numeric_20m%rowtype;
540  v_sql TEXT;
541  v_min_whence TIMESTAMPTZ;
542  v_max_rollup_20 TIMESTAMPTZ;
543  v_whence TIMESTAMPTZ;
544  rows INT;
545  v_nrunning INT;
546  v_self VARCHAR(22);
547  whenceint RECORD;
548 BEGIN
549
550   SELECT COUNT(1) INTO v_nrunning
551     from stratcon.rollup_runner t, pg_stat_activity a
552    where rollup_table ='rollup_matrix_numeric_20m'
553      and runner = procpid || '.' || date_part('epoch',backend_start);
554
555   IF v_nrunning > 0 THEN
556     RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running';
557     RETURN ;
558   END IF;
559
560   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
561     FROM pg_stat_activity
562    WHERE procpid = pg_backend_pid();
563
564   IF v_self IS NULL THEN
565     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot self-identify';
566   END IF;
567
568   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_20m''';
569
570   EXECUTE v_sql;
571
572 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='20 minutes' LOOP
573
574  SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='20 minutes'
575         INTO v_min_whence;
576        
577  SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_20m
578          INTO v_max_rollup_20;       
579  
580  -- Insert Log for Hourly rollup
581    
582    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour'
583            INTO v_whence;
584       IF NOT FOUND THEN
585        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour');
586    END IF;
587    
588  IF v_min_whence <= v_max_rollup_20 THEN
589
590    DELETE FROM stratcon.rollup_matrix_numeric_20m
591                 WHERE rollup_time = v_min_whence;
592  
593  END IF;
594
595  FOR rec IN
596                 SELECT sid , name,v_min_whence as rollup_time,
597                        SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value
598                        FROM stratcon.rollup_matrix_numeric_5m
599                       WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval
600                 GROUP BY sid,name
601  
602        LOOP
603    
604        
605         INSERT INTO stratcon.rollup_matrix_numeric_20m
606          (sid,name,rollup_time,count_rows,avg_value) VALUES
607          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value);
608        
609    END LOOP;
610
611   -- Delete from whence log table
612  
613   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='20 minutes';
614  
615   v_min_whence:= NULL;
616   v_max_rollup_20:= NULL;
617
618  END LOOP;
619  
620   UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
621  
622 RETURN;
623
624 EXCEPTION
625     WHEN RAISE_EXCEPTION THEN
626        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
627        RAISE EXCEPTION '%', SQLERRM;
628     WHEN OTHERS THEN
629       RAISE NOTICE '%', SQLERRM;
630 END
631 $$ LANGUAGE plpgsql;
632
633
634 -- 1 hour rollup
635
636
637 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m()
638 RETURNS void
639 AS $$
640 DECLARE
641   rec stratcon.rollup_matrix_numeric_60m%rowtype;
642   v_sql TEXT;
643   v_min_whence TIMESTAMPTZ;
644   v_max_rollup_60 TIMESTAMPTZ;
645   v_whence TIMESTAMPTZ;
646   v_nrunning INT;
647   v_self VARCHAR(22);
648   whenceint RECORD;
649 BEGIN
650
651   SELECT COUNT(1) INTO v_nrunning
652     from stratcon.rollup_runner t, pg_stat_activity a
653    where rollup_table ='rollup_matrix_numeric_60m'
654      and runner = procpid || '.' || date_part('epoch',backend_start);
655
656   IF v_nrunning > 0 THEN
657     RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running';
658     RETURN ;
659   END IF;
660
661   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
662     FROM pg_stat_activity
663    WHERE procpid = pg_backend_pid();
664
665   IF v_self IS NULL THEN
666     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot self-identify';
667   END IF;
668
669   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_60m''';
670
671   EXECUTE v_sql;
672
673 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='1 hour' LOOP
674            
675   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour'
676          INTO v_min_whence;
677          
678   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_60m
679          INTO v_max_rollup_60;   
680
681 -- Insert Log for 6 Hour rollup
682    
683    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'
684            INTO v_whence;
685       IF NOT FOUND THEN
686        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');
687    END IF;
688    
689    
690   IF v_min_whence <= v_max_rollup_60 THEN
691  
692   DELETE FROM stratcon.rollup_matrix_numeric_60m
693        WHERE rollup_time= v_min_whence;
694
695   END IF;
696  
697     FOR rec IN
698                 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
699                          FROM stratcon.rollup_matrix_numeric_20m
700                            WHERE date_hour(rollup_time)= v_min_whence
701                    GROUP BY date_hour(rollup_time),sid,name
702         LOOP
703      
704           INSERT INTO stratcon.rollup_matrix_numeric_60m
705           (sid,name,rollup_time,count_rows,avg_value) VALUES
706           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value);
707          
708      END LOOP;
709
710
711 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour';
712
713 v_min_whence := NULL;
714 v_max_rollup_60 := NULL;
715
716 END LOOP;
717
718 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
719
720 RETURN;
721
722 EXCEPTION
723     WHEN RAISE_EXCEPTION THEN
724        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
725        RAISE EXCEPTION '%', SQLERRM;
726     WHEN OTHERS THEN
727       RAISE NOTICE '%', SQLERRM;
728 END
729 $$ LANGUAGE plpgsql;
730
731
732 -- 6 hours
733
734 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_6hours()
735 RETURNS void
736 AS $$
737 DECLARE
738   rec stratcon.rollup_matrix_numeric_6hours%rowtype;
739   v_sql TEXT;
740   v_min_whence TIMESTAMPTZ;
741   v_max_rollup_6 TIMESTAMPTZ;
742   v_whence TIMESTAMPTZ;
743   v_nrunning INT;
744   v_self VARCHAR(22);
745   whenceint RECORD; 
746 BEGIN
747
748   SELECT COUNT(1) INTO v_nrunning
749     from stratcon.rollup_runner t, pg_stat_activity a
750     where rollup_table ='rollup_matrix_numeric_6hours'
751      and runner = procpid || '.' || date_part('epoch',backend_start);
752
753   IF v_nrunning > 0 THEN
754     RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running';
755     RETURN ;
756   END IF;
757
758   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
759     FROM pg_stat_activity
760      WHERE procpid = pg_backend_pid();
761
762   IF v_self IS NULL THEN
763     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot self-identify';
764    END IF;
765
766    v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_6hours''';
767
768   EXECUTE v_sql;
769
770 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='6 hours' LOOP
771
772   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours'
773          INTO v_min_whence;
774          
775   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_6hours
776          INTO v_max_rollup_6;   
777
778 -- Insert Log for 12 Hours rollup
779    
780    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'
781            INTO v_whence;
782       IF NOT FOUND THEN
783        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');
784    END IF;
785    
786    
787   IF v_min_whence <= v_max_rollup_6 THEN
788  
789   DELETE FROM stratcon.rollup_matrix_numeric_6hours
790        WHERE rollup_time= v_min_whence;
791
792   END IF;
793  
794     FOR rec IN
795                 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
796                          FROM stratcon.rollup_matrix_numeric_60m
797                            WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval
798                    GROUP BY sid,name
799         LOOP
800      
801        
802           INSERT INTO stratcon.rollup_matrix_numeric_6hours
803           (sid,name,rollup_time,count_rows,avg_value) VALUES
804           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value);
805          
806      END LOOP;
807
808
809 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours';
810 v_min_whence := NULL;
811 v_max_rollup_6 := NULL;
812
813 END LOOP;
814
815 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
816
817 RETURN;
818
819 EXCEPTION
820     WHEN RAISE_EXCEPTION THEN
821        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
822        RAISE EXCEPTION '%', SQLERRM;
823     WHEN OTHERS THEN
824        RAISE NOTICE '%', SQLERRM;
825 END
826 $$ LANGUAGE plpgsql;
827
828
829 -- 12 hours
830
831 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_12hours()
832 RETURNS void
833 AS $$
834 DECLARE
835   rec stratcon.rollup_matrix_numeric_12hours%rowtype;
836   v_sql TEXT;
837   v_min_whence TIMESTAMPTZ;
838   v_max_rollup_12 TIMESTAMPTZ;
839   v_whence TIMESTAMPTZ;
840   v_nrunning INT;
841   v_self VARCHAR(22);
842   whenceint RECORD;
843  
844 BEGIN
845
846   SELECT COUNT(1) INTO v_nrunning
847     from stratcon.rollup_runner t, pg_stat_activity a
848     where rollup_table ='rollup_matrix_numeric_12hours'
849      and runner = procpid || '.' || date_part('epoch',backend_start);
850
851   IF v_nrunning > 0 THEN
852     RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running';
853     RETURN ;
854   END IF;
855
856   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
857     FROM pg_stat_activity
858      WHERE procpid = pg_backend_pid();
859
860   IF v_self IS NULL THEN
861     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot self-identify';
862    END IF;
863
864    v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_12hours''';
865
866   EXECUTE v_sql;
867
868  FOR whenceint IN  SELECT * FROM stratcon.log_whence_s WHERE interval='12 hours' LOOP
869  
870   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours'
871          INTO v_min_whence;
872          
873   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_12hours
874          INTO v_max_rollup_12;   
875
876 /*-- Insert Log for 24 Hours rollup
877    
878    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'
879            INTO v_whence;
880       IF NOT FOUND THEN
881        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');
882    END IF;
883    */
884    
885   IF v_min_whence <= v_max_rollup_12 THEN
886  
887   DELETE FROM stratcon.rollup_matrix_numeric_12hours
888        WHERE rollup_time= v_min_whence;
889
890   END IF;
891  
892     FOR rec IN
893                 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
894                          FROM stratcon.rollup_matrix_numeric_6hours
895                            WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval
896                    GROUP BY sid,name
897         LOOP
898      
899        
900           INSERT INTO stratcon.rollup_matrix_numeric_12hours
901           (sid,name,rollup_time,count_rows,avg_value) VALUES
902           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value);
903          
904      END LOOP;
905
906
907 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours';
908
909 v_min_whence := NULL;
910 v_max_rollup_12 := NULL;
911
912 END LOOP;
913
914 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_12hours';
915
916 RETURN;
917
918 EXCEPTION
919     WHEN RAISE_EXCEPTION THEN
920       UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours';
921       RAISE EXCEPTION '%', SQLERRM;
922     WHEN OTHERS THEN
923       RAISE NOTICE '%', SQLERRM;
924 END
925 $$ LANGUAGE plpgsql;
926
927
928 CREATE OR REPLACE FUNCTION stratcon.fetch_varset(in_check uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer)
929   RETURNS SETOF stratcon.loading_dock_metric_text_s_change_log AS
930 $BODY$
931 declare
932   v_sid int;
933 begin
934   -- Map out uuid to an sid.
935   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check;
936   if not found then
937     return;
938   end if;
939
940   return query select * from stratcon.fetch_varset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods);
941 end
942 $BODY$
943   LANGUAGE 'plpgsql' VOLATILE
944   COST 100
945   ROWS 1000;
946 ALTER FUNCTION stratcon.fetch_varset(uuid, text, timestamp with time zone, timestamp with time zone, integer) OWNER TO omniti;
947
948 CREATE OR REPLACE FUNCTION stratcon.fetch_varset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer)
949   RETURNS SETOF stratcon.loading_dock_metric_text_s_change_log AS
950 $BODY$
951 declare
952   v_sid int;
953   v_target record;
954   v_start_adj timestamptz;
955   v_start_text text;
956   v_next_text text;
957   v_end_adj timestamptz;
958   v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype;
959 begin
960   -- Map out uuid to an sid.
961   v_sid := in_sid;
962
963   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
964
965   select 'epoch'::timestamp +
966          ((floor(extract('epoch' from in_start_time) /
967                  extract('epoch' from v_target.period)) *
968            extract('epoch' from v_target.period)) || ' seconds') ::interval
969     into v_start_adj;
970
971   select 'epoch'::timestamp +
972          ((floor(extract('epoch' from in_end_time) /
973                  extract('epoch' from v_target.period)) *
974            extract('epoch' from v_target.period)) || ' seconds') ::interval
975     into v_end_adj;
976
977   for v_change_row in
978     select sid, 'epoch'::timestamp +
979          ((floor(extract('epoch' from whence) /
980                  extract('epoch' from v_target.period)) *
981            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
982            name, value
983       from stratcon.loading_dock_metric_text_s_change_log
984      where sid = v_sid
985        and name = in_name
986        and whence <= v_start_adj
987   order by 'epoch'::timestamp +
988          ((floor(extract('epoch' from whence) /
989                  extract('epoch' from v_target.period)) *
990            extract('epoch' from v_target.period)) || ' seconds') ::interval desc
991      limit 1
992   loop
993     v_start_text := coalesce(v_change_row.value, '[unset]');
994   end loop;
995
996   for v_change_row in
997     select v_sid as sid, whence, in_name as name, value from
998 --    (select v_start_adj::timestamp + t * v_target.period::interval as whence
999 --      from generate_series(1, v_target.nperiods) t) s
1000 -- left join
1001     (select 'epoch'::timestamp +
1002          ((floor(extract('epoch' from whence) /
1003                  extract('epoch' from v_target.period)) *
1004            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
1005            coalesce(value, '[unset]') as value
1006       from stratcon.loading_dock_metric_text_s_change_log
1007      where sid = v_sid
1008        and name = in_name
1009        and whence > v_start_adj
1010        and whence <= v_end_adj) d
1011 --    using (whence)
1012   order by whence asc
1013   loop
1014     v_next_text := v_change_row.value;
1015     if v_change_row.value is not null and
1016        v_start_text != v_change_row.value then
1017       v_change_row.value := coalesce(v_start_text, '[unset]') || ' -> ' || coalesce(v_change_row.value, '[unset]');
1018     else
1019       v_change_row.value := v_start_text;
1020     end if;
1021     if v_next_text is not null then
1022       v_start_text := v_next_text;
1023     end if;
1024     return next v_change_row;
1025   end loop;
1026
1027   return;
1028 end
1029 $BODY$
1030   LANGUAGE 'plpgsql' VOLATILE
1031   COST 100
1032   ROWS 1000;
1033 ALTER FUNCTION stratcon.fetch_varset(integer, text, timestamp with time zone, timestamp with time zone, integer) OWNER TO omniti;
1034
1035 create or replace function
1036 stratcon.choose_window(in_start_time timestamptz,
1037                        in_end_time timestamptz,
1038                        in_hopeful_nperiods int,
1039                        out tablename text,
1040                        out period interval,
1041                        out nperiods int)
1042 returns setof record as
1043 $$
1044 declare
1045   window record;
1046 begin
1047   -- Figure out which table we should be looking in
1048   for window in
1049     select atablename, aperiod, anperiods
1050     from (select aperiod, iv/isec as anperiods, atablename,
1051                  abs(case when iv/isec - in_hopeful_nperiods < 0
1052                           then 10 * (in_hopeful_nperiods - iv/isec)
1053                           else iv/isec - in_hopeful_nperiods
1054                            end) as badness
1055             from (select extract('epoch' from in_end_time) -
1056                          extract('epoch' from in_start_time) as iv
1057                  ) i,
1058                  (   select 5*60 as isec, '5 minutes'::interval as aperiod,
1059                             'rollup_matrix_numeric_5m' as atablename
1060                   union all
1061                      select 20*60 as isec, '20 minutes'::interval as aperiod,
1062                             'rollup_matrix_numeric_20m' as atablename
1063                   union all
1064                      select 60*60 as isec, '1 hour'::interval as aperiod,
1065                             'rollup_matrix_numeric_60m' as atablename
1066                   union all
1067                      select 6*60*60 as isec, '6 hours'::interval as aaperiod,
1068                             'rollup_matrix_numeric_6hours' as atablename
1069                   union all
1070                      select 12*60*60 as isec, '12 hours'::interval as aperiod,
1071                             'rollup_matrix_numeric_12hours' as atablename
1072                  ) ivs
1073          ) b
1074  order by badness asc
1075   limit 1
1076   loop
1077     tablename := window.atablename;
1078     period := window.aperiod;
1079     nperiods := window.anperiods;
1080     return next;
1081   end loop;
1082   return;
1083 end
1084 $$ language 'plpgsql';
1085
1086 CREATE OR REPLACE FUNCTION stratcon.fetch_dataset(in_uuid uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean)
1087   RETURNS SETOF stratcon.rollup_matrix_numeric_5m AS
1088 $BODY$
1089 declare
1090   v_sid int;
1091 begin
1092   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check;
1093   if not found then
1094     return;
1095   end if;
1096
1097   return query select * from stratcon.fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive);
1098 end
1099 $BODY$
1100   LANGUAGE 'plpgsql' VOLATILE
1101   COST 100
1102   ROWS 1000;
1103 ALTER FUNCTION stratcon.fetch_dataset(uuid, text, timestamp with time zone, timestamp with time zone, integer, boolean) OWNER TO omniti;
1104
1105 CREATE OR REPLACE FUNCTION stratcon.fetch_dataset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean)
1106   RETURNS SETOF stratcon.rollup_matrix_numeric_5m AS
1107 $BODY$
1108 declare
1109   v_sql text;
1110   v_sid int;
1111   v_target record;
1112   v_interval numeric;
1113   v_start_adj timestamptz;
1114   v_end_adj timestamptz;
1115   v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
1116   v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
1117   v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
1118 begin
1119
1120   -- Map out uuid to an sid.
1121   v_sid := in_sid;
1122
1123   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
1124
1125   if not found then
1126     raise exception 'no target table';
1127     return;
1128   end if;
1129
1130   select 'epoch'::timestamp +
1131          ((floor(extract('epoch' from in_start_time) /
1132                  extract('epoch' from v_target.period)) *
1133            extract('epoch' from v_target.period)) || ' seconds') ::interval
1134     into v_start_adj;
1135
1136   select 'epoch'::timestamp +
1137          ((floor(extract('epoch' from in_end_time) /
1138                  extract('epoch' from v_target.period)) *
1139            extract('epoch' from v_target.period)) || ' seconds') ::interval
1140     into v_end_adj;
1141
1142   v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' ||
1143            's.rollup_time, d.count_rows, d.avg_value ' ||
1144            ' from ' ||
1145            '(select ' || quote_literal(v_start_adj) || '::timestamp' ||
1146                   ' + t * ' || quote_literal(v_target.period) || '::interval' ||
1147                        ' as rollup_time' ||
1148              ' from generate_series(1,' || v_target.nperiods || ') t) s ' ||
1149            'left join ' ||
1150            '(select * from stratcon.' || v_target.tablename ||
1151            ' where sid = ' || v_sid ||
1152              ' and name = ' || quote_literal(in_name) ||
1153              ' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' ||
1154                                  ' and ' || quote_literal(v_end_adj) || '::timestamp) d' ||
1155            ' using(rollup_time)';
1156
1157   for v_rollup_row in execute v_sql loop
1158     if derive is true then
1159       v_r_rollup_row := v_rollup_row;
1160       if v_l_rollup_row.count_rows is not null and
1161          v_rollup_row.count_rows is not null then
1162         v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time);
1163         v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2;
1164         v_r_rollup_row.avg_value :=
1165           (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval;
1166       else
1167         v_r_rollup_row.count_rows = NULL;
1168         v_r_rollup_row.avg_value = NULL;
1169        
1170       end if;
1171     else
1172       v_r_rollup_row := v_rollup_row;
1173     end if;
1174     return next v_r_rollup_row;
1175     v_l_rollup_row := v_rollup_row;
1176   end loop;
1177   return;
1178 end
1179 $BODY$
1180   LANGUAGE 'plpgsql' VOLATILE
1181   COST 100
1182   ROWS 1000;
1183 ALTER FUNCTION stratcon.fetch_dataset(integer, text, timestamp with time zone, timestamp with time zone, integer, boolean) OWNER TO omniti;
1184
1185 -- Remove Metric based on UUID and Metric_Name
1186
1187 CREATE OR REPLACE FUNCTION stratcon.remove_metric   (in_uuid uuid,
1188                                                      in_metric_name text,
1189                                                      v_debug text,
1190                                                      OUT v_out text)
1191 RETURNS text
1192 AS $$
1193 DECLARE
1194         v_del_sid INT;
1195         v_del_metric_name TEXT;
1196         v_del_metric_type TEXT;
1197         deleted_t INT;
1198         deleted_tc INT;
1199         deleted_n INT;
1200         deleted_5 INT;
1201         deleted_20 INT;
1202         deleted_60 INT;
1203         deleted_6h INT;
1204         deleted_12h INT;
1205         deleted_sum INT;
1206
1207 BEGIN
1208           SELECT s.sid,m.metric_name,m.metric_type
1209             FROM
1210                      stratcon.map_uuid_to_sid s,
1211                      stratcon.metric_name_summary m
1212                WHERE s.id=in_uuid
1213                      and s.sid=m.sid
1214                      and m.metric_name=in_metric_name
1215           INTO v_del_sid,v_del_metric_name,v_del_metric_type;
1216                 IF NOT FOUND THEN
1217                    IF v_debug = 'DEBUG' THEN
1218                      RAISE NOTICE 'Given UUID can not map to SID,Metric Name: %,%',in_uuid,in_metric_name;
1219                    END IF;
1220                    v_out:='Please Supply Valid UUID,Metric Name Combination :'||in_uuid||','||in_metric_name;
1221                  RETURN;
1222                 END IF;
1223 IF v_debug = 'DEBUG' THEN
1224         RAISE NOTICE 'Delete In Progress For: %,%,%',v_del_sid,v_del_metric_name,v_del_metric_type;
1225 END IF;
1226
1227 -- Check of Text or Numeric Type
1228 IF v_del_metric_type ='text' THEN
1229  -- Delete from Metrix Tex table
1230   DELETE FROM stratcon.loading_dock_metric_text_s WHERE sid=v_del_sid AND name=v_del_metric_name;
1231      GET DIAGNOSTICS deleted_t = ROW_COUNT;
1232      IF v_debug = 'DEBUG' THEN
1233            RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s : %',deleted;
1234      END IF;
1235  -- Delete from Metrix Change Log table
1236   DELETE FROM stratcon.loading_dock_metric_text_s_change_log WHERE sid=v_del_sid AND name=v_del_metric_name;
1237      GET DIAGNOSTICS deleted_tc = ROW_COUNT;
1238      IF v_debug = 'DEBUG' THEN
1239           RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s_change_log : %',deleted;
1240      END IF;
1241  ELSE
1242   -- Delete from Metrix Numeric table
1243    DELETE FROM stratcon.loading_dock_metric_numeric_s WHERE sid=v_del_sid AND name=v_del_metric_name;
1244    GET DIAGNOSTICS deleted_n = ROW_COUNT;
1245      IF v_debug = 'DEBUG' THEN
1246          RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_numeric_s : %',deleted;
1247      END IF;
1248   -- Delete from Rollup tables
1249    DELETE FROM stratcon.rollup_matrix_numeric_5m WHERE sid=v_del_sid AND name=v_del_metric_name;
1250    GET DIAGNOSTICS deleted_5 = ROW_COUNT;   
1251      IF v_debug = 'DEBUG' THEN
1252          RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_5m : %',deleted;
1253      END IF;
1254    DELETE FROM stratcon.rollup_matrix_numeric_20m WHERE sid=v_del_sid AND name=v_del_metric_name;
1255       GET DIAGNOSTICS deleted_20= ROW_COUNT;     
1256         IF v_debug = 'DEBUG' THEN
1257             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_20m : %',deleted;
1258         END IF;
1259    DELETE FROM stratcon.rollup_matrix_numeric_60m WHERE sid=v_del_sid AND name=v_del_metric_name;
1260       GET DIAGNOSTICS deleted_60 = ROW_COUNT;     
1261         IF v_debug = 'DEBUG' THEN
1262             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_60m : %',deleted;
1263         END IF;
1264    DELETE FROM stratcon.rollup_matrix_numeric_6hours WHERE sid=v_del_sid AND name=v_del_metric_name;
1265       GET DIAGNOSTICS deleted_6h = ROW_COUNT;     
1266         IF v_debug = 'DEBUG' THEN
1267             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_6hours : %',deleted;
1268         END IF;
1269    DELETE FROM stratcon.rollup_matrix_numeric_12hours WHERE sid=v_del_sid AND name=v_del_metric_name;
1270       GET DIAGNOSTICS deleted_12h = ROW_COUNT;     
1271         IF v_debug = 'DEBUG' THEN
1272             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_12hours : %',deleted;
1273         END IF;
1274 END IF;
1275   -- Delete from metrix summary table
1276    DELETE FROM stratcon.metrix_name_summary WHERE sid=v_del_sid AND metric_name=v_del_metric_name;
1277       GET DIAGNOSTICS deleted_sum= ROW_COUNT;     
1278         IF v_debug = 'DEBUG' THEN
1279             RAISE NOTICE 'DELELTED ROWS FROM metric_name_summary : %',deleted;
1280         END IF;
1281  v_out:='Deleted Rows for Metric_Text, Metrix_Text_change_log,Metric_Numeric,Rollup_5m,Rollup_20m,Rollup_1hour,Rollup_6hours,Rollup_12hours,Metric_Summary:'||deleted_t||','||deleted_tc||','||deleted_n||','||deleted_5||','||deleted_20||','||deleted_60||','||deleted_6h||','||deleted_12h||','||deleted_sum;
1282 RETURN;
1283    EXCEPTION
1284             WHEN RAISE_EXCEPTION THEN
1285                     RAISE EXCEPTION '%', SQLERRM;
1286             WHEN OTHERS THEN
1287                     RAISE NOTICE '%', SQLERRM;
1288 END
1289 $$ LANGUAGE plpgsql;
1290
1291 COMMIT;
Note: See TracBrowser for help on using the browser.