Changeset 3b5d1e9688429ed8e55ebcc0a750dda9a2fe54b4

Show
Ignore:
Timestamp:
05/09/08 20:39:46 (5 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1210365586 +0000
git-parent:

[7e71217def419c242b081547a970d47a5bf3f20d]

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

Updated whence column to timestamp instead of timestaptz. updated functions to check for already running function. Added 20 minutes rollup.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • sql/schema.sql

    r7e71217 r3b5d1e9  
    66    sid integer NOT NULL, 
    77    remote_address inet, 
    8     whence timestamp with time zone NOT NULL, 
     8    whence timestamp NOT NULL, 
    99    id uuid NOT NULL, 
    1010    target text NOT NULL, 
    …… 
    1616CREATE TABLE stratcon.loading_dock_status_s ( 
    1717    sid integer NOT NULL, 
    18     whence timestamp with time zone NOT NULL, 
     18    whence timestamp NOT NULL, 
    1919    state character(1) NOT NULL, 
    2020    availability character(1) NOT NULL, 
    …… 
    2626CREATE TABLE stratcon.loading_dock_metric_numeric_s ( 
    2727    sid integer NOT NULL, 
    28     whence timestamp with time zone NOT NULL, 
     28    whence timestamp NOT NULL, 
    2929    name text NOT NULL, 
    3030    value numeric, 
    …… 
    3434CREATE TABLE stratcon.loading_dock_metric_text_s ( 
    3535    sid integer NOT NULL, 
    36     whence timestamp with time zone NOT NULL, 
     36    whence timestamp NOT NULL, 
    3737    name text NOT NULL, 
    3838    value text, 
    …… 
    4242CREATE TABLE stratcon.loading_dock_metric_text_s_change_log ( 
    4343    sid integer NOT NULL, 
    44     whence timestamp with time zone NOT NULL, 
     44    whence timestamp NOT NULL, 
    4545    name text NOT NULL, 
    4646    value text, 
    …… 
    8585    sid integer NOT NULL, 
    8686    name text NOT NULL, 
    87     rollup_time timestamp with time zone NOT NULL, 
     87    rollup_time timestamp NOT NULL, 
    8888    count_rows integer, 
    8989    avg_value numeric, 
    …… 
    9494); 
    9595 
     96CREATE TABLE stratcon.rollup_matrix_numeric_20m ( 
     97    sid integer NOT NULL, 
     98    name text NOT NULL, 
     99    rollup_time timestamp NOT NULL, 
     100    count_rows integer, 
     101    avg_value numeric, 
     102    stddev_value numeric, 
     103    min_value numeric, 
     104    max_value numeric, 
     105    PRIMARY KEY (rollup_time,sid,name) 
     106); 
     107 
    96108CREATE TABLE stratcon.map_uuid_to_sid ( 
    97109    id uuid NOT NULL, 
    …… 
    101113 
    102114CREATE TABLE stratcon.log_whence_s ( 
    103     whence timestamp with time zone NOT NULL, 
     115    whence timestamp NOT NULL, 
    104116    interval varchar2(20, 
    105117    PRIMARY KEY(whence,interval) 
     118); 
     119 
     120CREATE TABLE stratcon.rollup_runner ( 
     121  rollup_table character varying(100), 
     122  runner character varying(22) 
    106123); 
    107124 
    …… 
    131148 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_60m TO stratcon; 
    132149 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_5m TO stratcon; 
     150 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_20m TO stratcon; 
    133151 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_6hours TO stratcon; 
    134152 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_12hours TO stratcon; 
    135153 GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon; 
     154 GRANT SELECT,INSERT,UPDATE,DELETE ON stratcon.rollup_runner TO stratcon; 
    136155 ALTER TABLE stratcon.seq_sid OWNER TO stratcon; 
    137156  
    …… 
    231250 rec stratcon.rollup_matrix_numeric_5m%rowtype; 
    232251 v_sql TEXT; 
    233  v_min_whence TIMESTAMPTZ; 
    234  v_max_rollup_5 TIMESTAMPTZ; 
    235  v_whence TIMESTAMPTZ; 
     252 v_min_whence TIMESTAMP; 
     253 v_max_rollup_5 TIMESTAMP; 
     254 v_whence TIMESTAMP; 
    236255 rows INT; 
     256 v_nrunning INT; 
     257 v_self VARCHAR(22); 
    237258  
    238259BEGIN 
     260 
     261  SELECT COUNT(1) INTO v_nrunning 
     262    from stratcon.rollup_runner t, pg_stat_activity a 
     263   where rollup_table ='rollup_matrix_numeric_5m' 
     264     and runner = procpid || '.' || date_part('epoch',backend_start); 
     265 
     266  IF v_nrunning > 0 THEN 
     267    RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running'; 
     268    RETURN ; 
     269  END IF; 
     270 
     271  SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) 
     272    FROM pg_stat_activity 
     273   WHERE procpid = pg_backend_pid(); 
     274 
     275  IF v_self IS NULL THEN 
     276    RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot self-identify'; 
     277  END IF; 
     278 
     279  v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_5m'''; 
     280 
     281  EXECUTE v_sql; 
    239282 
    240283 SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes' 
    …… 
    244287         INTO v_max_rollup_5;         
    245288  
    246  -- Insert Log for Hourly rollup 
    247     
    248    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour' 
     289 -- Insert Log for 20 minutes rollup 
     290    
     291   SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval and interval='20 minutes' 
    249292           INTO v_whence; 
    250293      IF NOT FOUND THEN 
    251        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour'); 
     294       INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval,'20 minutes'); 
    252295   END IF; 
    253296    
    …… 
    279322  DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes'; 
    280323  
     324  UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; 
     325   
    281326RETURN; 
    282327 
    283328EXCEPTION 
    284329    WHEN RAISE_EXCEPTION THEN 
     330       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; 
    285331       RAISE EXCEPTION '%', SQLERRM; 
    286332    WHEN OTHERS THEN 
     333      UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; 
    287334      RAISE NOTICE '%', SQLERRM; 
    288335END 
    289336$$ LANGUAGE plpgsql; 
    290337 
    291 -- 1 hourl rollup 
     338 
     339-- 20 minutes rollup 
     340 
     341CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_20m() 
     342RETURNS void 
     343AS $$ 
     344DECLARE 
     345  
     346 rec stratcon.rollup_matrix_numeric_20m%rowtype; 
     347 v_sql TEXT; 
     348 v_min_whence TIMESTAMP; 
     349 v_max_rollup_20 TIMESTAMP; 
     350 v_whence TIMESTAMP; 
     351 rows INT; 
     352 v_nrunning INT; 
     353 v_self VARCHAR(22); 
     354 
     355BEGIN 
     356 
     357  SELECT COUNT(1) INTO v_nrunning 
     358    from stratcon.rollup_runner t, pg_stat_activity a 
     359   where rollup_table ='rollup_matrix_numeric_20m' 
     360     and runner = procpid || '.' || date_part('epoch',backend_start); 
     361 
     362  IF v_nrunning > 0 THEN 
     363    RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running'; 
     364    RETURN ; 
     365  END IF; 
     366 
     367  SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) 
     368    FROM pg_stat_activity 
     369   WHERE procpid = pg_backend_pid(); 
     370 
     371  IF v_self IS NULL THEN 
     372    RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot self-identify'; 
     373  END IF; 
     374 
     375  v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_20m'''; 
     376 
     377  EXECUTE v_sql; 
     378 
     379 SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='20 minutes' 
     380        INTO v_min_whence; 
     381         
     382 SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_20m  
     383         INTO v_max_rollup_20;         
     384  
     385 -- Insert Log for Hourly rollup 
     386    
     387   SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour' 
     388           INTO v_whence; 
     389      IF NOT FOUND THEN 
     390       INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour'); 
     391   END IF; 
     392    
     393 IF v_min_whence <= v_max_rollup_20 THEN 
     394 
     395   DELETE FROM stratcon.rollup_matrix_numeric_20m  
     396                WHERE rollup_time = v_min_whence; 
     397  
     398 END IF; 
     399 
     400 FOR rec IN  
     401                SELECT sid , name,v_min_whence as rollup_time, 
     402                       SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
     403                       stddev(stddev_value) as stddev_value, 
     404                       MIN(min_value) as min_value ,MAX(max_value) as max_value 
     405                       FROM stratcon.rollup_matrix_numeric_5m 
     406                      WHERE ROLLUP_TIME<= v_min_whence AND WHENCE > v_min_whence -'20 minutes'::interval 
     407                GROUP BY rollup_time,sid,name 
     408  
     409       LOOP 
     410     
     411         
     412        INSERT INTO stratcon.rollup_matrix_numeric_20m 
     413         (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES  
     414         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     415         
     416   END LOOP; 
     417 
     418  -- Delete from whence log table 
     419   
     420  DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='20 minutes'; 
     421  
     422  UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; 
     423   
     424RETURN; 
     425 
     426EXCEPTION 
     427    WHEN RAISE_EXCEPTION THEN 
     428       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; 
     429       RAISE EXCEPTION '%', SQLERRM; 
     430    WHEN OTHERS THEN 
     431      UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; 
     432      RAISE NOTICE '%', SQLERRM; 
     433END 
     434$$ LANGUAGE plpgsql; 
     435 
     436 
     437-- 1 hour rollup 
    292438 
    293439 
    …… 
    298444  rec stratcon.rollup_matrix_numeric_60m%rowtype; 
    299445  v_sql TEXT; 
    300   v_min_whence TIMESTAMPTZ; 
    301   v_max_rollup_5 TIMESTAMPTZ; 
    302   v_whence TIMESTAMPTZ; 
     446  v_min_whence TIMESTAMP; 
     447  v_max_rollup_60 TIMESTAMP; 
     448  v_whence TIMESTAMP; 
     449  v_nrunning INT; 
     450  v_self VARCHAR(22); 
     451 
    303452BEGIN 
     453 
     454  SELECT COUNT(1) INTO v_nrunning 
     455    from stratcon.rollup_runner t, pg_stat_activity a 
     456   where rollup_table ='rollup_matrix_numeric_60m' 
     457     and runner = procpid || '.' || date_part('epoch',backend_start); 
     458 
     459  IF v_nrunning > 0 THEN 
     460    RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running'; 
     461    RETURN ; 
     462  END IF; 
     463 
     464  SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) 
     465    FROM pg_stat_activity 
     466   WHERE procpid = pg_backend_pid(); 
     467 
     468  IF v_self IS NULL THEN 
     469    RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot self-identify'; 
     470  END IF; 
     471 
     472  v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_60m'''; 
     473 
     474  EXECUTE v_sql; 
    304475 
    305476  SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour' 
    …… 
    307478          
    308479  SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_60m  
    309          INTO v_max_rollup_5;     
     480         INTO v_max_rollup_60;     
    310481 
    311482-- Insert Log for 6 Hour rollup 
    …… 
    318489    
    319490    
    320   IF v_min_whence <= v_max_rollup_5 THEN 
     491  IF v_min_whence <= v_max_rollup_60 THEN 
    321492   
    322493  DELETE FROM stratcon.rollup_matrix_numeric_60m  
    …… 
    329500                         stddev(stddev_value) as stddev_value, 
    330501                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
    331                          FROM stratcon.rollup_matrix_numeric_5m 
     502                         FROM stratcon.rollup_matrix_numeric_20m 
    332503                           WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) 
    333504                   GROUP BY date_trunc('hour',rollup_time),sid,name 
    …… 
    343514DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour'; 
    344515 
     516UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; 
     517 
    345518RETURN; 
    346519 
    347520EXCEPTION 
    348521    WHEN RAISE_EXCEPTION THEN 
     522       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; 
    349523       RAISE EXCEPTION '%', SQLERRM; 
    350524    WHEN OTHERS THEN 
     525      UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; 
    351526      RAISE NOTICE '%', SQLERRM; 
    352527END 
    …… 
    362537  rec stratcon.rollup_matrix_numeric_6hours%rowtype; 
    363538  v_sql TEXT; 
    364   v_min_whence TIMESTAMPTZ; 
    365   v_max_rollup_6 TIMESTAMPTZ; 
    366   v_whence TIMESTAMPTZ; 
    367   
     539  v_min_whence TIMESTAMP; 
     540  v_max_rollup_6 TIMESTAMP; 
     541  v_whence TIMESTAMP; 
     542  v_nrunning INT; 
     543  v_self VARCHAR(22); 
     544     
    368545BEGIN 
     546 
     547  SELECT COUNT(1) INTO v_nrunning 
     548    from stratcon.rollup_runner t, pg_stat_activity a 
     549    where rollup_table ='rollup_matrix_numeric_6hours' 
     550     and runner = procpid || '.' || date_part('epoch',backend_start); 
     551 
     552  IF v_nrunning > 0 THEN 
     553    RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running'; 
     554    RETURN ; 
     555  END IF; 
     556 
     557  SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) 
     558    FROM pg_stat_activity 
     559     WHERE procpid = pg_backend_pid(); 
     560 
     561  IF v_self IS NULL THEN 
     562    RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot self-identify'; 
     563   END IF; 
     564 
     565   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_6hours'''; 
     566 
     567  EXECUTE v_sql; 
    369568 
    370569  SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours' 
    …… 
    409608DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours'; 
    410609 
     610UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours'; 
     611 
    411612RETURN; 
    412613 
    413614EXCEPTION 
    414615    WHEN RAISE_EXCEPTION THEN 
     616       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';  
    415617       RAISE EXCEPTION '%', SQLERRM; 
    416618    WHEN OTHERS THEN 
    417       RAISE NOTICE '%', SQLERRM; 
     619       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours'; 
     620       RAISE NOTICE '%', SQLERRM; 
    418621END 
    419622$$ LANGUAGE plpgsql; 
    …… 
    428631  rec stratcon.rollup_matrix_numeric_12hours%rowtype; 
    429632  v_sql TEXT; 
    430   v_min_whence TIMESTAMPTZ; 
    431   v_max_rollup_12 TIMESTAMPTZ; 
    432   v_whence TIMESTAMPTZ; 
     633  v_min_whence TIMESTAMP; 
     634  v_max_rollup_12 TIMESTAMP; 
     635  v_whence TIMESTAMP; 
     636  v_nrunning INT; 
     637  v_self VARCHAR(22); 
     638  
    433639  
    434640BEGIN 
     641 
     642  SELECT COUNT(1) INTO v_nrunning 
     643    from stratcon.rollup_runner t, pg_stat_activity a 
     644    where rollup_table ='rollup_matrix_numeric_12hours' 
     645     and runner = procpid || '.' || date_part('epoch',backend_start); 
     646 
     647  IF v_nrunning > 0 THEN 
     648    RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running'; 
     649    RETURN ; 
     650  END IF; 
     651 
     652  SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start) 
     653    FROM pg_stat_activity 
     654     WHERE procpid = pg_backend_pid(); 
     655 
     656  IF v_self IS NULL THEN 
     657    RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot self-identify'; 
     658   END IF; 
     659 
     660   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_12hours'''; 
     661 
     662  EXECUTE v_sql; 
    435663 
    436664  SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours' 
    …… 
    475703DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours'; 
    476704 
     705UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_12hours'; 
     706 
    477707RETURN; 
    478708 
    479709EXCEPTION 
    480710    WHEN RAISE_EXCEPTION THEN 
    481        RAISE EXCEPTION '%', SQLERRM; 
     711      UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours'; 
     712      RAISE EXCEPTION '%', SQLERRM; 
    482713    WHEN OTHERS THEN 
     714      UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours'; 
    483715      RAISE NOTICE '%', SQLERRM; 
    484716END 
    …… 
    603835                 (   select 5*60 as isec, '5 minutes'::interval as aperiod, 
    604836                            'rollup_matrix_numeric_5m' as atablename 
     837                  union all 
     838                     select 20*60 as isec, '20 minutes'::interval as aperiod, 
     839                            'rollup_matrix_numeric_20m' as atablename 
    605840                  union all 
    606841                     select 60*60 as isec, '1 hour'::interval as aperiod,