Add write_syslog output plugin
[collectd.git] / 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 maintenance.
37 -- Please note that additional maintenance 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 with time zone NOT NULL,
89     name character varying(64) NOT NULL,
90     value double precision NOT NULL
91 );
92
93 CREATE OR REPLACE VIEW collectd
94     AS SELECT host, plugin, plugin_inst, type, type_inst,
95             host
96                 || '/' || plugin
97                 || CASE
98                     WHEN plugin_inst IS NOT NULL THEN '-'
99                     ELSE ''
100                 END
101                 || coalesce(plugin_inst, '')
102                 || '/' || type
103                 || CASE
104                     WHEN type_inst IS NOT NULL THEN '-'
105                     ELSE ''
106                 END
107                 || coalesce(type_inst, '') AS identifier,
108             tstamp, name, value
109         FROM identifiers
110             JOIN values
111             ON values.id = identifiers.id;
112
113 -- partition "values" by day (or week, month, ...)
114
115 -- create the child tables for today and the next 'days' days:
116 -- this may, for example, be used in a daily cron-job (or similar) to create
117 -- the tables for the next couple of days
118 CREATE OR REPLACE FUNCTION values_update_childs(
119         integer
120     ) RETURNS SETOF text
121     LANGUAGE plpgsql
122     AS $_$
123 DECLARE
124     days alias for $1;
125     cur_day date;
126     next_day date;
127     i integer;
128 BEGIN
129     IF days < 1 THEN
130         RAISE EXCEPTION 'Cannot have negative number of days';
131     END IF;
132
133     i := 0;
134     LOOP
135         EXIT WHEN i > days;
136
137         SELECT CAST ('now'::date + i * '1day'::interval AS date) INTO cur_day;
138         SELECT CAST ('now'::date + (i + 1) * '1day'::interval AS date) INTO next_day;
139
140         i := i + 1;
141
142         BEGIN
143             EXECUTE 'CREATE TABLE "values$' || cur_day || '" (
144                 CHECK (tstamp >= TIMESTAMP ''' || cur_day || ''' '
145                     || 'AND tstamp < TIMESTAMP ''' || next_day || ''')
146             ) INHERITS (values)';
147         EXCEPTION WHEN duplicate_table THEN
148             CONTINUE;
149         END;
150
151         RETURN NEXT 'values$' || cur_day::text;
152
153         EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '"
154             ADD CONSTRAINT "values_' || cur_day || '_pkey"
155                 PRIMARY KEY (id, tstamp, name, value)';
156         EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '"
157             ADD CONSTRAINT "values_' || cur_day || '_id_fkey"
158                 FOREIGN KEY (id) REFERENCES identifiers(id)';
159     END LOOP;
160     RETURN;
161 END;
162 $_$;
163
164 -- create initial child tables
165 SELECT values_update_childs(2);
166
167 CREATE OR REPLACE FUNCTION values_insert_trigger()
168     RETURNS trigger
169     LANGUAGE plpgsql
170     AS $_$
171 DECLARE
172     child_tbl character varying;
173 BEGIN
174     SELECT 'values$' || CAST (NEW.tstamp AS DATE) INTO child_tbl;
175     -- Rather than using 'EXECUTE', some if-cascade checking the date may also
176     -- be used. However, this would require frequent updates of the trigger
177     -- function while this example works automatically.
178     EXECUTE 'INSERT INTO "' || child_tbl || '" VALUES ($1.*)' USING NEW;
179     RETURN NULL;
180 END;
181 $_$;
182
183 CREATE TRIGGER insert_values_trigger
184     BEFORE INSERT ON values
185     FOR EACH ROW EXECUTE PROCEDURE values_insert_trigger();
186
187 -- when querying values make sure to enable constraint exclusion
188 -- SET constraint_exclusion = on;
189
190 CREATE OR REPLACE FUNCTION collectd_insert(
191         timestamp with time zone, character varying,
192         character varying, character varying,
193         character varying, character varying,
194         character varying[], character varying[], double precision[]
195     ) RETURNS void
196     LANGUAGE plpgsql
197     AS $_$
198 DECLARE
199     p_time alias for $1;
200     p_host alias for $2;
201     p_plugin alias for $3;
202     p_plugin_instance alias for $4;
203     p_type alias for $5;
204     p_type_instance alias for $6;
205     p_value_names alias for $7;
206     -- don't use the type info; for 'StoreRates true' it's 'gauge' anyway
207     -- p_type_names alias for $8;
208     p_values alias for $9;
209     ds_id integer;
210     i integer;
211 BEGIN
212     SELECT id INTO ds_id
213         FROM identifiers
214         WHERE host = p_host
215             AND plugin = p_plugin
216             AND COALESCE(plugin_inst, '') = COALESCE(p_plugin_instance, '')
217             AND type = p_type
218             AND COALESCE(type_inst, '') = COALESCE(p_type_instance, '');
219     IF NOT FOUND THEN
220         INSERT INTO identifiers (host, plugin, plugin_inst, type, type_inst)
221             VALUES (p_host, p_plugin, p_plugin_instance, p_type, p_type_instance)
222             RETURNING id INTO ds_id;
223     END IF;
224     i := 1;
225     LOOP
226         EXIT WHEN i > array_upper(p_value_names, 1);
227         INSERT INTO values (id, tstamp, name, value)
228             VALUES (ds_id, p_time, p_value_names[i], p_values[i]);
229         i := i + 1;
230     END LOOP;
231 END;
232 $_$;
233
234 -- vim: set expandtab :