[Reconnoiter-devel] [reconnoiter commit] r990 - branches/skeksis/sql/sprocs

svn-commit at lists.omniti.com svn-commit at lists.omniti.com
Wed Oct 21 12:41:39 EDT 2009


Author: robert
Date: 2009-10-21 12:41:38 -0400 (Wed, 21 Oct 2009)
New Revision: 990

Added:
   branches/skeksis/sql/sprocs/stratcon.unroll_metric_numeric_5m.sql
Modified:
   branches/skeksis/sql/sprocs/stratcon.fetch_dataset.sql
Log:
read data back out of our rollups

Modified: branches/skeksis/sql/sprocs/stratcon.fetch_dataset.sql
===================================================================
--- branches/skeksis/sql/sprocs/stratcon.fetch_dataset.sql	2009-10-21 15:50:40 UTC (rev 989)
+++ branches/skeksis/sql/sprocs/stratcon.fetch_dataset.sql	2009-10-21 16:41:38 UTC (rev 990)
@@ -1,102 +1,129 @@
--- Function: stratcon.fetch_dataset(uuid, text, timestamp with time zone, timestamp with time zone, integer, boolean)
 
-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)
-  RETURNS SETOF noit.metric_numeric_rollup_5m AS
-$BODY$declare
+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) 
+RETURNS SETOF stratcon.metric_numeric_rollup_segment
+AS $$
+DECLARE
   v_sid int;
-  v_record noit.metric_numeric_rollup_5m%rowtype;
-begin
-  select sid into v_sid from stratcon.map_uuid_to_sid where id = in_uuid;
-  if not found then
-    return;
-  end if;
+  v_record stratcon.metric_numeric_rollup_segment%rowtype;
+BEGIN
+  SELECT sid FROM stratcon.map_uuid_to_sid WHERE id = in_uuid INTO v_sid;
+  IF NOT FOUND THEN
+    RETURN;
+  END IF;
 
+/* i think this was only needed for < 8.4
     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
     return next v_record; 
     end loop;
+*/
 
---  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);
-  return;
-end
-$BODY$
-  LANGUAGE 'plpgsql' SECURITY DEFINER;
+  RETURN QUERY SELECT sid, name, rollup_time, count_rows, avg_value 
+                   FROM stratcon.fetch_dataset(v_sid, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive);
 
-GRANT EXECUTE ON FUNCTION stratcon.fetch_dataset(uuid, text, timestamp with time zone, timestamp with time zone, integer, boolean) TO stratcon;
+  RETURN;
+END
+$$
+LANGUAGE plpgsql
+SECURITY DEFINER
+;
 
-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)
-  RETURNS SETOF noit.metric_numeric_rollup_5m AS
-$BODY$declare
-  v_sql text;
-  v_sid int;
-  v_target record;
-  v_interval numeric;
-  v_start_adj timestamptz;
-  v_end_adj timestamptz;
-  v_l_rollup_row noit.metric_numeric_rollup_5m%rowtype;
-  v_rollup_row noit.metric_numeric_rollup_5m%rowtype;
-  v_r_rollup_row noit.metric_numeric_rollup_5m%rowtype;
-begin
 
-  -- Map out uuid to an sid.
-  v_sid := in_sid;
 
-  select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
+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) 
+RETURNS SETOF stratcon.metric_numeric_rollup_segment
+AS $_$
+DECLARE
+    v_sql           text;
+    v_sql_subtable  text;
+    v_target        record;
+    v_interval      numeric;
+    v_start_adj     timestamptz;
+    v_end_adj       timestamptz;
+    v_l_rollup_row  stratcon.metric_numeric_rollup_segment%rowtype;
+    v_rollup_row    stratcon.metric_numeric_rollup_segment%rowtype;
+    v_r_rollup_row  stratcon.metric_numeric_rollup_segment%rowtype;
+BEGIN
+    SELECT *, extract('epoch' FROM period) as epoch_period INTO v_target
+        FROM stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'no target table';
+        RETURN;
+    END IF;
 
-  if not found then
-    raise exception 'no target table';
-    return;
-  end if;
+    -- round start and end timestamps to period precision (i.e. to 5 minutes, or 1 hour, or ...)
+    v_start_adj := ( 'epoch'::timestamp + v_target.period * floor( extract('epoch' from in_start_time) / v_target.epoch_period ) ) AT TIME ZONE 'UTC';
+    v_end_adj   := ( 'epoch'::timestamp + v_target.period * floor( extract('epoch' from in_end_time)   / v_target.epoch_period ) ) AT TIME ZONE 'UTC';
 
-  select 'epoch'::timestamp +
-         ((floor(extract('epoch' from in_start_time) /
-                 extract('epoch' from v_target.period)) *
-           extract('epoch' from v_target.period)) || ' seconds') ::interval
-    into v_start_adj;
+    -- build sql using placeholders ([something]) to make it more readable than using ' || ... || ' all the time.
+    v_sql_subtable := $SQL$
+        select *
+        from stratcon.[tablename]
+        where
+            sid = [in_sid]
+            and "name" = [in_name]
+            and rollup_time between [v_start_adj]::timestamp AND [v_end_adj]::timestamp
+    $SQL$;
+    IF v_target.tablename = 'metric_numeric_rollup_5m' THEN
 
-  select 'epoch'::timestamp +
-         ((floor(extract('epoch' from in_end_time) /
-                 extract('epoch' from v_target.period)) *
-           extract('epoch' from v_target.period)) || ' seconds') ::interval
-    into v_end_adj;
+        v_sql_subtable := $SQL$
+            SELECT *
+            FROM stratcon.unroll_metric_numeric_5m(
+                [in_sid],
+                [in_name],
+                [v_start_adj]::timestamp,
+                [v_end_adj]::timestamp
+            )
+        $SQL$;
 
-  v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' ||
-           's.rollup_time, d.count_rows, d.avg_value, d.counter_dev ' ||
-           ' from ' ||
-           '(select ' || quote_literal(v_start_adj) || '::timestamp' ||
-                  ' + t * ' || quote_literal(v_target.period) || '::interval' ||
-                       ' as rollup_time' ||
-             ' from generate_series(1,' || v_target.nperiods || ') t) s ' ||
-           'left join ' ||
-           '(select * from ' || v_target.tablename ||
-           ' where sid = ' || v_sid ||
-             ' and name = ' || quote_literal(in_name) ||
-             ' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' ||
-                                 ' and ' || quote_literal(v_end_adj) || '::timestamp) d' ||
-           ' using(rollup_time)';
+    END IF;
 
-  for v_rollup_row in execute v_sql loop
-    if derive is true then
-      v_r_rollup_row := v_rollup_row;
-      if v_l_rollup_row.count_rows is not null and
-         v_rollup_row.count_rows is not null then
-        v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time);
-        v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2;
-        v_r_rollup_row.avg_value :=
-          (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval;
-      else
-        v_r_rollup_row.count_rows = NULL;
-        v_r_rollup_row.avg_value = NULL;
-        
-      end if;
-    else
-      v_r_rollup_row := v_rollup_row;
-    end if;
-    return next v_r_rollup_row;
-    v_l_rollup_row := v_rollup_row;
-  end loop;
+    v_sql := $SQL$
+        select
+            [in_sid] as sid,
+            [in_name] as name,
+            s.rollup_time,
+            d.count_rows,
+            d.avg_value,
+            d.counter_dev 
+        from 
+            (
+                select [v_start_adj]::timestamp + t * [period]::interval as rollup_time
+                from generate_series(1, [nperiods]) t
+            ) s 
+            left join ( [subtable] ) d using(rollup_time)
+    $SQL$;
+    -- change placeholders to real values.
+    v_sql := replace( v_sql, '[subtable]',    v_sql_subtable::TEXT              ); -- this one has to be the first, as it might contain other placeholder
+    v_sql := replace( v_sql, '[in_sid]',      in_sid::TEXT                      );
+    v_sql := replace( v_sql, '[in_name]',     quote_literal( in_name )          );
+    v_sql := replace( v_sql, '[v_start_adj]', quote_literal( v_start_adj )      );
+    v_sql := replace( v_sql, '[v_end_adj]',   quote_literal( v_end_adj )        );
+    v_sql := replace( v_sql, '[period]',      quote_literal( v_target.period )  );
+    v_sql := replace( v_sql, '[nperiods]',    v_target.nperiods::TEXT           );
+    v_sql := replace( v_sql, '[tablename]',   quote_ident( v_target.tablename ) );
+
+    FOR v_rollup_row IN EXECUTE v_sql LOOP
+        IF derive IS TRUE THEN
+            v_r_rollup_row := v_rollup_row;
+            IF v_l_rollup_row.count_rows  IS NOT NULL AND
+                v_rollup_row.count_rows   IS NOT NULL THEN
+                v_interval                := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time);
+                v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2;
+                v_r_rollup_row.avg_value  := (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval;
+            else
+                v_r_rollup_row.count_rows = NULL;
+                v_r_rollup_row.avg_value = NULL;
+            end if;
+        else
+            v_r_rollup_row := v_rollup_row;
+        end if;
+        return next v_r_rollup_row;
+        v_l_rollup_row := v_rollup_row;
+    end loop;
   return;
 end
-$BODY$
-  LANGUAGE 'plpgsql' SECURITY DEFINER;
-GRANT EXECUTE ON FUNCTION  stratcon.fetch_dataset(integer, text, timestamp with time zone, timestamp with time zone, integer, boolean) TO stratcon;
- 
+$_$
+    LANGUAGE plpgsql;
+

Added: branches/skeksis/sql/sprocs/stratcon.unroll_metric_numeric_5m.sql
===================================================================
--- branches/skeksis/sql/sprocs/stratcon.unroll_metric_numeric_5m.sql	                        (rev 0)
+++ branches/skeksis/sql/sprocs/stratcon.unroll_metric_numeric_5m.sql	2009-10-21 16:41:38 UTC (rev 990)
@@ -0,0 +1,43 @@
+
+CREATE OR REPLACE FUNCTION stratcon.unroll_numeric_metric_rollup_5m
+(in_sid integer, in_name text, in_start timestamp with time zone, in_end timestamp with time zone) 
+RETURNS SETOF stratcon.metric_numeric_rollup_segment
+AS $$
+DECLARE
+   v_row   stratcon.metric_numeric_rollup_segment%rowtype;
+   v_begin TIMESTAMPTZ := in_start;
+   v_end   TIMESTAMPTZ := in_end;
+   v_min_i INT4;
+   v_max_i INT4;
+   v_rollup_time timestamptz;
+   i       INT4;
+   temprec RECORD;
+BEGIN
+   LOOP
+       EXIT WHEN v_begin > in_end;
+       v_rollup_time := date_trunc('day', v_begin AT TIME ZONE 'UTC') AT TIME ZONE 'UTC';
+       v_end := LEAST( v_rollup_time + '1 day'::INTERVAL - '1 second'::INTERVAL, in_end );
+       SELECT * INTO temprec FROM metric_numeric_rollup_5m WHERE sid = in_sid AND "name" = in_name AND rollup_time = v_rollup_time;
+       v_min_i := (ceil(extract('epoch' FROM v_begin ) / 300.0))::INT8 % 288;
+       v_max_i := (extract('epoch' FROM v_end )::INT8 / 300) % 288;
+       v_row.sid  := in_sid;
+       v_row.name := in_name;
+       FOR i in v_min_i .. v_max_i LOOP
+           v_row.rollup_time := v_rollup_time + '5 minutes'::INTERVAL * i;
+           v_row.count_rows  := temprec.count_rows[i + array_lower( temprec.count_rows, 1 ) ];
+           v_row.avg_value   := NULL;
+           v_row.counter_dev := NULL;
+           IF v_row.count_rows > 0 THEN
+               v_row.avg_value   := temprec.avg_value[i + array_lower( temprec.avg_value, 1 ) ];
+               v_row.counter_dev := temprec.counter_dev[i + array_lower( temprec.counter_dev, 1 ) ];
+           END IF;
+           RETURN next v_row;
+       END LOOP;
+       v_begin := date_trunc( 'day', ( v_begin + '1 day'::INTERVAL) AT TIME ZONE 'UTC') AT TIME ZONE 'UTC';
+   END LOOP;
+END;
+$$
+LANGUAGE plpgsql
+SECURITY DEFINER;
+
+



More information about the Reconnoiter-devel mailing list