【PostgreSQL】クエリ応答がない時の対処法

2021年4月26日 23:45

SQLの実行結果が返ってこない・・・

クエリを実行してみたところ、コマンドの実行結果が全く返ってこないというような経験はありませんか？

最近案件の性能検証中Javaで1億件以上のレコードをINSERTしようとしたのですが一日かかっても処理が終わらなかったので、途中でプロセスを停止して環境を作り直そうと思い、次の日の朝にTRUNCATE文を実行したのですが応答が返ってこない...

こういう時は「DB側のプロセスが残っていることを疑え！！！」です。

さっきの流れで行きますと、Javaのプロセスは停止してもDBのトランザクションプロセスが残っていたので、テーブルをTRUNCATEしようとしてもロックがかかっていてクエリを実行できなかったのが原因だったようです。

ということで、Postgreでプロセスを終了する方法をメモっておきます。

プロセス確認

ステータスがactiveなPIDが存在することを確認（PIDをメモしておく）

nuko_db=> select datid,datname,pid,usename,state,backend_start,xact_start,query_start,state_change from pg_stat_activity; datid |   datname    |  pid  | usename  | state  |         backend_start         |          xact_start           |          query_start          |         state_change
-------+--------------+-------+----------+--------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
20491 | nuko_db | 28296 | nuko_user  | active | 2021-04-24 00:19:26.489419+09 | 2021-04-24 00:19:26.957188+09 | 2021-04-24 02:07:44.370359+09 | 2021-04-24 02:07:44.370364+09

対象のPIDで実行されているクエリを確認する（キルしたい対象で間違いないかみる）

nuko_db=> select query from pg_stat_activity where pid = '28296';
======================
sqlが表示される
======================

プロセス解除方法

下記コマンドでプロセスを解除できます。

SELECT pg_cancel_backend(プロセスID)
nuko_db=> SELECT pg_cancel_backend('28296');
pg_cancel_backend
-------------------
t
(1 行)

プロセスが削除されていることも確認しておきましょう。

nuko_db=> select datid,datname,pid,usename,state,query,backend_start,xact_start,query_start,state_change from pg_stat_activity where pid = '28296'; datid | datname | pid | usename | state | query | backend_start | xact_start | query_start | state_change
-------+---------+-----+---------+-------+-------+---------------+------------+-------------+--------------
(0 行)

各項目値の意味はリンク先のサイトに詳しく書いてありました！！！
https://gist.github.com/koudaiii/437a22451ff06c5949aba5b0c6e385b3