Changeset 54b93a1e1604123f50988175daf9d9f718b8bbaf

Show
Ignore:
Timestamp:
10/23/09 18:17:34 (4 years ago)
Author:
Depesz Lubaczewski <depesz@omniti.com>
git-committer:
Depesz Lubaczewski <depesz@omniti.com> 1256321854 +0000
git-parent:

[2b6a82ffdcf845a41064c33d8d4913b28cd42fca]

git-author:
Depesz Lubaczewski <depesz@omniti.com> 1256321854 +0000
Message:

Make the function work with sid/name being NULL - returning data on all sids and names

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • sql/sprocs/stratcon.unroll_metric_numeric.sql

    ra22c5e5 r54b93a1  
    55AS $$ 
    66DECLARE 
    7    v_row   stratcon.metric_numeric_rollup_segment%rowtype; 
    8    v_begin TIMESTAMPTZ; 
    9    v_end   TIMESTAMPTZ; 
    10    adj_end TIMESTAMPTZ; 
    11    v_min_i INT4; 
    12    v_max_i INT4; 
    13    v_rollup_time timestamptz; 
    14    v_conf  RECORD; 
    15    i       INT4; 
    16    temprec RECORD; 
     7    v_row           stratcon.metric_numeric_rollup_segment%rowtype; 
     8    v_begin         TIMESTAMPTZ; 
     9    v_end           TIMESTAMPTZ; 
     10    v_adj_end       TIMESTAMPTZ; 
     11    v_min_i         INT4; 
     12    v_max_i         INT4; 
     13    v_rollup_time   TIMESTAMPTZ; 
     14    v_conf          RECORD; 
     15    v_i             INT4; 
     16    v_sql           TEXT; 
     17    temprec         RECORD; 
    1718BEGIN 
    18    SELECT * FROM metric_numeric_rollup_config WHERE rollup = in_roll INTO v_conf;  
    19    IF NOT FOUND THEN 
    20        RAISE EXCEPTION 'Unknown rollup %', in_roll; 
    21    END IF; 
    22    v_begin := 'epoch'::timestamptz + (floor(extract('epoch' FROM in_start) / v_conf.seconds) * v_conf.seconds) * '1 second'::interval; 
    23    adj_end := 'epoch'::timestamptz + (floor(extract('epoch' FROM in_end) / v_conf.seconds) * v_conf.seconds) * '1 second'::interval; 
    24    LOOP 
    25        EXIT WHEN v_begin > adj_end; 
    26        v_rollup_time := 'epoch'::timestamptz + (floor(extract('epoch' FROM v_begin) / v_conf.span) * v_conf.span) * '1 second'::interval; 
    27        v_end := LEAST( v_rollup_time + (v_conf.span * '1 second'::interval) - '1 second'::interval, adj_end ); 
    28        execute 'SELECT * FROM metric_numeric_rollup_' || in_roll || ' ' || 
    29                ' WHERE sid = ' || in_sid || ' AND "name" = ' || quote_literal(in_name) || ' ' || 
    30                '   AND rollup_time = ' || quote_literal(v_rollup_time) INTO temprec; 
    31        v_min_i := (ceil(extract('epoch' FROM v_begin ) / v_conf.seconds))::INT8 % (v_conf.span / v_conf.seconds); 
    32        v_max_i := (extract('epoch' FROM v_end )::INT8 / v_conf.seconds) % (v_conf.span / v_conf.seconds); 
    33        v_row.sid  := in_sid; 
    34        v_row.name := in_name; 
    35        FOR i in v_min_i .. v_max_i LOOP 
    36            v_row.rollup_time := v_rollup_time + '1 second'::INTERVAL * i * v_conf.seconds; 
    37            v_row.count_rows  := temprec.count_rows[i + array_lower( temprec.count_rows, 1 ) ]; 
    38            v_row.avg_value   := NULL; 
    39            v_row.counter_dev := NULL; 
    40            IF v_row.count_rows > 0 THEN 
    41                v_row.avg_value   := temprec.avg_value[i + array_lower( temprec.avg_value, 1 ) ]; 
    42                v_row.counter_dev := temprec.counter_dev[i + array_lower( temprec.counter_dev, 1 ) ]; 
    43            END IF; 
    44            RETURN next v_row; 
    45        END LOOP; 
    46        -- add a span 
    47        v_begin := v_begin + v_conf.span * '1 second'::interval; 
    48        -- trunc to the beginning of a span 
    49        v_begin := 'epoch'::timestamptz + (floor(extract('epoch' FROM v_begin) / v_conf.span) * v_conf.span) * '1 second'::interval; 
    50    END LOOP; 
     19    SELECT * FROM metric_numeric_rollup_config WHERE rollup = in_roll INTO v_conf;  
     20    IF NOT FOUND THEN 
     21        RAISE EXCEPTION 'Unknown rollup %', in_roll; 
     22    END IF; 
     23    v_begin   := 'epoch'::timestamptz + (floor(extract('epoch' FROM in_start) / v_conf.seconds) * v_conf.seconds) * '1 second'::interval; 
     24    v_adj_end := 'epoch'::timestamptz + (floor(extract('epoch' FROM in_end) / v_conf.seconds) * v_conf.seconds) * '1 second'::interval; 
     25    LOOP 
     26        EXIT WHEN v_begin > v_adj_end; 
     27 
     28        v_rollup_time := 'epoch'::timestamptz + (floor(extract('epoch' FROM v_begin) / v_conf.span) * v_conf.span) * '1 second'::interval; 
     29        v_end         := LEAST( v_rollup_time + (v_conf.span * '1 second'::interval) - '1 second'::interval, v_adj_end ); 
     30        v_min_i       := (ceil(extract('epoch' FROM v_begin ) / v_conf.seconds))::INT8 % (v_conf.span / v_conf.seconds); 
     31        v_max_i       := (extract('epoch' FROM v_end )::INT8 / v_conf.seconds) % (v_conf.span / v_conf.seconds); 
     32 
     33        -- field = coalesce( $x, field ) is a trick that can be said otherwise as: 
     34        -- ( $x is null or field = $x ) 
     35        -- which means - for every given sid/name - we will search for only this sid/name. But when given NULLs, it will return data on all sid/names. 
     36        v_sql := 'SELECT * FROM metric_numeric_rollup_' || in_roll || ' WHERE '; 
     37        v_sql := 'rollup_time = $1 AND sid = coalesce( $2, sid ) AND "name" = coalesce( $3, "name" )'; 
     38 
     39        -- Following code (EXECUTE ... USING ... will work only from 8.4 on! 
     40        FOR temprec IN EXECUTE v_sql USING v_rollup_time, in_sid, in_name LOOP 
     41            v_row.sid  := temprec.sid; 
     42            v_row.name := temprec.name; 
     43            FOR v_i in v_min_i .. v_max_i LOOP 
     44                v_row.rollup_time := v_rollup_time + '1 second'::INTERVAL * v_i * v_conf.seconds; 
     45                v_row.count_rows  := temprec.count_rows[v_i + array_lower( temprec.count_rows, 1 ) ]; 
     46                v_row.avg_value   := NULL; 
     47                v_row.counter_dev := NULL; 
     48                IF v_row.count_rows > 0 THEN 
     49                    v_row.avg_value   := temprec.avg_value[v_i + array_lower( temprec.avg_value, 1 ) ]; 
     50                    v_row.counter_dev := temprec.counter_dev[v_i + array_lower( temprec.counter_dev, 1 ) ]; 
     51                END IF; 
     52                RETURN next v_row; 
     53            END LOOP; 
     54        END LOOP; 
     55        -- add a span 
     56        v_begin := v_begin + v_conf.span * '1 second'::interval; 
     57        -- trunc to the beginning of a span 
     58        v_begin := 'epoch'::timestamptz + (floor(extract('epoch' FROM v_begin) / v_conf.span) * v_conf.span) * '1 second'::interval; 
     59    END LOOP; 
    5160END; 
    5261$$ 
    …… 
    5463SECURITY DEFINER; 
    5564 
    56  
     65-- wrapper so we will NOT give (NULL, NULL,) when calling unroll for all sid AND names. 
     66CREATE OR REPLACE FUNCTION stratcon.unroll_metric_numeric (timestamptz, timestamptz, text)  
     67RETURNS SETOF stratcon.metric_numeric_rollup_segment 
     68AS $$ 
     69    SELECT * FROM stratcon.unroll_metric_numeric( NULL, NULL, $1, $2, $3 ); 
     70$$ language sql;