root/sql/schema.sql

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

exception handing on stratcon.loading_dock_metric_numeric_s_whence_log

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