X-Git-Url: https://git.verplant.org/?a=blobdiff_plain;f=contrib%2Fpostgresql%2Fcollectd_insert.sql;h=bee182cb715f703605c7983a2cccbb2c12f3af37;hb=de21923bcdbd01827f445eb1f573778b7bfa5202;hp=67a106e4acb17c3c4393d539c10515f477b965e4;hpb=e0d76f0aa2258b01a85f32b7bb3e0f0f2342c537;p=collectd.git

diff --git a/contrib/postgresql/collectd_insert.sql b/contrib/postgresql/collectd_insert.sql
index 67a106e4..bee182cb 100644
--- a/contrib/postgresql/collectd_insert.sql
+++ b/contrib/postgresql/collectd_insert.sql
@@ -33,8 +33,8 @@
 -- and 'values' to store the value-list identifier and the actual values
 -- respectively.
 --
--- The 'values' table is partitioned to improve performance and maintainance.
--- Please note that additional maintainance scripts are required in order to
+-- The 'values' table is partitioned to improve performance and maintenance.
+-- Please note that additional maintenance scripts are required in order to
 -- keep the setup running -- see the comments below for details.
 --
 -- The function 'collectd_insert' may be used to actually insert values
@@ -85,33 +85,84 @@ CREATE INDEX identifiers_type_inst ON identifiers USING btree (type_inst);
 
 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
@@ -137,7 +188,7 @@ CREATE TRIGGER insert_values_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[]
@@ -180,3 +231,4 @@ BEGIN
 END;
 $_$;
 
+-- vim: set expandtab :