前提・実現したいこと
PostgreSQL9.6.6で開発しています。
1レコードのみ存在するテーブルに対して1秒間に数十回のUPDATEを常時行っているのですが、
数日に1度の頻度で2レコードに増えてしまいます。
概要テーブルに対してINSERTは行っていないので、2レコードに増えるはずはないので、
原因がわかりません。
2レコードに増える原因、増やさない対処方法はございませんでしょうか。
発生している問題・エラーメッセージ
UPDATEのみ行っているテーブルでレコード数が増えてしまう。
該当のソースコード
PostgreSQL9.6.6
CREATE TABLE 最新データtmp (日時 TIMESTAMP,担当者 VARCHAR,ID INTEGER); CREATE TABLE 最新データ (日時 TIMESTAMP,担当者 VARCHAR,ID INTEGER); INSERT INTO 最新データ (日時) VALUES (CURRENT_TIMESTAMP); CREATE FUNCTION 最新データins() RETURNS TRIGGER AS $$ DECLARE BEGIN UPDATE 最新データ SET 日時 = current_timestamp, 担当者 = NEW.担当者, ID = NEW.ID; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER 最新データtmp BEFORE INSERT ON 最新データtmp FOR EACH ROW EXECUTE PROCEDURE 最新データins();
上記実行後に 最新データtmp テーブルに対して1秒間に数十回のINSERTを行っており、
その都度 最新データ テーブルが上書きされるようにしております。
INSERT INTO 最新データtmp (日時,担当者,ID) VALUES (current_timestamp,'太郎',12345);
数日に1度の頻度で 最新データ テーブルが2レコードに増えてしまいます。
発生時刻に規則性はありませんでした。
また、最新データtmp テーブルは日に1度TRUNCATEしております。
INSERT INTO 文を実行するきっかけは何ですか? web由来なのか、ファイルアップロード由来なのか、crontab由来なのかなど。きっかけを2回貰えば2件レコードができるので、2回実行しているんだろうなっていう目で点検していますか? また、テーブル「最新データtmp」「最新データ」どちらも主キーやユニークキーが設定されていないように見えますが、日時でユニークキーが設定されていれば、トランザクション時の日時(current_timestamp)を使えばもしも2回実行しているとしても2度目はエラーで引っかかったりしますけども。
INSERT INTO文はODBCから受け取っております。
UPDATEは複数回実行されているはずですが、INSERTを行っていないので、レコード数が増える原因がわかりません。
INSERT INTO 最新データtmp (日時,担当者,ID) VALUES
(current_timestamp,'太郎',12345),
(current_timestamp,'太郎',12346);
という形でデータを入れても、通常では最新データtmpは1行のままでした。
日時にユニークキーを設定したことがあります。
その際は同一内容で2レコードに増えたうえで、次の 最新データtmp テーブルへのINSERT時にエラーが発生しました。エラーが起こらないようにしたいです。
最新データは1行だけなのですか?
主キーのないテーブルを作って運用したことは無いので未知ですが
確かPostgreSQLのUPDATEって新しいデータの 追記 と古いデータに削除済みフラグ立てる更新の組み合わせでしたよね?
主キーないのがそこでトラブル起こしてるとかでしょうか?
> INSERT INTO 最新データtmp (日時,担当者,ID) VALUES
> (current_timestamp,'太郎',12345),
> , (current_timestamp,'太郎',12346);
> という形でデータを入れても、通常では最新データtmpは1行のままでした。
1行で当然という事みたいですけど、なぜ1行なのですか?
プライマリーで弾かれない限り追加されるはずですけど。
> 最新データは1行だけなのですか?
事象ではなく、仕様の確認です。
updateに条件が無い事から、最新の1行というのが仕様でしょうか?
多分
通常では最新データ は1行のままでした。
の誤記ですよね。
UPDATEは新しいデータの 追記 と古いデータに削除済みフラグ立てる更新の組み合わせなので、そのあたりで何か起きているのではないかと思うのですが、わかりませんでした。
頻繁にvacuumがおこなわれているので、その際に不要な行を消しそびれたのかもと思ったのですが、わかりませんでした。
最新データを1行だけ という仕様で作っており、最新の1行のみが保存されるようにしております。
申し訳ありません。通常では最新データ は1行のままでした。
の誤記です。
最新データtmp テーブルは日に1度TRUNCATEとありますが、NULLリターンで作成されていないのでクリア(VACUUM)すべきは、最新データの方かと。AUTOVACUUMをしているとは思いますが更新中はスキップされます。
最新データtmp テーブルはRETURN NULLになっており、レコードが無いせいか、ここ数か月間でAUTOVACUUMは実行されたことはありませんでした。
最新データ テーブルは数分に1度AUTOVACUUMが実行されており、これまで数千回実行されていました。
更新中はスキップされるとのことですので、更新の合間に行われていたのだと思います。
余談ですがVACUMMは実行されてもremovable可能数が1000個でも、10個または0個の処理となる事が有ります。これはページ8Kの中で動きが無い場合を対象にしている様です。VERBOSEを付けると得られます。最大2個でしょうか?XIDが戻ると古いのが見れますが心臓部なのでさすがに無いでしょうね。
VACUUM VERBOSE ~を実行してみました。
全34ページ中の34ページで見つかった行バージョン:移動可能 11行、削除負荷1行
0 dead row versions cannot be removed yet.
There where 241 unused item pointers.
skipped 0 pages due to buffer pins.
などと表示されました。
上記を見る限り問題はなさそうでうです。横道で申し訳ない。当システムでも更新が激しいものはFREEZEとANARYZEを行ってはいます。UPDATEで増える事はなさそうに思います。テーブル名tmpのtmpの無いインサートが流れる方の可能性が大きいです。駄案ですが「最新データ」名称を変えてみては?