root/sql/sprocs/rollup_metric_numeric_generic.sql

Revision f0c2368bc65ecc30e12507bd8c0ce078fd060a4a, 5.2 kB (checked in by Robert Treat <robert@omniti.com>, 4 years ago)

fix vars to match real config table, note on busted sql

  • Property mode set to 100644
Line 
1 CREATE OR REPLACE FUNCTION stratcon.rollup_metric_numeric_generic(in_roll text)
2 RETURNS int AS
3 $$
4 DECLARE 
5  v_rec stratcon.metric_numeric_rollup_segment%rowtype;
6  v_segment stratcon.metric_numeric_rollup_segment%rowtype;
7  v_conf RECORD;
8  v_sql TEXT;
9  v_min_whence TIMESTAMPTZ;
10  v_max_rollup TIMESTAMPTZ;
11  v_whence TIMESTAMPTZ;
12  whenceint RECORD;
13  v_taskid int;
14  v_locked boolean;
15  v_this_roll text;
16  v_stored_rollup timestamptz;
17  v_offset integer;
18  v_init boolean := FALSE ;
19  v_i smallint;
20 BEGIN
21
22 v_this_roll := 'rollup_metric_numeric_'||in_roll;
23 SELECT id FROM tasklock WHERE name = v_this_roll INTO v_taskid;
24 IF v_taskid IS NULL THEN
25     INSERT INTO tasklock (id, name) VALUES (nextval('tasklock_id_seq'), v_this_roll)
26       RETURNING id into v_taskid;
27 END IF;
28
29 SELECT pg_try_advisory_lock(43191, v_taskid) INTO v_locked;
30
31 IF v_locked = false THEN
32     RAISE NOTICE 'rollup for metric numeric (%) already running', in_roll;
33     RETURN 0;
34 END IF;
35
36 SELECT * FROM stratcon.metric_numeric_rollup_config WHERE rollup = in_roll INTO v_conf;
37
38 LOOP
39     IF v_i > 10 THEN
40         RETURN 1;
41     END IF;
42
43     v_sql := 'SELECT MIN(whence) FROM metric_numeric_rollup_queue WHERE interval='||in_roll;
44     EXECUTE v_sql INTO v_min_whence;
45        
46     v_sql := 'SELECT MAX(rollup_time) FROM numeric_metric_rollup_'||in_roll;
47     EXECUTE v_sql INTO v_max_rollup;       
48
49 /*
50  -- Insert Log for Hourly rollup
51    
52    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour'
53            INTO v_whence;
54       IF NOT FOUND THEN
55        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour');
56       END IF;
57 */
58  
59     IF v_min_whence <= v_max_rollup THEN
60         v_sql := 'DELETE FROM numeric_metric_rollup_'||in_roll||' WHERE rollup_time = '||quote_ident(v_min_whence); 
61         EXECUTE v_sql;
62  
63     END IF;
64
65 /* THIS V_SQL NEEDS TO BE REWRITTEN TO GET THE VALUE FROM ARRAY BASED TABLES */
66     v_sql := 'SELECT sid, name, '|| v_min_whence || ' as rollup_time,
67                      SUM(1) as count_rows ,(SUM(avg_value*1)/SUM(1)) as avg_value,
68                      (SUM(counter_dev*1)/SUM(1)) as counter_dev
69               FROM metric_numeric_rollup_'||v_conf.dependent_on||'
70               WHERE rollup_time<= '|| v_min_whence ||' AND rollup_time > ' || v_min_whence - v_conf.seconds * '1 second'::interval || '
71               GROUP BY sid, name';
72
73     FOR v_rec IN EXECUTE v_sql LOOP
74             v_stored_rollup := floor(extract('epoch' from v_rec.rollup_time)/v_conf.span)+vconf.window;
75             v_offset := floor( (extract('epoch' from v_rec.rollup_time) - v_stored_rollup) / v_conf.seconds );
76  
77             --v_offset := ( 12*(extract('hour' from v_info.rollup_time))+floor(extract('minute' from v_info.rollup_time)/5) );
78             --v_stored_rollup := v_info.rollup_time::date;
79             -- RAISE NOTICE 'sid %, name %, rollup_time %, offset %', v_rec.sid, v_rec.name, v_stored_rollup, v_offset;
80
81             v_sql := 'SELECT * FROM metric_numeric_rollup_'||in_roll||' WHERE rollup_time = '||quote_literal(v_stored_rollup);
82             v_sql := v_sql ||' and sid='||v_rec.sid||' and name = '|| quote_literal(v_rec.name);
83
84             EXECUTE v_sql INTO v_segment;
85             IF v_segment IS NOT NULL THEN
86                 v_sql := 'SELECT * FROM stratcon.init_metric_numeric_rollup_segment('||quote_literal(in_roll)||')';
87                 EXECUTE v_sql INTO v_segment;
88                 v_init := true;
89                 RAISE NOTICE 'didnt find sid %, name %, rollup_time %, offset %', v_rec.sid, v_rec.name, v_stored_rollup, v_offset;
90             END IF;
91    
92             v_segment.sid := v_rec.sid;
93             v_segment.name := v_rec.name;
94             v_segment.count_rows[v_offset] := v_rec.count_rows;
95             v_segment.avg_value[v_offset] := v_rec.avg_value;
96             v_segment.counter_dev[v_offset] := v_rec.counter_dev;
97
98             IF v_init THEN
99                 v_sql := 'INSERT INTO metric_numeric_rollup_'||in_roll||' (sid,name,rollup_time,count_rows,avg_value,counter_dev)
100                     VALUES ('|| v_segment.sid||','||quote_literal(v_segment.name)||','||quote_literal(v_stored_rollup)||','||v_segment.count_rows
101                     ||','||v_segment.avg_value||','||v_segment.counter_dev||')';
102                 EXECUTE v_sql;
103                 v_init := false;
104             ELSE
105                 v_sql := 'UPDATE metric_numeric_rollup_'||in_roll;
106                 v_sql := v_sql || 'SET (count_rows,avg_value,counter_dev) = ('||v_rec.count_rows||','||v_rec.avg_value||','||v_rec.counter_dev||')';
107                 v_sql := v_sql || 'WHERE rollup_time = '||v_stored_rollup||'  AND sid = '||v_info.sid||'  AND name = '||quote_literal(v_info.name);
108             END IF;
109
110     v_i := v_i + 1;
111     END LOOP;
112
113   -- Delete from whence log table
114  
115   DELETE FROM metric_numeric_rollup_queue WHERE WHENCE=v_min_whence AND INTERVAL=in_roll;
116  
117   v_min_whence := NULL;
118   v_max_rollup := NULL;
119
120  END LOOP;
121
122   perform pg_advisory_unlock(43191, v_taskid);
123
124   RETURN 0;
125
126 EXCEPTION
127     WHEN RAISE_EXCEPTION THEN
128        perform pg_advisory_unlock(43191, v_taskid);
129        RAISE EXCEPTION '%', SQLERRM;
130     WHEN OTHERS THEN
131        perform pg_advisory_unlock(43191, v_taskid);
132        RAISE NOTICE '%', SQLERRM;
133 END
134 $$ LANGUAGE plpgsql;
Note: See TracBrowser for help on using the browser.