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