root/sql/sprocs/stratcon.rollup_metric_numeric_5m.sql

Revision ead2d4e20f8d06ebf7b813d12c1b0fd5cad0d619, 3.5 kB (checked in by Robert Treat <robert@omniti.com>, 5 years ago)

might as well name the file properly

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