contrib/postgresql/collectd_insert.sql

   1 -- collectd - contrib/postgresql/collectd_insert.sql
   2 -- Copyright (C) 2012 Sebastian 'tokkee' Harl
   3 -- All rights reserved.
   4 --
   5 -- Redistribution and use in source and binary forms, with or without
   6 -- modification, are permitted provided that the following conditions
   7 -- are met:
   8 --
   9 -- - Redistributions of source code must retain the above copyright
  10 --   notice, this list of conditions and the following disclaimer.
  11 --
  12 -- - Redistributions in binary form must reproduce the above copyright
  13 --   notice, this list of conditions and the following disclaimer in the
  14 --   documentation and/or other materials provided with the distribution.
  15 --
  16 -- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
  17 -- AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
  18 -- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
  19 -- ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
  20 -- LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
  21 -- CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
  22 -- SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
  23 -- INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
  24 -- CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
  25 -- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
  26 -- POSSIBILITY OF SUCH DAMAGE.
  27
  28 -- Description:
  29 -- ------------
  30 --
  31 -- This is a sample database setup that may be used to write data collected by
  32 -- collectd to a PostgreSQL database. We're using two tables, 'identifiers'
  33 -- and 'values' to store the value-list identifier and the actual values
  34 -- respectively.
  35 --
  36 -- The 'values' table is partitioned to improve performance and maintainance.
  37 -- Please note that additional maintainance scripts are required in order to
  38 -- keep the setup running -- see the comments below for details.
  39 --
  40 -- The function 'collectd_insert' may be used to actually insert values
  41 -- submitted by collectd into those tables.
  42 --
  43 -- Sample configuration:
  44 -- ---------------------
  45 --
  46 -- <Plugin postgresql>
  47 --     <Writer sqlstore>
  48 --         Statement "SELECT collectd_insert($1, $2, $3, $4, $5, $6, $7, $8, $9);"
  49 --     </Writer>
  50 --     <Database foo>
  51 --         # ...
  52 --         Writer sqlstore
  53 --     </Database>
  54 -- </Plugin>
  55
  56 CREATE TABLE identifiers (
  57     id integer NOT NULL,
  58     host character varying(64) NOT NULL,
  59     plugin character varying(64) NOT NULL,
  60     plugin_inst character varying(64) DEFAULT NULL::character varying,
  61     type character varying(64) NOT NULL,
  62     type_inst character varying(64) DEFAULT NULL::character varying
  63 );
  64 CREATE SEQUENCE identifiers_id_seq
  65     START WITH 1
  66     INCREMENT BY 1
  67     NO MINVALUE
  68     NO MAXVALUE
  69     CACHE 1;
  70 ALTER SEQUENCE identifiers_id_seq OWNED BY identifiers.id;
  71 ALTER TABLE ONLY identifiers
  72     ALTER COLUMN id SET DEFAULT nextval('identifiers_id_seq'::regclass);
  73 ALTER TABLE ONLY identifiers
  74     ADD CONSTRAINT identifiers_host_plugin_plugin_inst_type_type_inst_key
  75         UNIQUE (host, plugin, plugin_inst, type, type_inst);
  76 ALTER TABLE ONLY identifiers
  77     ADD CONSTRAINT identifiers_pkey PRIMARY KEY (id);
  78
  79 -- optionally, create indexes for the identifier fields
  80 CREATE INDEX identifiers_host ON identifiers USING btree (host);
  81 CREATE INDEX identifiers_plugin ON identifiers USING btree (plugin);
  82 CREATE INDEX identifiers_plugin_inst ON identifiers USING btree (plugin_inst);
  83 CREATE INDEX identifiers_type ON identifiers USING btree (type);
  84 CREATE INDEX identifiers_type_inst ON identifiers USING btree (type_inst);
  85
  86 CREATE TABLE "values" (
  87     id integer NOT NULL,
  88     tstamp timestamp without time zone NOT NULL,
  89     name character varying(64) NOT NULL,
  90     value double precision NOT NULL
  91 );
  92
  93 -- partition "values" by day (or week, month, ...)
  94
  95 -- create the child tables for today and the next 'days' days:
  96 -- this may, for example, be used in a daily cron-job (or similar) to create
  97 -- the tables for the next couple of days
  98 CREATE OR REPLACE FUNCTION values_update_childs(
  99         integer
 100     ) RETURNS integer
 101     LANGUAGE plpgsql
 102     AS $_$
 103 DECLARE
 104     days alias for $1;
 105     cur_day date;
 106     next_day date;
 107     i integer;
 108     n integer;
 109 BEGIN
 110     IF days < 1 THEN
 111         RAISE EXCEPTION 'Cannot have negative number of days';
 112     END IF;
 113
 114     i := 0;
 115     n := 0;
 116     LOOP
 117         EXIT WHEN i > days;
 118
 119         SELECT CAST ('now'::date + i * '1day'::interval AS date) INTO cur_day;
 120         SELECT CAST ('now'::date + (i + 1) * '1day'::interval AS date) INTO next_day;
 121
 122         i := i + 1;
 123
 124         BEGIN
 125             EXECUTE 'CREATE TABLE "values$' || cur_day || '" (
 126                 CHECK (tstamp >= TIMESTAMP ''' || cur_day || ''' '
 127                     || 'AND tstamp < TIMESTAMP ''' || next_day || ''')
 128             ) INHERITS (values)';
 129         EXCEPTION WHEN duplicate_table THEN
 130             CONTINUE;
 131         END;
 132
 133         RAISE INFO 'Created table "values$%"', cur_day;
 134         n := n + 1;
 135
 136         EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '"
 137             ADD CONSTRAINT "values_' || cur_day || '_pkey"
 138                 PRIMARY KEY (id, tstamp, name, value)';
 139         EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '"
 140             ADD CONSTRAINT "values_' || cur_day || '_id_fkey"
 141                 FOREIGN KEY (id) REFERENCES identifiers(id)';
 142     END LOOP;
 143     RETURN n;
 144 END;
 145 $_$;
 146
 147 -- create initial child tables
 148 SELECT values_update_childs(2);
 149
 150 CREATE OR REPLACE FUNCTION values_insert_trigger()
 151     RETURNS trigger
 152     LANGUAGE plpgsql
 153     AS $_$
 154 DECLARE
 155     child_tbl character varying;
 156 BEGIN
 157     SELECT 'values$' || CAST (NEW.tstamp AS DATE) INTO child_tbl;
 158     -- Rather than using 'EXECUTE', some if-cascade checking the date may also
 159     -- be used. However, this would require frequent updates of the trigger
 160     -- function while this example works automatically.
 161     EXECUTE 'INSERT INTO "' || child_tbl || '" VALUES ($1.*)' USING NEW;
 162     RETURN NULL;
 163 END;
 164 $_$;
 165
 166 CREATE TRIGGER insert_values_trigger
 167     BEFORE INSERT ON values
 168     FOR EACH ROW EXECUTE PROCEDURE values_insert_trigger();
 169
 170 -- when querying values make sure to enable constraint exclusion
 171 -- SET constraint_exclusion = on;
 172
 173 CREATE OR REPLACE FUNCTION collectd_insert(
 174         timestamp, character varying,
 175         character varying, character varying,
 176         character varying, character varying,
 177         character varying[], character varying[], double precision[]
 178     ) RETURNS void
 179     LANGUAGE plpgsql
 180     AS $_$
 181 DECLARE
 182     p_time alias for $1;
 183     p_host alias for $2;
 184     p_plugin alias for $3;
 185     p_plugin_instance alias for $4;
 186     p_type alias for $5;
 187     p_type_instance alias for $6;
 188     p_value_names alias for $7;
 189     -- don't use the type info; for 'StoreRates true' it's 'gauge' anyway
 190     -- p_type_names alias for $8;
 191     p_values alias for $9;
 192     ds_id integer;
 193     i integer;
 194 BEGIN
 195     SELECT id INTO ds_id
 196         FROM identifiers
 197         WHERE host = p_host
 198             AND plugin = p_plugin
 199             AND COALESCE(plugin_inst, '') = COALESCE(p_plugin_instance, '')
 200             AND type = p_type
 201             AND COALESCE(type_inst, '') = COALESCE(p_type_instance, '');
 202     IF NOT FOUND THEN
 203         INSERT INTO identifiers (host, plugin, plugin_inst, type, type_inst)
 204             VALUES (p_host, p_plugin, p_plugin_instance, p_type, p_type_instance)
 205             RETURNING id INTO ds_id;
 206     END IF;
 207     i := 1;
 208     LOOP
 209         EXIT WHEN i > array_upper(p_value_names, 1);
 210         INSERT INTO values (id, tstamp, name, value)
 211             VALUES (ds_id, p_time, p_value_names[i], p_values[i]);
 212         i := i + 1;
 213     END LOOP;
 214 END;
 215 $_$;
 216
 217 -- vim: set expandtab :