Show
Ignore:
Timestamp:
03/28/08 19:12:13 (6 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1206731533 +0000
git-parent:

[3a1a63f5dd675d5b58def4ed6e2727db597f31b5]

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

Final 5 minutes and 1 hour rollup functions

Files:

Legend:

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

    r3a1a63f r5eaa178  
    8080CREATE TABLE stratcon.log_whence_s ( 
    8181    whence timestamp with time zone NOT NULL, 
    82     PRIMARY KEY(whence) 
     82    interval varchar2(20, 
     83    PRIMARY KEY(whence,interval) 
    8384); 
    8485 
     
    9293    CACHE 1; 
    9394 
    94  
    95 -- Function To generate SID from ID  
    96  
    97 CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid) 
    98 RETURNS integer 
    99 AS $$ 
    100 DECLARE 
    101    v_ex_sid integer; 
    102    v_new_sid integer; 
    103   
    104 BEGIN 
    105  
    106 SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id 
    107   INTO v_ex_sid; 
    108  
    109  IF NOT FOUND THEN 
    110     SELECT nextval('stratcon.seq_sid')  
    111     INTO v_new_sid; 
    112  
    113     INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid); 
    114     
    115     RETURN v_new_sid; 
    116  ELSE 
    117       RETURN v_ex_sid; 
    118  END IF; 
    119  
    120 END 
    121 $$ LANGUAGE plpgsql; 
    122  
    123 -- Trigger Function to change Metrix Text Changes  
    124  
    125 CREATE TRIGGER loading_dock_metric_text_s_change_log 
    126     AFTER INSERT ON loading_dock_metric_text_s 
    127     FOR EACH ROW 
    128     EXECUTE PROCEDURE loading_dock_metric_text_s_change_log(); 
    129  
    130 CREATE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger 
    131     AS $$ 
    132 DECLARE 
    133     v_oldvalue TEXT; 
    134 BEGIN 
    135  
    136 IF TG_OP = 'INSERT' THEN 
    137     SELECT value FROM  stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name  
    138         AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log  
    139                         WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name ) 
    140     INTO v_oldvalue; 
    141  
    142     IF v_oldvalue IS DISTINCT FROM NEW.value THEN 
    143  
    144         INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value) 
    145             VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);  
    146  
    147     END IF; 
    148  
    149 ELSE 
    150         RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table'; 
    151 END IF; 
    152  
    153     RETURN NULL; 
    154  
    155 END 
    156 $$ 
    157     LANGUAGE plpgsql; 
    158      
    159 -- Trigger on Metrix Numeric to log last inserted timestamp  
    160  
    161 CREATE TRIGGER loading_dock_metric_numeric_s_whence_log 
    162     AFTER INSERT ON loading_dock_metric_numeric_s 
    163     FOR EACH ROW 
    164     EXECUTE PROCEDURE loading_dock_metric_numeric_s_whence_log(); 
    165  
    166      
    167 CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log()  
    168 RETURNS trigger 
    169 AS $$ 
    170 DECLARE 
    171 v_whence timestamptz; 
    172 BEGIN 
    173 IF TG_OP = 'INSERT' THEN 
    174     SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval 
    175      INTO v_whence; 
    176    IF NOT FOUND THEN 
    177        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval); 
    178    END IF; 
    179 END IF; 
    180  
    181     RETURN NULL; 
    182 END 
    183 $$ 
    184     LANGUAGE plpgsql; 
    185  
    186  
    187  
    188 -- Generic rollup function (under progress) 
    189  
    190  
    191  
    192 CREATE OR REPLACE FUNCTION stratcon.generic_rollup_metrix_numeric() 
    193 RETURNS void 
    194 AS $$ 
    195  
    196 DECLARE 
    197  
    198 v_min_whence TIMESTAMPTZ; 
    199 v_max_rollup_5 TIMESTAMPTZ; 
    200 v_cur_time TIMESTAMPTZ; 
    201  
    202 BEGIN 
    203  
    204   select min(whence) from stratcon.log_whence_s  
    205          INTO v_min_whence; 
    206           
    207   select max(rollup_time) from  stratcon.rollup_matrix_numeric_5m  
    208          INTO v_max_rollup_5;          
    209   select now() 
    210          INTO v_cur_time; 
    211           
    212  IF v_max_rollup_5 IS NULL  THEN 
    213    v_max_rollup_5:=timestamp '2008-01-01 00:00:00'; 
    214  END IF; 
    215   
    216           
    217   IF v_min_whence >= v_max_rollup_5 THEN 
    218    
    219   -- 5 MINUTES ROLLUP 
    220    
    221      PERFORM stratcon.rollup_matrix_numeric_5m(v_min_whence); 
    222       
    223      -- HOURLY ROLLUP 
    224       
    225      IF  extract('minutes' from v_cur_time)>55 and extract('minutes' from v_cur_time)<59 THEN 
    226       
    227        PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence); 
    228       
    229      END IF; 
    230    
    231   -- DELETE FROM LOG TABLE 
    232    
    233    DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence; 
    234    
    235   ELSIF v_min_whence < v_max_rollup_5 THEN 
    236    
    237    -- 5 MINUTES ROLLUP 
    238  
    239 DELETE FROM stratcon.rollup_matrix_numeric_5m  
    240  WHERE rollup_time = date_trunc('minutes',v_min_whence); 
    241       
    242       PERFORM stratcon.rollup_matrix_numeric_5m(v_min_whence ,v_max_rollup_5); 
    243       
    244   -- HOURLY ROLLUP 
    245     
    246    DELETE FROM stratcon.rollup_matrix_numeric_60m  
    247        WHERE date_trunc('hour',rollup_time) = date_trunc('hour',v_min_whence); 
    248         
    249          PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence); 
    250           
    251   -- DELETE FROM LOG TABLE 
    252  
    253       DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence; 
    254        
    255   ELSE 
    256    
    257       RETURN; 
    258   
    259   END IF; 
    260   
    261 RETURN; 
    262  
    263 EXCEPTION 
    264     WHEN RAISE_EXCEPTION THEN 
    265        RAISE EXCEPTION '%', SQLERRM; 
    266     WHEN OTHERS THEN 
    267       RAISE NOTICE '%', SQLERRM; 
    268 END 
    269 $$ LANGUAGE plpgsql; 
    270  
    271 --- 5 minutes rollup  
    272  
    273 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m(v_min_whence timestamptz) 
    274 RETURNS void 
    275 AS $$ 
    276 DECLARE 
    277   
    278  rec stratcon.rollup_matrix_numeric_5m%rowtype; 
    279  v_sql TEXT; 
    280   
    281 BEGIN 
    282  
    283  FOR rec IN  
    284                 SELECT sid , name, date_trunc('H',whence) + (round(extract('minute' from whence)/5)*5) * '1 minute'::interval as rollup_time, 
    285                       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 
    286                       FROM stratcon.loading_dock_metric_numeric_s 
    287                       WHERE WHENCE < date_trunc('minutes',v_min_whence) AND WHENCE >= date_trunc('minutes',v_min_whence)-'5 minutes'::interval 
    288                 GROUP BY rollup_time,sid,name 
    289   
    290        LOOP 
    291   
    292         INSERT INTO stratcon.rollup_matrix_numeric_5m 
    293          (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES  
    294          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
    295          
    296   
    297  END LOOP; 
    298  
    299   
    300 RETURN; 
    301  
    302 EXCEPTION 
    303     WHEN RAISE_EXCEPTION THEN 
    304        RAISE EXCEPTION '%', SQLERRM; 
    305     WHEN OTHERS THEN 
    306       RAISE NOTICE '%', SQLERRM; 
    307 END 
    308 $$ LANGUAGE plpgsql; 
    309  
    310 --- Hourly rollup 
    311  
    312  
    313 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m(v_min_whence timestamptz) 
    314 RETURNS void 
    315 AS $$ 
    316 DECLARE 
    317   rec stratcon.rollup_matrix_numeric_60m%rowtype; 
    318  v_sql TEXT; 
    319   
    320 BEGIN 
    321     FOR rec IN  
    322                 SELECT sid , name,date_trunc('hour',rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    323                          SQRT((SUM((count_rows-1)*(POWER(stddev_value,2)+POWER(avg_value,2)))/(SUM(count_rows)-1)))-(power(SUM(avg_value*count_rows)/SUM(count_rows),2)) as stddev_value, 
    324                          MIN(min_value) as min_value ,MAX(max_value) as max_value 
    325                          FROM stratcon.rollup_matrix_numeric_5m 
    326                            WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) 
    327                    GROUP BY date_trunc('hour',rollup_time),sid,name 
    328         LOOP 
    329        
    330           INSERT INTO stratcon.rollup_matrix_numeric_60m 
    331           (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 
    332           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
    333            
    334      END LOOP; 
    335 RETURN; 
    336  
    337 EXCEPTION 
    338     WHEN RAISE_EXCEPTION THEN 
    339        RAISE EXCEPTION '%', SQLERRM; 
    340     WHEN OTHERS THEN 
    341       RAISE NOTICE '%', SQLERRM; 
    342 END 
    343 $$ LANGUAGE plpgsql; 
    34495 
    34596 
     
    360111 GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon; 
    361112 ALTER TABLE stratcon.seq_sid OWNER TO stratcon; 
     113  
     114  
     115-- Function To generate SID from ID  
     116 
     117CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid) 
     118RETURNS integer 
     119AS $$ 
     120DECLARE 
     121   v_ex_sid integer; 
     122   v_new_sid integer; 
     123  
     124BEGIN 
     125 
     126SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id 
     127  INTO v_ex_sid; 
     128 
     129 IF NOT FOUND THEN 
     130    SELECT nextval('stratcon.seq_sid')  
     131    INTO v_new_sid; 
     132 
     133    INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid); 
     134    
     135    RETURN v_new_sid; 
     136 ELSE 
     137      RETURN v_ex_sid; 
     138 END IF; 
     139 
     140END 
     141$$ LANGUAGE plpgsql; 
     142 
     143-- Trigger Function to change Metrix Text Changes  
     144 
     145CREATE TRIGGER loading_dock_metric_text_s_change_log 
     146    AFTER INSERT ON loading_dock_metric_text_s 
     147    FOR EACH ROW 
     148    EXECUTE PROCEDURE loading_dock_metric_text_s_change_log(); 
     149 
     150CREATE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger 
     151    AS $$ 
     152DECLARE 
     153    v_oldvalue TEXT; 
     154BEGIN 
     155 
     156IF TG_OP = 'INSERT' THEN 
     157    SELECT value FROM  stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name  
     158        AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log  
     159                        WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name ) 
     160    INTO v_oldvalue; 
     161 
     162    IF v_oldvalue IS DISTINCT FROM NEW.value THEN 
     163 
     164        INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value) 
     165            VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);  
     166 
     167    END IF; 
     168 
     169ELSE 
     170        RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table'; 
     171END IF; 
     172 
     173    RETURN NULL; 
     174 
     175END 
     176$$ 
     177    LANGUAGE plpgsql; 
     178     
     179-- Trigger on Metrix Numeric to log last inserted timestamp  
     180 
     181CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log()  
     182RETURNS trigger 
     183AS $$ 
     184DECLARE 
     185v_whence timestamptz; 
     186BEGIN 
     187IF TG_OP = 'INSERT' THEN 
     188   SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval and interval='5 minutes' 
     189     INTO v_whence; 
     190   IF NOT FOUND THEN 
     191       INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval,'5 minutes'); 
     192    END IF; 
     193END IF; 
     194 
     195    RETURN NULL; 
     196END 
     197$$ 
     198    LANGUAGE plpgsql; 
     199 
     200-- 5 minutes rollup 
     201 
     202CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m() 
     203RETURNS void 
     204AS $$ 
     205DECLARE 
     206  
     207 rec stratcon.rollup_matrix_numeric_5m%rowtype; 
     208 v_sql TEXT; 
     209 v_min_whence TIMESTAMPTZ; 
     210 v_max_rollup_5 TIMESTAMPTZ; 
     211 v_whence TIMESTAMPTZ; 
     212  
     213BEGIN 
     214 
     215 SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes' 
     216        INTO v_min_whence; 
     217         
     218 SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_5m  
     219         INTO v_max_rollup_5;         
     220  
     221 IF v_min_whence < v_max_rollup_5 THEN 
     222 
     223   DELETE FROM stratcon.rollup_matrix_numeric_5m  
     224                WHERE rollup_time = v_min_whence; 
     225   
     226  ELSIF  v_min_whence = v_max_rollup_5 THEN 
     227  
     228  DELETE FROM stratcon.log_whence_s  
     229        WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes'; 
     230         
     231    RETURN;         
     232 
     233 END IF; 
     234 
     235 FOR rec IN  
     236                SELECT sid , name,v_min_whence as rollup_time, 
     237                      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 
     238                      FROM stratcon.loading_dock_metric_numeric_s 
     239                      WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval 
     240                GROUP BY rollup_time,sid,name 
     241  
     242       LOOP 
     243     
     244        INSERT INTO stratcon.rollup_matrix_numeric_5m 
     245         (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES  
     246         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     247         
     248   END LOOP; 
     249 
     250  -- Insert Log for Hourly rollup 
     251   
     252  SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour' 
     253          INTO v_whence; 
     254     IF NOT FOUND THEN 
     255      INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour'); 
     256   END IF; 
     257    
     258    
     259  -- Delete from whence log table 
     260   
     261  DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes'; 
     262  
     263RETURN; 
     264 
     265EXCEPTION 
     266    WHEN RAISE_EXCEPTION THEN 
     267       RAISE EXCEPTION '%', SQLERRM; 
     268    WHEN OTHERS THEN 
     269      RAISE NOTICE '%', SQLERRM; 
     270END 
     271$$ LANGUAGE plpgsql; 
     272 
     273-- 1 hourl rollup 
     274 
     275 
     276CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m() 
     277RETURNS void 
     278AS $$ 
     279DECLARE 
     280  rec stratcon.rollup_matrix_numeric_60m%rowtype; 
     281  v_sql TEXT; 
     282  v_min_whence TIMESTAMPTZ; 
     283  v_max_rollup_5 TIMESTAMPTZ; 
     284  
     285BEGIN 
     286 
     287  SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour' 
     288         INTO v_min_whence; 
     289          
     290  SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_60m  
     291         INTO v_max_rollup_5;     
     292          
     293  IF v_min_whence <= v_max_rollup_5 THEN 
     294   
     295  DELETE FROM stratcon.rollup_matrix_numeric_60m  
     296       WHERE rollup_time= v_min_whence; 
     297 
     298  END IF; 
     299   
     300    FOR rec IN  
     301                SELECT sid,name,date_trunc('hour',rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
     302                         SQRT((SUM((count_rows-1)*(POWER(stddev_value,2)+POWER(avg_value,2)))/(SUM(count_rows)-1)))-(power(SUM(avg_value*count_rows)/SUM(count_rows),2)) as stddev_value, 
     303                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
     304                         FROM stratcon.rollup_matrix_numeric_5m 
     305                           WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) 
     306                   GROUP BY date_trunc('hour',rollup_time),sid,name 
     307        LOOP 
     308       
     309          INSERT INTO stratcon.rollup_matrix_numeric_60m 
     310          (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 
     311          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     312           
     313     END LOOP; 
     314 
     315 
     316DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour'; 
     317 
     318RETURN; 
     319 
     320EXCEPTION 
     321    WHEN RAISE_EXCEPTION THEN 
     322       RAISE EXCEPTION '%', SQLERRM; 
     323    WHEN OTHERS THEN 
     324      RAISE NOTICE '%', SQLERRM; 
     325END 
     326$$ LANGUAGE plpgsql; 
     327 
     328 
    362329 
    363330COMMIT; 
    364