root/sql/sprocs/stratcon.rollup_metric_numeric_5m.sql

Revision b354e99dbd9c5a7fed905a1de2014ea0ed2dc507, 3.6 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 4 years ago)

sweep

  • 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     IF v_min_whence <= v_max_rollup_5 THEN
52 -- do we still need this? i think "we" dont, but maybe some people do?
53        DELETE FROM metric_numeric_rollup_5m
54            WHERE rollup_time = v_min_whence;
55     END IF;
56
57     FOR v_info IN SELECT * FROM stratcon.window_robust_derive(v_min_whence) LOOP
58
59         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) );   
60         v_info.rollup_time := date_trunc('day', v_info.rollup_time AT TIME ZONE 'UTC') AT TIME ZONE 'UTC';
61
62         v_init := false;
63         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;
64         IF NOT FOUND THEN
65                 SELECT * FROM stratcon.init_metric_numeric_rollup('5m') INTO v_rec;
66                 v_init := true;
67         END IF;
68
69         v_rec.sid := v_info.sid;
70         v_rec.name := v_info.name;
71         v_rec.rollup_time := v_info.rollup_time;
72         v_rec.count_rows[v_offset] := v_info.count_rows;
73         v_rec.avg_value[v_offset] := v_info.avg_value; 
74         v_rec.counter_dev[v_offset] := v_info.counter_dev; 
75
76         IF v_init THEN
77                 INSERT INTO metric_numeric_rollup_5m (sid,name,rollup_time,count_rows,avg_value,counter_dev)
78                         VALUES (v_rec.sid,v_rec.name,v_rec.rollup_time,v_rec.count_rows,v_rec.avg_value,v_rec.counter_dev);
79         ELSE
80                 UPDATE metric_numeric_rollup_5m SET (count_rows,avg_value,counter_dev)
81                         = (v_rec.count_rows,v_rec.avg_value,v_rec.counter_dev)
82                         WHERE rollup_time = v_info.rollup_time  AND sid = v_info.sid  AND name = v_info.name;
83         END IF;
84
85     END LOOP;
86  
87     -- Delete from whence log table
88     DELETE FROM metric_numeric_rollup_queue WHERE WHENCE=v_min_whence AND INTERVAL='5m';
89  
90     v_min_whence:= NULL;
91     v_max_rollup_5:= NULL;
92  
93   END LOOP;
94
95   PERFORM pg_advisory_unlock(43191, v_taskid);
96  
97 RETURN;
98
99 EXCEPTION
100     WHEN RAISE_EXCEPTION THEN
101          PERFORM pg_advisory_unlock(43191, v_taskid);
102          RAISE EXCEPTION '%', SQLERRM;
103     WHEN OTHERS THEN
104          PERFORM pg_advisory_unlock(43191, v_taskid);
105          RAISE NOTICE '%', SQLERRM;
106 END
107 $$
108 LANGUAGE plpgsql
109 SECURITY DEFINER
110 ;
Note: See TracBrowser for help on using the browser.