PostgreSQLでUPDATEしかしていないテーブルでレコードが増える

Question

前提・実現したいこと

PostgreSQL9.6.6で開発しています。
１レコードのみ存在するテーブルに対して１秒間に数十回のUPDATEを常時行っているのですが、
数日に１度の頻度で２レコードに増えてしまいます。

概要テーブルに対してINSERTは行っていないので、２レコードに増えるはずはないので、
原因がわかりません。
２レコードに増える原因、増やさない対処方法はございませんでしょうか。

発生している問題・エラーメッセージ

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 テーブルに対して１秒間に数十回のINSERTを行っており、
その都度 最新データ テーブルが上書きされるようにしております。

INSERT INTO 最新データtmp (日時,担当者,ID) VALUES (current_timestamp,'太郎',12345);

数日に１度の頻度で 最新データ テーブルが２レコードに増えてしまいます。
発生時刻に規則性はありませんでした。
また、最新データtmp テーブルは日に１度TRUNCATEしております。

Comments

[退会済みユーザー]

INSERT INTO 文を実行するきっかけは何ですか？ web由来なのか、ファイルアップロード由来なのか、crontab由来なのかなど。きっかけを2回貰えば2件レコードができるので、2回実行しているんだろうなっていう目で点検していますか？ また、テーブル「最新データtmp」「最新データ」どちらも主キーやユニークキーが設定されていないように見えますが、日時でユニークキーが設定されていれば、トランザクション時の日時（current_timestamp）を使えばもしも2回実行しているとしても2度目はエラーで引っかかったりしますけども。

[kitteru]

INSERT INTO文はODBCから受け取っております。
UPDATEは複数回実行されているはずですが、INSERTを行っていないので、レコード数が増える原因がわかりません。
INSERT INTO 最新データtmp (日時,担当者,ID) VALUES
(current_timestamp,'太郎',12345),
(current_timestamp,'太郎',12346);
という形でデータを入れても、通常では最新データtmpは１行のままでした。
日時にユニークキーを設定したことがあります。
その際は同一内容で２レコードに増えたうえで、次の 最新データtmp テーブルへのINSERT時にエラーが発生しました。エラーが起こらないようにしたいです。

[sazi]

最新データは1行だけなのですか？

[q_sane_q]

主キーのないテーブルを作って運用したことは無いので未知ですが
確かPostgreSQLのUPDATEって新しいデータの　追記　と古いデータに削除済みフラグ立てる更新の組み合わせでしたよね？
主キーないのがそこでトラブル起こしてるとかでしょうか？

[sazi]

> INSERT INTO 最新データtmp (日時,担当者,ID) VALUES
> (current_timestamp,'太郎',12345),
> , (current_timestamp,'太郎',12346);
> という形でデータを入れても、通常では最新データtmpは１行のままでした。
1行で当然という事みたいですけど、なぜ1行なのですか？
プライマリーで弾かれない限り追加されるはずですけど。

[sazi]

> 最新データは1行だけなのですか？
事象ではなく、仕様の確認です。
updateに条件が無い事から、最新の1行というのが仕様でしょうか？

[q_sane_q]

多分
通常では最新データ は１行のままでした。
の誤記ですよね。

[kitteru]

UPDATEは新しいデータの　追記　と古いデータに削除済みフラグ立てる更新の組み合わせなので、そのあたりで何か起きているのではないかと思うのですが、わかりませんでした。
頻繁にvacuumがおこなわれているので、その際に不要な行を消しそびれたのかもと思ったのですが、わかりませんでした。

最新データを１行だけ という仕様で作っており、最新の１行のみが保存されるようにしております。

申し訳ありません。通常では最新データ は１行のままでした。
の誤記です。

[amura]

最新データtmp テーブルは日に１度TRUNCATEとありますが、NULLリターンで作成されていないのでクリア（VACUUM）すべきは、最新データの方かと。AUTOVACUUMをしているとは思いますが更新中はスキップされます。

[kitteru]

最新データtmp テーブルはRETURN NULLになっており、レコードが無いせいか、ここ数か月間でAUTOVACUUMは実行されたことはありませんでした。
最新データ テーブルは数分に１度AUTOVACUUMが実行されており、これまで数千回実行されていました。
更新中はスキップされるとのことですので、更新の合間に行われていたのだと思います。

[amura]

余談ですがVACUMMは実行されてもremovable可能数が1000個でも、10個または0個の処理となる事が有ります。これはページ８Kの中で動きが無い場合を対象にしている様です。VERBOSEを付けると得られます。最大2個でしょうか？XIDが戻ると古いのが見れますが心臓部なのでさすがに無いでしょうね。

[kitteru]

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.
などと表示されました。

[amura]

上記を見る限り問題はなさそうでうです。横道で申し訳ない。当システムでも更新が激しいものはFREEZEとANARYZEを行ってはいます。UPDATEで増える事はなさそうに思います。テーブル名tmpのtmpの無いインサートが流れる方の可能性が大きいです。駄案ですが「最新データ」名称を変えてみては？

Answer 1

他にTrigerなどでinsertしているところは無いでしょうか？
SQLトレースなどで確認してみてください。

私も経験があるのですが、「無いはず」などの思い込みで、結局は「やらかしてた」という事が殆どです。

insertの発行が無い場合には、Trigerとかトランザクションモードに関するバグかもしれません。

改善するかどうかは分かりませんが、最新データにプライマリの項目を追加して、updateでその項目にwhere条件を指定するようなオマジナイを試してみてはどうでしょうか。

Comments

[kitteru]

回答ありがとうございます。
通常の動作では別にinsertはありませんでした。
ログを残す設定にして、発生時の状況を見てみようと思います。
また、プライマリ項目追加→where条件のおまじないについてもまずは試してみます。

[sazi]

因みに、トランザクション分離レベルは何ですか？

[kitteru]

トランザクション分離レベルについてよくわかっておらず、
初期の状態から変更しておりません。
最新データ テーブルにUPDATEを実行する際のロックモードをみたところ、RowExclusiveLockが表示されました。