Changeset 03c905446907610b20f89a64a0d2810d134e52ec

Show
Ignore:
Timestamp:
03/26/08 22:02:44 (6 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1206568964 +0000
git-parent:

[bce2dd5b1ebb5d3628562c5f487797d4d7da1b46]

git-author:
Denish Patel <denish@omniti.com> 1206568964 +0000
Message:

updated functions for rollups

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/schema.sql

    rb9b5a61 r03c9054  
    4848); 
    4949 
    50 CREATE TABLE stratcon.loading_dock_metric_numeric_s_rullup_60m ( 
     50 
     51CREATE TABLE stratcon.rollup_matrix_numeric_60m( 
     52   sid integer not null, 
     53   name text not null,  
     54   rollup_time timestamp not null,  
     55   count_rows integer, 
     56   avg_value numeric , 
     57   stddev_value numeric, 
     58   min_value numeric , 
     59   max_value numeric , 
     60   PRIMARY KEY(rollup_time,sid,name)); 
     61  
     62CREATE TABLE stratcon.rollup_matrix_numeric_5m ( 
    5163    sid integer NOT NULL, 
    5264    name text NOT NULL, 
     
    89101   v_ex_sid integer; 
    90102   v_new_sid integer; 
    91    v_sql text; 
     103  
    92104BEGIN 
    93105 
     
    99111    INTO v_new_sid; 
    100112 
    101     v_sql:='INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES ('||quote_literal(v_in_id)||','||v_new_sid||');'; 
    102     EXECUTE v_sql;    
     113    INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid); 
    103114    
    104115    RETURN v_new_sid; 
     
    176187 
    177188 
     189 
     190-- Generic rollup function (under progress) 
     191 
     192 
     193 
     194CREATE OR REPLACE FUNCTION stratcon.generic_rollup_metrix_numeric() 
     195RETURNS void 
     196AS $$ 
     197 
     198DECLARE 
     199 
     200v_min_whence TIMESTAMPTZ; 
     201v_max_rollup_5 TIMESTAMPTZ; 
     202v_max_rollup_60 TIMESTAMPTZ; 
     203 
     204BEGIN 
     205 
     206  select min(whence) from stratcon.log_whence_s  
     207         INTO v_min_whence; 
     208          
     209  select max(rollup_time) from  stratcon.rollup_matrix_numeric_5m  
     210         INTO v_max_rollup_5;          
     211  
     212  select max(rollup_time) from  stratcon.rollup_matrix_numeric_60m  
     213         INTO v_max_rollup_60;  
     214          
     215 IF v_max_rollup_5 IS NULL  THEN 
     216   v_max_rollup_5:=timestamp '2008-01-01 00:00:00'; 
     217 END IF; 
     218  
     219 IF v_max_rollup_60 IS NULL  THEN 
     220    v_max_rollup_60:=timestamp '2008-01-01 00:00:00'; 
     221  END IF; 
     222  
     223          
     224  IF v_min_whence > v_max_rollup_5 THEN 
     225   
     226  -- 5 MINUTES ROLLUP 
     227   
     228     PERFORM stratcon.rollup_matrix_numeric_5m(v_min_whence); 
     229      
     230     -- HOURLY ROLLUP 
     231      
     232     IF  date_trunc('H',v_min_whence)!= date_trunc('H',v_max_rollup_60) THEN 
     233      
     234       PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence); 
     235      
     236     END IF; 
     237   
     238  -- DELETE FROM LOG TABLE 
     239   
     240   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence; 
     241   
     242  ELSIF v_min_whence < v_max_rollup_5 THEN 
     243   
     244   -- 5 MINUTES ROLLUP 
     245 
     246     PERFORM stratcon.rollup_matrix_numeric_5m_odd(v_min_whence ,v_max_rollup_5); 
     247      
     248  -- HOURLY ROLLUP 
     249    
     250   DELETE FROM stratcon.rollup_matrix_numeric_60m  
     251       WHERE date_trunc('hour',rollup_time) = date_trunc('hour',v_min_whence); 
     252        
     253         PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence); 
     254          
     255  -- DELETE FROM LOG TABLE 
     256 
     257      DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence; 
     258       
     259  ELSE 
     260   
     261      RETURN; 
     262  
     263  END IF; 
     264  
     265RETURN; 
     266 
     267EXCEPTION 
     268    WHEN RAISE_EXCEPTION THEN 
     269       RAISE EXCEPTION '%', SQLERRM; 
     270    WHEN OTHERS THEN 
     271      RAISE NOTICE '%', SQLERRM; 
     272END 
     273$$ LANGUAGE plpgsql; 
     274 
     275 
     276--- 5 minutes rollup  
     277 
     278 
     279CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m(v_min_whence timestamptz) 
     280RETURNS voidddddd 
     281AS $$ 
     282DECLARE 
     283  
     284 rec stratcon.rollup_matrix_numeric_5m%rowtype; 
     285 v_sql TEXT; 
     286  
     287BEGIN 
     288 
     289 FOR rec IN  
     290                SELECT sid , name, date_trunc('H',whence) + (round(extract('minute' from whence)/5)*5) * '1 minute'::interval as rollup_time, 
     291                      COUNT(1) as count_rows ,AVG(value) as avg_value,STDDEV(value) as stddev_value ,MIN(value) as min_value ,MAX(value) as max_value 
     292                      FROM stratcon.loading_dock_metric_numeric_s 
     293                      WHERE WHENCE < date_trunc('minutes',v_min_whence) AND WHENCE >= date_trunc('minutes',v_min_whence)-'5 minutes'::interval 
     294                GROUP BY rollup_time,sid,name 
     295  
     296       LOOP 
     297  
     298        v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_5m'||                         
     299        '(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES '|| 
     300        '('||rec.sid||', '||quote_literal(rec.name)||', '||quote_literal(rec.rollup_time)||', '||rec.count_rows||', '||rec.avg_value||', '||coalesce(rec.stddev_value,0)|| 
     301        ', '||rec.min_value||', '||rec.max_value||')'; 
     302 
     303 
     304          EXECUTE v_sql; 
     305 
     306 END LOOP; 
     307 
     308  
     309RETURN; 
     310 
     311EXCEPTION 
     312    WHEN RAISE_EXCEPTION THEN 
     313       RAISE EXCEPTION '%', SQLERRM; 
     314    WHEN OTHERS THEN 
     315      RAISE NOTICE '%', SQLERRM; 
     316END 
     317$$ LANGUAGE plpgsql; 
     318 
     319 
     320 
     321CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m_odd(v_min_whence timestamptz,v_max_rollup_5 timestamptz) 
     322RETURNS void 
     323AS $$ 
     324DECLARE 
     325  
     326 rec stratcon.rollup_matrix_numeric_5m%rowtype; 
     327 v_sql TEXT; 
     328  
     329BEGIN 
     330 
     331 DELETE FROM stratcon.rollup_matrix_numeric_5m  
     332 WHERE rollup_time >= date_trunc('minutes',v_min_whence); 
     333  
     334 FOR rec IN  
     335                 SELECT sid , name, date_trunc('H',whence) + (round(extract('minute' from whence)/5)*5) * '1 minute'::interval as rollup_time, 
     336                       COUNT(1) as count_rows ,AVG(value) as avg_value,STDDEV(value) as stddev_value ,MIN(value) as min_value ,MAX(value) as max_value 
     337                       FROM stratcon.loading_dock_metric_numeric_s 
     338                       WHERE WHENCE >= date_trunc('minutes',v_min_whence) AND WHENCE <= date_trunc('minutes',v_max_rollup_5) 
     339                 GROUP BY rollup_time,sid,name 
     340   
     341        LOOP 
     342  
     343     
     344         v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_5m'||                         
     345         '(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES '|| 
     346         '('||rec.sid||', '||quote_literal(rec.name)||', '||quote_literal(rec.rollup_time)||', '||rec.count_rows||', '||rec.avg_value||', '||coalesce(rec.stddev_value,0)|| 
     347         ', '||rec.min_value||', '||rec.max_value||')'; 
     348  
     349  
     350           EXECUTE v_sql; 
     351  
     352  END LOOP; 
     353  
     354 
     355RETURN; 
     356 
     357EXCEPTION 
     358    WHEN RAISE_EXCEPTION THEN 
     359       RAISE EXCEPTION '%', SQLERRM; 
     360    WHEN OTHERS THEN 
     361      RAISE NOTICE '%', SQLERRM; 
     362END 
     363$$ LANGUAGE plpgsql; 
     364 
     365 
     366 
     367 
     368 
     369 
     370--- Hourly rollup 
     371 
     372 
     373CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m(v_min_whence timestamptz) 
     374RETURNS void 
     375AS $$ 
     376DECLARE 
     377  rec stratcon.rollup_matrix_numeric_60m%rowtype; 
     378 v_sql TEXT; 
     379  
     380BEGIN 
     381    FOR rec IN  
     382                SELECT sid , name,date_trunc('hour',rollup_time) as rollup_time,SUM (count_rows) as count_rows ,AVG(avg_value) as avg_value, 
     383                         STDDEV(stddev_value) as stddev_value ,MIN(min_value) as min_value ,MAX(max_value) as max_value 
     384                         FROM stratcon.rollup_matrix_numeric_5m 
     385                           WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) 
     386                   GROUP BY rollup_time,sid,name 
     387        LOOP 
     388        v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_60m'||                         
     389        '(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES '|| 
     390        '('||rec.sid||', '||quote_literal(rec.name)||', '||quote_literal(rec.rollup_time)||', '||rec.count_rows||', '||rec.avg_value||', '||coalesce(rec.stddev_value,0)|| 
     391        ', '||rec.min_value||', '||rec.max_value||')'; 
     392 
     393          EXECUTE v_sql; 
     394     END LOOP; 
     395RETURN; 
     396 
     397EXCEPTION 
     398    WHEN RAISE_EXCEPTION THEN 
     399       RAISE EXCEPTION '%', SQLERRM; 
     400    WHEN OTHERS THEN 
     401      RAISE NOTICE '%', SQLERRM; 
     402END 
     403$$ LANGUAGE plpgsql; 
     404 
     405 
    178406-- GRANTS  
    179407 
     
    186414 GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric_s TO stratcon; 
    187415 GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s_change_log TO stratcon; 
    188  GRANT SELECT,INSERT,UPDATE ON stratcon.log_whence_s TO stratcon; 
     416 GRANT SELECT,INSERT,DELETE ON stratcon.log_whence_s TO stratcon; 
    189417 GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s TO stratcon; 
    190  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric_s_rullup_60m TO stratcon; 
     418 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_60m TO stratcon; 
     419 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_5m TO stratcon; 
    191420 GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon; 
    192421 ALTER TABLE stratcon.seq_sid OWNER TO stratcon;