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,
8 in_ds_type datasource_type,
10 in_plugin_instance text,
13 in_type_instance text) returns void as $$
19 select into host_id id from hostname_dimension where hostname = in_hostname;
21 insert into hostname_dimension (hostname) values (in_hostname) returning id into host_id;
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;
27 select into a_plugin_id id from plugin_dimension where plugin = in_plugin and plugin_instance = in_plugin_instance;
31 insert into plugin_dimension (plugin, plugin_instance) values (in_plugin, in_plugin_instance) returning id into a_plugin_id;
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;
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;
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;
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);
48 create or replace function get_interval(start_timestamp timestamp, length interval, step text) returns SETOF timestamp as $$
50 v_timestamp timestamp;
51 end_timestamp timestamp;
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;
62 create or replace function create_partition_trigger(parent text,
63 start_timestamp timestamp,
66 format text) returns void as $trigger$
74 v_current_date := date(start_timestamp);
75 v_function := 'CREATE OR REPLACE FUNCTION ' || parent || '_insert_trigger() '
76 || 'RETURNS TRIGGER LANGUAGE plpgsql AS $$ '
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
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;
92 v_function := v_function || 'ELSE RETURN NEW; END IF; RETURN NULL; END; $$';
95 $trigger$ LANGUAGE plpgsql;
97 create or replace function create_partition_tables(parent text, start_timestamp timestamp, length interval, step text, format text) returns void as $$
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 || ');'
113 EXECUTE 'create index index_' || table_name || '_on_timestamp_hostname_and_plugin_and_type on ' || table_name || ' (timestamp, hostname_id, plugin_id, type_id);';
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.
124 -- WARNING - for large datasets THIS WILL BE SLOW!
126 -- create view metrics_view as
128 -- ((m.measure - lag(m.measure)
129 -- over(partition by m.hostname_id,
132 -- order by timestamp, m.hostname_id, p.plugin_id, t.type_id))) AS metric,
136 -- FROM metrics m, plugin_dimension p, type_dimension t
137 -- where m.type_id = t.id
138 -- and t.ds_type = 'COUNTER'
140 -- select timestamp, m.measure as metric,
144 -- FROM metrics m, type_dimension t
145 -- where m.type_id = t.id
146 -- and t.ds_type = 'GUAGE';