root/sql/sprocs/stratcon.unroll_metric_numeric.sql

Revision a22c5e518153d427a2ead233822c631e036c22ba, 2.6 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 4 years ago)

rework the rollups to be generic

  • Property mode set to 100644
Line 
1
2 CREATE OR REPLACE FUNCTION stratcon.unroll_metric_numeric
3 (in_sid integer, in_name text, in_start timestamp with time zone, in_end timestamp with time zone, in_roll text)
4 RETURNS SETOF stratcon.metric_numeric_rollup_segment
5 AS $$
6 DECLARE
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;
17 BEGIN
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;
51 END;
52 $$
53 LANGUAGE plpgsql
54 SECURITY DEFINER;
55
56
Note: See TracBrowser for help on using the browser.