root/sql/schema.sql

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

Grants.

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