root/sql/sprocs/stratcon.rollup_metric_numeric_5m.sql

Revision e339e1b44a0250a2b1f15e90ca24b42c3561d6a9, 3.9 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 8 years ago)

this is wrong

  • Property mode set to 100644
Line 
1 CREATE OR REPLACE FUNCTION stratcon.rollup_metric_numeric_5m() RETURNS void
2 AS $$
3 DECLARE
4  v_sql TEXT;
5  v_min_whence TIMESTAMPTZ;
6  v_max_rollup_5 TIMESTAMPTZ;
7  v_whence TIMESTAMPTZ;
8  rows INT;
9  v_nrunning INT;
10  v_taskid int;
11  v_locked boolean;
12  whenceint RECORD;
13
14  v_rec RECORD;
15  v_info stratcon.metric_numeric_rollup_segment%rowtype;
16  v_offset smallint;
17  v_init boolean := false;
18 BEGIN
19
20   SELECT id INTO v_taskid FROM tasklock WHERE name = 'rollup_metric_numeric_5m';
21   IF v_taskid IS NULL THEN
22     INSERT INTO tasklock (id, name) VALUES (nextval('tasklock_id_seq'), 'rollup_metric_numeric_5m')
23       RETURNING id into v_taskid;
24   END IF;
25
26   select pg_try_advisory_lock(43191, v_taskid) into v_locked;
27
28   IF v_locked = false THEN
29     RAISE NOTICE 'rollup_metric_numeric_5m already running';
30     RETURN ;
31   END IF;
32
33   FOR whenceint IN SELECT * FROM metric_numeric_rollup_queue WHERE interval='5m' LOOP
34
35     SELECT MIN(whence) FROM metric_numeric_rollup_queue WHERE interval='5m'
36         INTO v_min_whence;
37        
38     SELECT MAX(rollup_time) FROM metric_numeric_rollup_5m
39          INTO v_max_rollup_5;       
40  
41     -- Insert Log for 20 minutes rollup
42    
43     SELECT whence FROM metric_numeric_rollup_queue
44         WHERE whence=date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval and interval='20m'
45         INTO v_whence;
46     IF NOT FOUND THEN
47        INSERT INTO  metric_numeric_rollup_queue
48            VALUES(date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval,'20m');
49     END IF;
50
51     -- Insert Log for 30 minutes rollup
52    
53     SELECT whence FROM metric_numeric_rollup_queue
54         WHERE whence=date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/30)*30) * '1 minute'::interval and interval='30m'
55         INTO v_whence;
56     IF NOT FOUND THEN
57        INSERT INTO  metric_numeric_rollup_queue
58            VALUES(date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/30)*30) * '1 minute'::interval,'30m');
59     END IF;
60
61     FOR v_info IN SELECT * FROM stratcon.window_robust_derive(v_min_whence) LOOP
62
63         v_offset := ( 12*(extract('hour' from v_min_whence at time zone 'UTC'))+floor(extract('minute' from v_min_whence at time zone 'UTC')/5) );   
64         v_info.rollup_time := date_trunc('day', v_info.rollup_time AT TIME ZONE 'UTC') AT TIME ZONE 'UTC';
65
66         v_init := false;
67         SELECT * FROM metric_numeric_rollup_5m WHERE rollup_time = v_info.rollup_time AND sid=v_info.sid AND name=v_info.name INTO v_rec;
68         IF NOT FOUND THEN
69                 SELECT * FROM stratcon.init_metric_numeric_rollup('5m') INTO v_rec;
70                 v_init := true;
71         END IF;
72
73         v_rec.sid := v_info.sid;
74         v_rec.name := v_info.name;
75         v_rec.rollup_time := v_info.rollup_time;
76         v_rec.count_rows[v_offset] := v_info.count_rows;
77         v_rec.avg_value[v_offset] := v_info.avg_value; 
78         v_rec.counter_dev[v_offset] := v_info.counter_dev; 
79
80         IF v_init THEN
81                 INSERT INTO metric_numeric_rollup_5m (sid,name,rollup_time,count_rows,avg_value,counter_dev)
82                         VALUES (v_rec.sid,v_rec.name,v_rec.rollup_time,v_rec.count_rows,v_rec.avg_value,v_rec.counter_dev);
83         ELSE
84                 UPDATE metric_numeric_rollup_5m SET (count_rows,avg_value,counter_dev)
85                         = (v_rec.count_rows,v_rec.avg_value,v_rec.counter_dev)
86                         WHERE rollup_time = v_info.rollup_time  AND sid = v_info.sid  AND name = v_info.name;
87         END IF;
88
89     END LOOP;
90  
91     -- Delete from whence log table
92     DELETE FROM metric_numeric_rollup_queue WHERE WHENCE=v_min_whence AND INTERVAL='5m';
93  
94     v_min_whence:= NULL;
95     v_max_rollup_5:= NULL;
96  
97   END LOOP;
98
99   PERFORM pg_advisory_unlock(43191, v_taskid);
100  
101 RETURN;
102
103 EXCEPTION
104     WHEN RAISE_EXCEPTION THEN
105          PERFORM pg_advisory_unlock(43191, v_taskid);
106          RAISE EXCEPTION '%', SQLERRM;
107     WHEN OTHERS THEN
108          PERFORM pg_advisory_unlock(43191, v_taskid);
109          RAISE NOTICE '%', SQLERRM;
110 END
111 $$
112 LANGUAGE plpgsql
113 SECURITY DEFINER
114 ;
Note: See TracBrowser for help on using the browser.