Show
Ignore:
Timestamp:
06/25/08 22:50:16 (6 years ago)
Author:
robert
Message:

do some actual debugging, make this stuff actually work

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • trunk/table_growth/otools.summarize_table_growth.sql

    r14 r15  
    1 create or replace function otools.summarize_table_growth  
     1create or replace function otools.summarize_table_growth()  
    22returns void  
    33as $$ 
     
    88-- Daily summarization 
    99IF to_char(current_date,'dd') <> '01' THEN 
    10     insert into table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time) 
    11     select a.table_owner, a.schema_name, a.table_name, a.actual_size, CASE WHEN b.actual_size IS NULL then 0 else a.actual_size-b.actual_size END AS table_growth, 1, a.timestamp 
    12     from table_growth a left join table_growth b on (a.table_owner=b.table_owner and a.table_name=b.table_name and a.schema_name=b.schema_name)  
    13     where a.sum_flag=0 and a.capture_time = current_date and b.capture_time = current_date - 1; 
     10    insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time) 
     11    select a.table_owner, a.schema_name, a.table_name, a.actual_size, CASE WHEN b.actual_size IS NULL then 0 else a.actual_size-b.actual_size END AS table_growth, 1, a.capture_time 
     12    from otools.table_growth a  
     13        left join otools.table_growth b  
     14            on (a.table_owner=b.table_owner and a.table_name=b.table_name and a.schema_name=b.schema_name and b.capture_time = current_date -1)  
     15    where  
     16        a.sum_flag=0 and a.capture_time = current_date; 
    1417    -- now remove older rows 
    1518    delete from otools.table_growth where sum_flag = 0; 
     
    1821-- Monthly summarization 
    1922IF to_char(current_date,'dd') = '01' THEN 
    20     insert into table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time) 
    21     select a.table_owner, a.schema_name, a.table_name, a.actual_size, sum(growth_size), 2, (current_date - '1 month'::interval)  
    22     from otools.table_growth  
    23     where sum_flags=1 and capture_time between (current_date - '1 month'::interval) and current_date  
     23    insert into otools.table_growth (table_owner, schema_name, table_name, growth_size, sum_flag, capture_time) 
     24    select a.table_owner, a.schema_name, a.table_name, sum(growth_size), 2, (current_date - '1 month'::interval)  
     25    from otools.table_growth a  
     26    where sum_flag=1 and capture_time between (current_date - '1 month'::interval) and current_date  
    2427    group by table_owner, schema_name, table_name; 
    2528    -- now remove older rows