Changeset f805e6ba6d1f0c796ea6e011688c18a4e56fe9dd

Show
Ignore:
Timestamp:
10/21/09 16:41:38 (5 years ago)
Author:
Robert Treat <robert@omniti.com>
git-committer:
Robert Treat <robert@omniti.com> 1256143298 +0000
git-parent:

[32ddc52a40c4d76a2cfc234cae27df4212c7c745]

git-author:
Robert Treat <robert@omniti.com> 1256143298 +0000
Message:

read data back out of our rollups

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/sprocs/stratcon.fetch_dataset.sql

    rdfd06d8 rf805e6b  
    1 -- Function: stratcon.fetch_dataset(uuid, text, timestamp with time zone, timestamp with time zone, integer, boolean) 
    21 
    3 CREATE OR REPLACE FUNCTION stratcon.fetch_dataset(in_uuid uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean) 
    4   RETURNS SETOF noit.metric_numeric_rollup_5m AS 
    5 $BODY$declare 
     2CREATE OR REPLACE FUNCTION stratcon.fetch_dataset 
     3(in_uuid uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean)  
     4RETURNS SETOF stratcon.metric_numeric_rollup_segment 
     5AS $$ 
     6DECLARE 
    67  v_sid int; 
    7   v_record noit.metric_numeric_rollup_5m%rowtype; 
    8 begin 
    9   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_uuid; 
    10   if not found then 
    11     return
    12   end if
     8  v_record stratcon.metric_numeric_rollup_segment%rowtype; 
     9BEGIN 
     10  SELECT sid FROM stratcon.map_uuid_to_sid WHERE id = in_uuid INTO v_sid; 
     11  IF NOT FOUND THEN 
     12    RETURN
     13  END IF
    1314 
     15/* i think this was only needed for < 8.4 
    1416    for v_record in  select sid, name, rollup_time, count_rows, avg_value, counter_dev from stratcon.fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive) loop 
    1517    return next v_record;  
    1618    end loop; 
     19*/ 
    1720 
    18 --  return query select sid, name, rollup_time, count_rows, avg_value from stratcon.fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive); 
     21  RETURN QUERY SELECT sid, name, rollup_time, count_rows, avg_value  
     22                   FROM stratcon.fetch_dataset(v_sid, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive); 
     23 
     24  RETURN; 
     25END 
     26$$ 
     27LANGUAGE plpgsql 
     28SECURITY DEFINER 
     29
     30 
     31 
     32 
     33CREATE OR REPLACE FUNCTION stratcon.fetch_dataset 
     34(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean)  
     35RETURNS SETOF stratcon.metric_numeric_rollup_segment 
     36AS $_$ 
     37DECLARE 
     38    v_sql           text; 
     39    v_sql_subtable  text; 
     40    v_target        record; 
     41    v_interval      numeric; 
     42    v_start_adj     timestamptz; 
     43    v_end_adj       timestamptz; 
     44    v_l_rollup_row  stratcon.metric_numeric_rollup_segment%rowtype; 
     45    v_rollup_row    stratcon.metric_numeric_rollup_segment%rowtype; 
     46    v_r_rollup_row  stratcon.metric_numeric_rollup_segment%rowtype; 
     47BEGIN 
     48    SELECT *, extract('epoch' FROM period) as epoch_period INTO v_target 
     49        FROM stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods); 
     50    IF NOT FOUND THEN 
     51        RAISE EXCEPTION 'no target table'; 
     52        RETURN; 
     53    END IF; 
     54 
     55    -- round start and end timestamps to period precision (i.e. to 5 minutes, or 1 hour, or ...) 
     56    v_start_adj := ( 'epoch'::timestamp + v_target.period * floor( extract('epoch' from in_start_time) / v_target.epoch_period ) ) AT TIME ZONE 'UTC'; 
     57    v_end_adj   := ( 'epoch'::timestamp + v_target.period * floor( extract('epoch' from in_end_time)   / v_target.epoch_period ) ) AT TIME ZONE 'UTC'; 
     58 
     59    -- build sql using placeholders ([something]) to make it more readable than using ' || ... || ' all the time. 
     60    v_sql_subtable := $SQL$ 
     61        select * 
     62        from stratcon.[tablename] 
     63        where 
     64            sid = [in_sid] 
     65            and "name" = [in_name] 
     66            and rollup_time between [v_start_adj]::timestamp AND [v_end_adj]::timestamp 
     67    $SQL$; 
     68    IF v_target.tablename = 'metric_numeric_rollup_5m' THEN 
     69 
     70        v_sql_subtable := $SQL$ 
     71            SELECT * 
     72            FROM stratcon.unroll_metric_numeric_5m( 
     73                [in_sid], 
     74                [in_name], 
     75                [v_start_adj]::timestamp, 
     76                [v_end_adj]::timestamp 
     77            ) 
     78        $SQL$; 
     79 
     80    END IF; 
     81 
     82    v_sql := $SQL$ 
     83        select 
     84            [in_sid] as sid, 
     85            [in_name] as name, 
     86            s.rollup_time, 
     87            d.count_rows, 
     88            d.avg_value, 
     89            d.counter_dev  
     90        from  
     91            ( 
     92                select [v_start_adj]::timestamp + t * [period]::interval as rollup_time 
     93                from generate_series(1, [nperiods]) t 
     94            ) s  
     95            left join ( [subtable] ) d using(rollup_time) 
     96    $SQL$; 
     97    -- change placeholders to real values. 
     98    v_sql := replace( v_sql, '[subtable]',    v_sql_subtable::TEXT              ); -- this one has to be the first, as it might contain other placeholder 
     99    v_sql := replace( v_sql, '[in_sid]',      in_sid::TEXT                      ); 
     100    v_sql := replace( v_sql, '[in_name]',     quote_literal( in_name )          ); 
     101    v_sql := replace( v_sql, '[v_start_adj]', quote_literal( v_start_adj )      ); 
     102    v_sql := replace( v_sql, '[v_end_adj]',   quote_literal( v_end_adj )        ); 
     103    v_sql := replace( v_sql, '[period]',      quote_literal( v_target.period )  ); 
     104    v_sql := replace( v_sql, '[nperiods]',    v_target.nperiods::TEXT           ); 
     105    v_sql := replace( v_sql, '[tablename]',   quote_ident( v_target.tablename ) ); 
     106 
     107    FOR v_rollup_row IN EXECUTE v_sql LOOP 
     108        IF derive IS TRUE THEN 
     109            v_r_rollup_row := v_rollup_row; 
     110            IF v_l_rollup_row.count_rows  IS NOT NULL AND 
     111                v_rollup_row.count_rows   IS NOT NULL THEN 
     112                v_interval                := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time); 
     113                v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2; 
     114                v_r_rollup_row.avg_value  := (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval; 
     115            else 
     116                v_r_rollup_row.count_rows = NULL; 
     117                v_r_rollup_row.avg_value = NULL; 
     118            end if; 
     119        else 
     120            v_r_rollup_row := v_rollup_row; 
     121        end if; 
     122        return next v_r_rollup_row; 
     123        v_l_rollup_row := v_rollup_row; 
     124    end loop; 
    19125  return; 
    20126end 
    21 $BODY
    22   LANGUAGE 'plpgsql' SECURITY DEFINER
     127$_
     128    LANGUAGE plpgsql
    23129 
    24 GRANT EXECUTE ON FUNCTION stratcon.fetch_dataset(uuid, text, timestamp with time zone, timestamp with time zone, integer, boolean) TO stratcon; 
    25  
    26 CREATE OR REPLACE FUNCTION stratcon.fetch_dataset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean) 
    27   RETURNS SETOF noit.metric_numeric_rollup_5m AS 
    28 $BODY$declare 
    29   v_sql text; 
    30   v_sid int; 
    31   v_target record; 
    32   v_interval numeric; 
    33   v_start_adj timestamptz; 
    34   v_end_adj timestamptz; 
    35   v_l_rollup_row noit.metric_numeric_rollup_5m%rowtype; 
    36   v_rollup_row noit.metric_numeric_rollup_5m%rowtype; 
    37   v_r_rollup_row noit.metric_numeric_rollup_5m%rowtype; 
    38 begin 
    39  
    40   -- Map out uuid to an sid. 
    41   v_sid := in_sid; 
    42  
    43   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods); 
    44  
    45   if not found then 
    46     raise exception 'no target table'; 
    47     return; 
    48   end if; 
    49  
    50   select 'epoch'::timestamp + 
    51          ((floor(extract('epoch' from in_start_time) / 
    52                  extract('epoch' from v_target.period)) * 
    53            extract('epoch' from v_target.period)) || ' seconds') ::interval 
    54     into v_start_adj; 
    55  
    56   select 'epoch'::timestamp + 
    57          ((floor(extract('epoch' from in_end_time) / 
    58                  extract('epoch' from v_target.period)) * 
    59            extract('epoch' from v_target.period)) || ' seconds') ::interval 
    60     into v_end_adj; 
    61  
    62   v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' || 
    63            's.rollup_time, d.count_rows, d.avg_value, d.counter_dev ' || 
    64            ' from ' || 
    65            '(select ' || quote_literal(v_start_adj) || '::timestamp' || 
    66                   ' + t * ' || quote_literal(v_target.period) || '::interval' || 
    67                        ' as rollup_time' || 
    68              ' from generate_series(1,' || v_target.nperiods || ') t) s ' || 
    69            'left join ' || 
    70            '(select * from ' || v_target.tablename || 
    71            ' where sid = ' || v_sid || 
    72              ' and name = ' || quote_literal(in_name) || 
    73              ' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' || 
    74                                  ' and ' || quote_literal(v_end_adj) || '::timestamp) d' || 
    75            ' using(rollup_time)'; 
    76  
    77   for v_rollup_row in execute v_sql loop 
    78     if derive is true then 
    79       v_r_rollup_row := v_rollup_row; 
    80       if v_l_rollup_row.count_rows is not null and 
    81          v_rollup_row.count_rows is not null then 
    82         v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time); 
    83         v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2; 
    84         v_r_rollup_row.avg_value := 
    85           (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval; 
    86       else 
    87         v_r_rollup_row.count_rows = NULL; 
    88         v_r_rollup_row.avg_value = NULL; 
    89          
    90       end if; 
    91     else 
    92       v_r_rollup_row := v_rollup_row; 
    93     end if; 
    94     return next v_r_rollup_row; 
    95     v_l_rollup_row := v_rollup_row; 
    96   end loop; 
    97   return; 
    98 end 
    99 $BODY$ 
    100   LANGUAGE 'plpgsql' SECURITY DEFINER; 
    101 GRANT EXECUTE ON FUNCTION  stratcon.fetch_dataset(integer, text, timestamp with time zone, timestamp with time zone, integer, boolean) TO stratcon; 
    102