sql/metrics.sql

   1 --  Written by Bob Cotton <bob.cotton@gmail.com>
   2 --  This is free software; you can redistribute it and/or modify it under
   3 --  the terms of the GNU General Public License as published by the Free
   4 --  Software Foundation; only version 2 of the License is applicable.
   5 create or replace function insert_metric(in_timestamp timestamp,
   6                                          in_measure double precision,
   7                                          in_hostname text,
   8                                          in_ds_type datasource_type,
   9                                          in_plugin text,
  10                                          in_plugin_instance text,
  11                                          in_type text,
  12                                          in_type_name text,
  13                                          in_type_instance text) returns void as $$
  14   DECLARE
  15     host_id integer;
  16     a_plugin_id integer;
  17     a_type_id integer;
  18   BEGIN
  19     select into host_id id from hostname_dimension where hostname = in_hostname;
  20     IF NOT FOUND THEN
  21       insert into hostname_dimension (hostname) values (in_hostname) returning id into host_id;
  22     END IF;
  23
  24     IF in_plugin_instance IS NULL THEN
  25         select into a_plugin_id id from plugin_dimension where plugin = in_plugin and plugin_instance is null;
  26     ELSE
  27         select into a_plugin_id id from plugin_dimension where plugin = in_plugin and plugin_instance = in_plugin_instance;
  28     END IF;
  29
  30     IF NOT FOUND THEN
  31        insert into plugin_dimension (plugin, plugin_instance) values (in_plugin, in_plugin_instance) returning id into a_plugin_id;
  32     END IF;
  33
  34     IF in_type_instance IS NULL THEN
  35         select into a_type_id id from type_dimension where type = in_type and type_name = in_type_name and type_instance is null;
  36     ELSE
  37         select into a_type_id id from type_dimension where type = in_type and type_name = in_type_name and type_instance = in_type_instance;
  38     END IF;
  39
  40     IF NOT FOUND THEN
  41        insert into type_dimension (type, ds_type, type_name, type_instance) values (in_type, in_ds_type, in_type_name, in_type_instance) returning id into a_type_id;
  42     END IF;
  43
  44     insert into metrics (timestamp, measure, hostname_id, plugin_id, type_id) values (in_timestamp, in_measure, host_id, a_plugin_id, a_type_id);
  45   END;
  46 $$ LANGUAGE plpgsql;
  47
  48 create or replace function get_interval(start_timestamp timestamp, length interval, step text) returns SETOF timestamp as $$
  49   DECLARE
  50     v_timestamp timestamp;
  51     end_timestamp timestamp;
  52   BEGIN
  53     v_timestamp := start_timestamp;
  54     end_timestamp := start_timestamp + length;
  55     WHILE v_timestamp <= end_timestamp LOOP
  56       RETURN NEXT v_timestamp;
  57       v_timestamp := v_timestamp + ('1' || step)::interval;
  58     END LOOP;
  59   END;
  60 $$ language plpgsql;
  61
  62 create or replace function create_partition_trigger(parent text,
  63                                                     start_timestamp timestamp,
  64                                                     length interval,
  65                                                     step text,
  66                                                     format text) returns void as $trigger$
  67   DECLARE
  68     v_function text;
  69     v_body text;
  70     v_current_date date;
  71     v_start_date date;
  72     v_suffix text;
  73   BEGIN
  74     v_current_date := date(start_timestamp);
  75     v_function := 'CREATE OR REPLACE FUNCTION ' || parent || '_insert_trigger() '
  76                   || 'RETURNS TRIGGER LANGUAGE plpgsql AS $$ '
  77                   || 'BEGIN ';
  78
  79     FOR v_start_date in select * from get_interval(start_timestamp, length, step) LOOP
  80         select trim(to_char(v_start_date, format)) into v_suffix;
  81         IF v_current_date = v_start_date THEN
  82            v_body := ' IF ';
  83         ELSE
  84            v_body := ' ELSEIF ';
  85         END IF;
  86         v_body := v_body || ' NEW.timestamp >= ''' || v_start_date || '''::timestamp and '
  87                          || ' NEW.timestamp < ''' || v_start_date + ( '1' || step)::interval || '''::timestamp THEN '
  88                          || ' INSERT INTO ' || parent || '_' || v_suffix
  89                          || ' values (NEW.*); ';
  90         v_function := v_function || v_body;
  91     END LOOP;
  92     v_function := v_function || 'ELSE RETURN NEW; END IF; RETURN NULL; END; $$';
  93     EXECUTE v_function;
  94   END;
  95 $trigger$ LANGUAGE plpgsql;
  96
  97 create or replace function create_partition_tables(parent text, start_timestamp timestamp, length interval, step text, format text) returns void as $$
  98   DECLARE
  99       sql text;
 100       v_suffix text;
 101       v_start_date date;
 102       table_name text;
 103   BEGIN
 104       FOR v_start_date in select * from get_interval(start_timestamp, length, step) LOOP
 105           select trim(to_char(v_start_date, format)) into v_suffix;
 106           select parent || '_' || v_suffix into table_name;
 107           select 'create table ' || table_name
 108                 || ' (CHECK (timestamp >= ' || quote_literal(v_start_date)
 109                 || '::timestamp and timestamp < ' || quote_literal(v_start_date + ( '1' || step)::interval)
 110                 || '::timestamp)) INHERITS (' || parent || ');'
 111                 into sql;
 112            EXECUTE sql;
 113            EXECUTE 'create index index_' || table_name || '_on_timestamp_hostname_and_plugin_and_type on ' || table_name || ' (timestamp, hostname_id, plugin_id, type_id);';
 114        END LOOP;
 115   END;
 116 $$ LANGUAGE plpgsql;
 117
 118 -- if you are using postgres 8.4, which introduced window functions
 119 -- you can use something like this to to create a view on rows that come from
 120 -- COUNTER plugins. Because the real measurement is the difference of
 121 -- the last sample to the next sample, use the lag() function to do that math.
 122 -- This will create a combined view of both COUNTER and GUAGE types.
 123 --
 124 -- WARNING - for large datasets THIS WILL BE SLOW!
 125
 126 -- create view metrics_view as
 127 --  SELECT timestamp,
 128 --   ((m.measure - lag(m.measure)
 129 --           over(partition by m.hostname_id,
 130 --                             p.plugin,
 131 --                             p.plugin_instance,
 132 --                             t.type,
 133 --                             t.type_instance
 134 --                 order by timestamp, m.hostname_id, p.plugin, p.plugin_instance, t.type, t.type_instance))) AS metric,
 135 --  m.hostname_id,
 136 --  m.plugin_id,
 137 --  m.type_id
 138 -- FROM metrics m, plugin_dimension p, type_dimension t
 139 -- where m.type_id = t.id
 140 -- and m.plugin_id = p.id
 141 -- and t.ds_type = 'COUNTER'
 142 -- UNION
 143 -- select timestamp, m.measure as metric,
 144 --  m.hostname_id,
 145 --  m.plugin_id,
 146 --  m.type_id
 147 -- FROM metrics m, type_dimension t
 148 -- where m.type_id = t.id
 149 -- and t.ds_type = 'GUAGE';