root/sql/schema.sql

Revision 356779758e509ee0f1a2f419e9fab1d48fdc3c33, 41.7 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 9 years ago)

implement a materialized view on the most recent metric_text change (need speed!)

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