root/sql/schema.sql

Revision 7710784460d9831062155d3e99f3bf22e4d978b0, 39.7 kB (checked in by Denish Patel <denish@omniti.com>, 6 years ago)

added node configuration tables and trigger.

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