CREATE TABLE "values" (
id integer NOT NULL,
- tstamp timestamp without time zone NOT NULL,
+ tstamp timestamp with time zone NOT NULL,
name character varying(64) NOT NULL,
value double precision NOT NULL
);
+CREATE OR REPLACE VIEW collectd
+ AS SELECT host, plugin, plugin_inst, type, type_inst,
+ host
+ || '/' || plugin
+ || CASE
+ WHEN plugin_inst IS NOT NULL THEN '-'
+ ELSE ''
+ END
+ || coalesce(plugin_inst, '')
+ || '/' || type
+ || CASE
+ WHEN type_inst IS NOT NULL THEN '-'
+ ELSE ''
+ END
+ || coalesce(type_inst, '') AS identifier,
+ tstamp, name, value
+ FROM identifiers
+ JOIN values
+ ON values.id = identifiers.id;
+
-- partition "values" by day (or week, month, ...)
--- make sure the CHECKs don't overlap!
-CREATE TABLE "values$2012-08-19" (
- CHECK (tstamp >= TIMESTAMP '2012-08-19' AND tstamp < TIMESTAMP '2012-08-20')
-) INHERITS (values);
-CREATE TABLE "values$2012-08-20" (
- CHECK (tstamp >= TIMESTAMP '2012-08-20' AND tstamp < TIMESTAMP '2012-08-21')
-) INHERITS (values);
--- ...
--- set up a daily (weekly, monthly, ...) cron-job (or similar) to create the
--- tables for the next day (week, month, ...)
-
-ALTER TABLE ONLY "values$2012-08-19"
- ADD CONSTRAINT "values_2012-08-19_pkey" PRIMARY KEY (id, tstamp, name, value);
-ALTER TABLE ONLY "values$2012-08-19"
- ADD CONSTRAINT "values_2012-08-19_id_fkey" FOREIGN KEY (id) REFERENCES identifiers(id);
-
-ALTER TABLE ONLY "values$2012-08-20"
- ADD CONSTRAINT "values_2012-08-20_pkey" PRIMARY KEY (id, tstamp, name, value);
-ALTER TABLE ONLY "values$2012-08-20"
- ADD CONSTRAINT "values_2012-08-20_id_fkey" FOREIGN KEY (id) REFERENCES identifiers(id);
--- ...
+
+-- create the child tables for today and the next 'days' days:
+-- this may, for example, be used in a daily cron-job (or similar) to create
+-- the tables for the next couple of days
+CREATE OR REPLACE FUNCTION values_update_childs(
+ integer
+ ) RETURNS SETOF text
+ LANGUAGE plpgsql
+ AS $_$
+DECLARE
+ days alias for $1;
+ cur_day date;
+ next_day date;
+ i integer;
+BEGIN
+ IF days < 1 THEN
+ RAISE EXCEPTION 'Cannot have negative number of days';
+ END IF;
+
+ i := 0;
+ LOOP
+ EXIT WHEN i > days;
+
+ SELECT CAST ('now'::date + i * '1day'::interval AS date) INTO cur_day;
+ SELECT CAST ('now'::date + (i + 1) * '1day'::interval AS date) INTO next_day;
+
+ i := i + 1;
+
+ BEGIN
+ EXECUTE 'CREATE TABLE "values$' || cur_day || '" (
+ CHECK (tstamp >= TIMESTAMP ''' || cur_day || ''' '
+ || 'AND tstamp < TIMESTAMP ''' || next_day || ''')
+ ) INHERITS (values)';
+ EXCEPTION WHEN duplicate_table THEN
+ CONTINUE;
+ END;
+
+ RETURN NEXT 'values$' || cur_day::text;
+
+ EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '"
+ ADD CONSTRAINT "values_' || cur_day || '_pkey"
+ PRIMARY KEY (id, tstamp, name, value)';
+ EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '"
+ ADD CONSTRAINT "values_' || cur_day || '_id_fkey"
+ FOREIGN KEY (id) REFERENCES identifiers(id)';
+ END LOOP;
+ RETURN;
+END;
+$_$;
+
+-- create initial child tables
+SELECT values_update_childs(2);
CREATE OR REPLACE FUNCTION values_insert_trigger()
RETURNS trigger
-- SET constraint_exclusion = on;
CREATE OR REPLACE FUNCTION collectd_insert(
- timestamp, character varying,
+ timestamp with time zone, character varying,
character varying, character varying,
character varying, character varying,
character varying[], character varying[], double precision[]
END;
$_$;
+-- vim: set expandtab :