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

これと
これと
これの続きです。

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

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

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

今回はfunctionをUPDATE,INSERTできるところまで作りこみます。

■今回完成したfunction(_debug)テーブルに突っ込むものです。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

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_debug' THEN              --test table 'trends_debug'
    EXECUTE SQL INTO exist_trends;
  ELSE
    EXECUTE SQL INTO exist_trends_uint;
  END IF;
 
-- trends UPDATE 処理。
  IF NOT FOUND AND input_table = 'trends_debug' THEN           --test table 'trends_debug'
    IF input_value < exist_trends.value_min THEN
      t_value_min = input_value;
    ELSE
      t_value_min = exist_trends.value_min;
    END IF;
    IF input_value > exist_trends.value_max THEN
      t_value_max = input_value;
    ELSE
      t_value_max = exist_trends.value_max;
    END IF;
    t_value_avg = (exist_trends.num * exist_trends.value_avg + input_value ) / (exist_trends.num +1);
    t_num := exist_trends.num +1;
 
    SQL := 'UPDATE '|| input_table || 
         ' SET num='|| t_num ||' ,value_min='|| t_value_min || ' ,value_avg=' ||t_value_avg ||',value_max='|| t_value_max ||
           'WHERE itemid = '|| input_itemid ||' AND clock = '|| input_clock;
    -- RAISE NOTICE '%',sql;
    EXECUTE SQL ;
    -- RAISE NOTICE ' trends.value_min: %',t_value_min;
    -- RAISE NOTICE ' trends.value_avg: %',t_value_avg;
    -- RAISE NOTICE ' trends.value_max: %',t_value_max;
    RETURN 'OK';
 
 
-- trends_uint UPDATE 処理。
  ELSIF NOT FOUND AND input_table = 'trends_uint_debug' THEN  --test table 'trends_uint_debug'
 
    IF input_value < exist_trends_uint.value_min THEN
      tu_value_min = input_value;
    ELSE
      tu_value_min = exist_trends_uint.value_min;
    END IF;
    IF input_value > exist_trends_uint.value_max THEN
      tu_value_max = input_value;
    ELSE
      tu_value_max = exist_trends_uint.value_max;
    END IF;
    tu_value_avg = (exist_trends_uint.num * exist_trends_uint.value_avg + input_value ) / (exist_trends_uint.num +1);
    tu_num := exist_trends_uint.num +1;
 
    SQL :='UPDATE '||input_table ||
         ' SET num='|| tu_num ||' ,value_min='|| tu_value_min ||' ,value_avg='|| tu_value_avg ||
         ' ,value_max='|| tu_value_max ||
         'WHERE itemid ='|| input_itemid ||' AND clock ='|| input_clock;
    EXECUTE SQL ;
 
    -- 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 'UPDATE OK';
 
-- trends trends_uint INSERT 処理。
  ELSE
    SQL := 'INSERT INTO '|| input_table ||
    ' ('|| input_clock || ','|| input_itemid||',1,'|| input_value|| ',' || input_value ||','|| input_value||')';
    EXECUTE SQL;
    RETURN 'INSERT OK';
 
  END IF;
 
END;
 
$$ LANGUAGE plpgsql;

※上記functionは致命的なミスをしています。

39

  IF NOT FOUND AND input_table = 'trends_debug' THEN           --test table 'trends_debug'
65

  ELSIF NOT FOUND AND input_table = 'trends_uint_debug' THEN  --test table 'trends_uint_debug'

これは次回気づいた箇所です。

SELECTで既存データがなかった場合・・・を判定基準して書いてしまっているのに、
正常動作として動作したものと考えてテストを行っている。

EXECUTEは中身がSELECT文だったとしても、IF [NOT] FOUND では検知しないみたいです。・・・IF文で囲っているからかもしれませんが。

では引き続き、入れた経緯をまとめます。

■テスト用のテーブルを作る。

CREATE TABLE trends_debug AS
SELECT * FROM trends;
 
ALTER TABLE trends_debug ADD PRIMARY KEY (itemid,clock);
 
CREATE TABLE trends_uint_debug AS
SELECT * FROM trends_uint;
 
ALTER TABLE trends_uint_debug ADD PRIMARY KEY (itemid,clock);

最初からこれでテストしろと言われそうですけれどね。。

テスト用のSQLコマンドも変えて、

SELECT insert_past_trends('trends_uint_debug'::text,22497,1309964400,32420000000);
SELECT insert_past_trends('trends_uint_debug'::text,22497,1309964400,64860000000);

判定部分も

  IF NOT FOUND AND input_table = 'trends' THEN           --test table 'trends_debug'
  ELSIF NOT FOUND AND input_table = 'trends_uint' THEN  --test table 'trends_uint_debug'
 
 
  IF NOT FOUND AND input_table = 'trends_debug' THEN           --test table 'trends_debug'
  ELSIF NOT FOUND AND input_table = 'trends_uint_debug' THEN  --test table 'trends_uint_debug'

■テスト例1(trend_uint_debug)

実行前

zabbix=# SELECT * FROM trends_uint_debug WHERE itemid = 22497 AND clock = 1309964400;
 itemid |   clock    | num |  value_min  |  value_avg  |  value_max  
--------+------------+-----+-------------+-------------+-------------
  22497 | 1309964400 |   1 | 32430000000 | 32430000000 | 32430000000

実行

# SELECT insert_past_trends('trends_uint_debug'::text,22497,1309964400,32420000000);
 insert_past_trends 
--------------------
 UPDATE OK
(1 ROW)

結果

zabbix=# SELECT * FROM trends_uint_debug WHERE itemid = 22497 AND clock = 1309964400;
 itemid |   clock    | num |  value_min  |  value_avg  |  value_max  
--------+------------+-----+-------------+-------------+-------------
  22497 | 1309964400 |   2 | 32420000000 | 32425000000 | 32430000000
(1 ROW)

■テスト例2(trend_uint_debug)
実行前はテスト例1の結果

実行

zabbix=# SELECT insert_past_trends('trends_uint_debug'::text,22497,1309964400,32410000000);
 insert_past_trends 
--------------------
 UPDATE OK
(1 ROW)

結果

zabbix=# SELECT * FROM trends_uint_debug WHERE itemid = 22497 AND clock = 1309964400;
 itemid |   clock    | num |  value_min  |  value_avg  |  value_max  
--------+------------+-----+-------------+-------------+-------------
  22497 | 1309964400 |   3 | 32410000000 | 32420000000 | 32430000000
(1 ROW)

■trends のほうもテスト。

テストデータはこれを使ってみる。

zabbix=# SELECT * FROM trends WHERE num = 1 limit 10;
 itemid |   clock    | num | value_min | value_avg | value_max 
--------+------------+-----+-----------+-----------+-----------
  22498 | 1309867200 |   1 |   71.8500 |   71.8500 |   71.8500

実行SQL

SELECT insert_past_trends('trends_debug'::text,22498,1309867200,70.1500);

実行前

zabbix=# SELECT * FROM trends_debug WHERE itemid = 22498 AND clock = 1309867200;
 itemid |   clock    | num | value_min | value_avg | value_max 
--------+------------+-----+-----------+-----------+-----------
  22498 | 1309867200 |   1 |   71.8500 |   71.8500 |   71.8500

実行

zabbix=# SELECT insert_past_trends('trends_debug'::text,22498,1309867200,70.1500);

結果

zabbix=# SELECT * FROM trends_debug WHERE itemid = 22498 AND clock = 1309867200;
 itemid |   clock    | num | value_min | value_avg | value_max 
--------+------------+-----+-----------+-----------+-----------
  22498 | 1309867200 |   2 |   70.1500 |   71.0000 |   71.8500
(1 ROW)

■trendsのテスト2

実行

zabbix=# SELECT insert_past_trends('trends_debug'::text,22498,1309867200,70.1500);

結果

zabbix=# SELECT * FROM trends_debug WHERE itemid = 22498 AND clock = 1309867200;
 itemid |   clock    | num | value_min | value_avg | value_max 
--------+------------+-----+-----------+-----------+-----------
  22498 | 1309867200 |   3 |   70.1500 |   70.7167 |   71.8500
(1 ROW)

うまくいって浮かれていましたが、INSERTのテストをしていないことにこの時点では気づいていませんでした。
続きます。

■エラー集

ERROR:  syntax error AT OR near "'WHERE itemid = '"
LINE 1: ...  || ' ,value_avg='|| $4  || ',value_max='||  $5  'WHERE ite...
                                                             ^
QUERY:  SELECT  'UPDATE '||  $1 || ' SET num='||  $2  ||' ,value_min='||  $3  || ' ,value_avg='|| $4  || ',value_max='||  $5  'WHERE itemid = '||  $6  ||' AND clock = '||  $7 
CONTEXT:  SQL statement in PL/PgSQL function "insert_past_trends" near line 54

WHERE の前に||が足らなかった。

zabbix$# $$ LANGUAGE plpgsql;
ERROR:  syntax error AT OR near "$1"
LINE 1: INSERT  $1  ( $2 , $3 ,1, $4 , $4 , $4 )
                ^
QUERY:  INSERT  $1  ( $2 , $3 ,1, $4 , $4 , $4 )
CONTEXT:  SQL statement IN PL/PgSQL FUNCTION "insert_past_trends" near line 90

INSERT INTO を記述ミス。
EXECUTEにもしてない。

zabbix=# SELECT insert_past_trends('trends_debug'::text,22498,1309867200,70.1500);
ERROR:  query string argument OF EXECUTE IS NULL
CONTEXT:  PL/pgSQL FUNCTION "insert_past_trends" line 56 AT EXECUTE statement

関連記事:

  1. zabbixで東京電力の過去分の値を入れる準備その４
  2. zabbixで東京電力の過去分の値を入れる準備その３
  3. zabbixで東京電力の過去分の値を入れる準備その２
  4. zabbixで東京電力の過去分の値を入れる準備
  5. ソース引用部分を見やすくするため、WP-Syntax プラグインのインストール