Changeset 51b2a89650aa59a060f72e44e0936e37650c04d6 for sql/sprocs
- Timestamp:
- 10/31/11 14:28:24 (2 years ago)
- git-parent:
- Files:
-
- sql/sprocs/stratcon.drop_old_metrics.sql (added)
- sql/sprocs/stratcon.rollup_metric_numeric.sql (modified) (7 diffs)
Legend:
- Unmodified
- Added
- Removed
- Modified
- Copied
- Moved
sql/sprocs/stratcon.rollup_metric_numeric.sql
r603ae82 r51b2a89 15 15 v_offset INTEGER; 16 16 v_init BOOLEAN := FALSE; 17 v_i SMALLINT ;17 v_i SMALLINT := 0; 18 18 v_temprec RECORD; 19 19 v_count INTEGER; 20 v_max_segs INTEGER := 12; -- maximum number of segments to process in one go 20 21 BEGIN 21 22 v_i := 0;23 22 24 23 -- Get rollup config based on given name, and fail if its wrong name. 25 24 SELECT * FROM metric_numeric_rollup_config WHERE rollup = in_roll INTO v_conf; 26 25 IF NOT FOUND THEN 27 raise exception'Given rollup name is invalid! [%]', in_roll;26 RAISE EXCEPTION 'Given rollup name is invalid! [%]', in_roll; 28 27 END IF; 29 28 … … 42 41 END IF; 43 42 44 v_current_whence := 'epoch'::timestamptz + '1 second'::INTERVAL * v_conf.seconds * floor(extract( epoch FROM now() ) / v_conf.seconds);43 v_current_whence := 'epoch'::timestamptz + '1 second'::INTERVAL * v_conf.seconds * floor(extract( epoch FROM now() ) / v_conf.seconds); 45 44 46 45 LOOP 47 IF v_i > 12 THEN 48 perform pg_advisory_unlock(43191, v_taskid); 46 IF v_i > v_max_segs THEN 47 PERFORM pg_advisory_unlock(43191, v_taskid); 48 RAISE NOTICE 'processed % segments, exiting', v_i - 1; 49 49 RETURN 1; 50 50 END IF; … … 62 62 v_temprec.use_whence := 'epoch'::timestamptz + '1 second'::INTERVAL * v_temprec.seconds * floor(extract( epoch FROM v_temprec.use_whence ) / v_temprec.seconds); 63 63 64 RAISE NOTICE '(%,%)',v_temprec.rollup, v_temprec.use_whence;64 RAISE NOTICE 'queueing for rollup: interval = %, whence = %',v_temprec.rollup, v_temprec.use_whence; 65 65 -- Poor mans UPSERT :) 66 66 INSERT INTO metric_numeric_rollup_queue ("interval", whence) … … 71 71 END LOOP; 72 72 73 RAISE NOTICE 'processing: interval = %, whence = %', in_roll, v_min_whence; 73 74 IF in_roll = '5m' THEN 74 75 v_sql := 'SELECT * FROM stratcon.window_robust_derive('||quote_literal(v_min_whence)||')'; … … 82 83 v_sql := v_sql || ' GROUP BY sid, name'; 83 84 END IF; 84 RAISE NOTICE'v_sql was (%)',v_sql;85 RAISE DEBUG 'v_sql was (%)',v_sql; 85 86 86 87 FOR v_rec IN EXECUTE v_sql LOOP … … 97 98 v_segment := stratcon.init_metric_numeric_rollup( in_roll ); 98 99 v_init := true; 99 RAISE NOTICE 'didnt find sid %, name %, rollup_time %, offset%', v_rec.sid, v_rec.name, v_stored_rollup_tm, v_offset;100 RAISE DEBUG 'didn''t find, inserting: sid = %, name = %, rollup_time = %, offset = %', v_rec.sid, v_rec.name, v_stored_rollup_tm, v_offset; 100 101 END IF; 101 102 … … 124 125 -- Delete from whence log table 125 126 DELETE FROM metric_numeric_rollup_queue WHERE whence=v_min_whence AND "interval"=in_roll; 126 127 RAISE NOTICE 'done, removed from queue: interval = %, whence = %', in_roll, v_min_whence; 128 127 129 v_min_whence := NULL; 128 130 END LOOP;