zabbixで東京電力の過去分の値を入れる準備その４

これと
これの続きです。

注意：この作業を行うにあたって、心構えとしては

・運用中のzabbixが不安定になる覚悟をする。
・バックアップを取ったデータベースがなければ、過去データはすべて破棄する覚悟。
・作業を行った直後は問題がなかったとしても、特殊なデータや状況が発生した時に、データ破損してしまうことを受け入れる覚悟。

これらのことが許容できる環境の持ち主か、どうにもせっぱつまっていて、上記のリスクに比べたら相当に致命的な状況まで追い込まれている人じゃない限り、やらないほうが良い作業だと思っています。※技術力向上のためにやれるなら幸せかもですが。

今回の目的はSELECTで取得したデータを適切な型の変数に入れて、IF文などで分岐し、四則演算によって平均値とか
きちんと出るか?までをテストしました。

■まずはこのテストが完了した時点のfunctionです。

CREATE OR REPLACE FUNCTION insert_past_trends(TEXT,bigint,INTEGER,numeric(20,0))  RETURNS TEXT AS $$
DECLARE
 
 input_table  ALIAS FOR $1;
 input_itemid ALIAS FOR $2;
 input_clock  ALIAS FOR $3;
 input_value  ALIAS FOR $4;
 
 t_num trends.num%TYPE;
 t_value_min trends.value_min%TYPE;
 t_value_avg trends.value_avg%TYPE;
 t_value_max trends.value_max%TYPE;
 
 tu_num trends_uint.num%TYPE;
 tu_value_min trends_uint.value_min%TYPE;
 tu_value_avg trends_uint.value_avg%TYPE;
 tu_value_max trends_uint.value_max%TYPE;
 
 SQL TEXT;
 
 exist_trends      trends%ROWTYPE;
 exist_trends_uint trends_uint%ROWTYPE;
 
 
 
BEGIN
 
  SQL := 'SELECT * FROM '|| input_table ||' AS t WHERE t.itemid= '|| input_itemid ||' AND t.clock = '|| input_clock;
 
  IF input_table = 'trends' THEN
    EXECUTE SQL INTO exist_trends;
  ELSE
    EXECUTE SQL INTO exist_trends_uint;
  END IF;
 
-- trends 判定。
 
  IF exist_trends IS NOT NULL AND input_table = 'trends' THEN           --test table 'trends_debug'
 
    IF input_value < exist_trends.value_min THEN
      t_value_min = input_value;
    END IF;
    IF input_value > exist_trends.value_max THEN
      t_value_max = input_value;
    END IF;
    t_value_avg = (exist_trends.num * exist_trends.value_avg + input_value ) / (exist_trends.num +1);
 
    RAISE NOTICE ' trends.value_avg: %',t_value_avg;
    RETURN 'OK';
 
 
  ELSIF exist_trends_uint IS NOT NULL AND input_table = 'trends_uint' THEN  --test table 'trends_uint_debug'
 
    IF input_value < exist_trends_uint.value_min THEN
      tu_value_min = input_value;
    END IF;
    IF input_value > exist_trends_uint.value_max THEN
      tu_value_max = input_value;
    END IF;
    tu_value_avg = (exist_trends_uint.num * exist_trends_uint.value_avg + input_value ) / (exist_trends_uint.num +1);
 
 
    RAISE NOTICE ' trends_uint.value_avg: %',tu_value_avg;
    RAISE NOTICE ' trends_uint.value_min: %',tu_value_min;
    RAISE NOTICE ' trends_uint.value_max: %',tu_value_max;
    RETURN 'OK';
 
  END IF;
 
END;
 
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION insert_past_trends(TEXT,bigint,integer,numeric(20,0)) RETURNS TEXT AS $$ DECLARE input_table ALIAS FOR $1; input_itemid ALIAS FOR $2; input_clock ALIAS FOR $3; input_value ALIAS FOR $4; t_num trends.num%TYPE; t_value_min trends.value_min%TYPE; t_value_avg trends.value_avg%TYPE; t_value_max trends.value_max%TYPE; tu_num trends_uint.num%TYPE; tu_value_min trends_uint.value_min%TYPE; tu_value_avg trends_uint.value_avg%TYPE; tu_value_max trends_uint.value_max%TYPE; sql TEXT; exist_trends trends%ROWTYPE; exist_trends_uint trends_uint%ROWTYPE; BEGIN sql := 'SELECT * FROM '|| input_table ||' AS t WHERE t.itemid= '|| input_itemid ||' AND t.clock = '|| input_clock; IF input_table = 'trends' THEN EXECUTE sql INTO exist_trends; ELSE EXECUTE sql INTO exist_trends_uint; END IF; -- trends 判定。 IF exist_trends IS NOT NULL AND input_table = 'trends' THEN --test table 'trends_debug' IF input_value < exist_trends.value_min THEN t_value_min = input_value; END IF; IF input_value > exist_trends.value_max THEN t_value_max = input_value; END IF; t_value_avg = (exist_trends.num * exist_trends.value_avg + input_value ) / (exist_trends.num +1); RAISE NOTICE ' trends.value_avg: %',t_value_avg; RETURN 'OK'; ELSIF exist_trends_uint IS NOT NULL AND input_table = 'trends_uint' THEN --test table 'trends_uint_debug' IF input_value < exist_trends_uint.value_min THEN tu_value_min = input_value; END IF; IF input_value > exist_trends_uint.value_max THEN tu_value_max = input_value; END IF; tu_value_avg = (exist_trends_uint.num * exist_trends_uint.value_avg + input_value ) / (exist_trends_uint.num +1); RAISE NOTICE ' trends_uint.value_avg: %',tu_value_avg; RAISE NOTICE ' trends_uint.value_min: %',tu_value_min; RAISE NOTICE ' trends_uint.value_max: %',tu_value_max; RETURN 'OK'; END IF; END; $$ LANGUAGE plpgsql;

■最初に動いた様子average だけ表示。

zabbix=# SELECT insert_past_trends('trends_uint'::text,22497,1309870800,3333);
NOTICE:   trends_uint.value_avg: 33559091210
 insert_past_trends 
--------------------
 OK
(1 ROW)

■すべての値が同じで、numが1のデータでやってみる。

22497 | 1309964400 |   1 | 32430000000 | 32430000000 | 32430000000

・同じ値を入れてみた。予想では、全く同じ値が返るはず。

zabbix=# SELECT insert_past_trends('trends_uint'::text,22497,1309964400,32430000000);
NOTICE:   trends_uint.value_avg: 32430000000

平均値が同じ。成功！

■さらに、2倍のデータを入れてみる。

zabbix=# SELECT insert_past_trends('trends_uint'::text,22497,1309964400,64860000000);
NOTICE:   trends_uint.value_avg: 48645000000

1.5倍。計算通り。

■min,max も期待通りかテスト

zabbix=# SELECT insert_past_trends('trends_uint'::text,22497,1309964400,32430000001);
NOTICE:   trends_uint.value_avg: 32430000001
NOTICE:   trends_uint.value_min: <NULL>
NOTICE:   trends_uint.value_max: 32430000001
 insert_past_trends 
--------------------
 OK
(1 ROW)
 
zabbix=# SELECT insert_past_trends('trends_uint'::text,22497,1309964400,32430000002);
NOTICE:   trends_uint.value_avg: 32430000001
NOTICE:   trends_uint.value_min: <NULL>
NOTICE:   trends_uint.value_max: 32430000002
 insert_past_trends 
--------------------
 OK
(1 ROW)
 
zabbix=# 
zabbix=# SELECT insert_past_trends('trends_uint'::text,22497,1309964400,32420000000);
NOTICE:   trends_uint.value_avg: 32425000000
NOTICE:   trends_uint.value_min: 32420000000
NOTICE:   trends_uint.value_max: <NULL>
 insert_past_trends 
--------------------
 OK
(1 ROW)

問題ないみたい。

■この時点で速度を計測。単純にSELECTして値を持ってくるところを抜き出してみました。
それぞれ10回程度実行して、平均的な値を持ってきたものです。

zabbix=# EXPLAIN ANALYZE SELECT insert_past_trends('trends_uint'::text,22497,1309964400,32420000000
);
NOTICE:   trends_uint.value_avg: 32425000000
NOTICE:   trends_uint.value_min: 32420000000
NOTICE:   trends_uint.value_max: <NULL>
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=0) (actual TIME=0.957..0.959 rows=1 loops=1)
 Total runtime: 1.012 ms
(2 rows)
 
zabbix=# EXPLAIN ANALYZE SELECT * FROM trends_uint WHERE itemid = 22497 AND clock = 1309964400;
                                                          QUERY PLAN                               
 
---------------------------------------------------------------------------------------------------
----------------------------
 INDEX Scan using trends_uint_pkey ON trends_uint  (cost=0.00..6.63 rows=1 width=43) (actual TIME=0
.042..0.045 rows=1 loops=1)
   INDEX Cond: ((itemid = 22497) AND (clock = 1309964400))
 Total runtime: 0.124 ms
(3 rows)

約10倍ですね。変数の処理とか計算とかで使っているみたいです。
ここからわかるのは、すべてがストアドプロシージャだと早いのか?という問いには
全てじゃないという答えでしょうか。どうしても、変数の宣言が多くなるし、無駄だと思うデータでも
可読性あげるために処理したりとか必要に思えましたので。

今回のように、手動で入れるという特殊なケースだと、ここまで作りこんだfunctionを作るかもです。
SQLを頻繁に実行したり、データ構造をしっかり利用した処理を作るとかなら、さらに効果が増すのではないかと思います。

■ほかに確認した仕様
四則演算がどのようになっているのかを確認。どうも、小数点がついている、ついていないで処理が違うらしい。

zabbix=# SELECT ( 3 * 4 + 15 ) / ( 3 + 7 );
 ?column? 
----------
        2
(1 ROW)
 
zabbix=# SELECT ( 3.0 * 4.0 + 15.0 ) / ( 3.0 + 7.0 );
      ?column?      
--------------------
 2.7000000000000000
(1 ROW)
 
zabbix=# SELECT ( 3.0 * 4.0 + 15.0 ) / ( 3.0 + 15.0 );
      ?column?      
--------------------
 1.5000000000000000
(1 ROW)
 
zabbix=# SELECT ( 3.00 * 4.00 + 15.00 ) / ( 3.00 + 15.00 );
      ?column?      
--------------------
 1.5000000000000000
(1 ROW)
 
zabbix=# SELECT ( 3.00 * 4.00 + 15.00 ) / ( 3.00 + 16.00 );
      ?column?      
--------------------
 1.4210526315789474

■出たエラーのまとめ。

zabbix=# SELECT insert_past_trends('trends_uint'::text,22497,1309870800,3333);
ERROR:  RECORD "buff" IS NOT assigned yet
DETAIL:  The tuple structure OF a not-yet-assigned RECORD IS indeterminate.
CONTEXT:  PL/pgSQL FUNCTION "insert_past_trends" line 20 AT assignment

この時はbuff RECORD型にして、結果を取得できないか探してました。
SELECT || buff || と記述ミスをして何も入っていないbuff を指定したためでてます。

zabbix=# SELECT insert_past_trends('trends_uint'::text,22497,1309870800,3333);
ERROR:  syntax error AT OR near "INTO"
LINE 1: SELECT INTO buff num,value_min,value_avg,value_max FROM tren...
               ^
QUERY:  SELECT INTO buff num,value_min,value_avg,value_max FROM trends_uint AS t WHERE t.itemid= 22497 AND t.clock = 1309870800
CONTEXT:  PL/pgSQL FUNCTION "insert_past_trends" line 21 AT EXECUTE statement

EXECUTE で実行できないのかな。。？

FOR IN EXECUTE でRECORDSにいれている例
http://www.postgresql.jp/document/pg743doc/html/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

http://www.postgresql.jp/document/pg743doc/html/plpgsql-statements.html
↓ カーソル使えから変化している。

http://www.postgresql.jp/document/pg835doc/html/plpgsql-statements.html

行型で指定して、OK
EXECUTE sql INTO f_trends_unit;

ERROR:  missing FROM-clause entry FOR TABLE "exist_trends_unit"
LINE 1: SELECT   $1  < exist_trends_unit.value_min
                       ^
QUERY:  SELECT   $1  < exist_trends_unit.value_min
CONTEXT:  PL/pgSQL FUNCTION "insert_past_trends" line 53 AT IF

x exist_trends_unit.value
o exist_trends_uint.value
記述ミス。

8/1追記。
条件判定に致命的なミス。→シェルスクリプトのテストまで失念してました。
INSERTとUPDATEが切り替わる条件判定のテストをしていなかったせいで、IF NOT FOUND の部分がPL/pgSQL例文そのままに。

  IF NOT FOUND AND input_table = 'trends' THEN           --test table 'trends_debug'
   ↓
  IF exist_trends IS NOT NULL AND input_table = 'trends' THEN           --test table 'trends_debug'

SeedsLight

Server and Development Blog

zabbixで東京電力の過去分の値を入れる準備その４