Postgres Uncommitted Transactions 處理紀錄

發生了什麼事

發現 drop table 的 query 會直接卡住，無法成功執行。

排查過程

1. 找出未完成的 query

   SELECT pid, usename, application_name, query, state
   FROM pg_stat_activity
   WHERE pid <> pg_backend_pid()
   AND state = 'idle in transaction'
   

   問了一下 chatgpt 關於 idle 與 idle in transaction 的解釋

   idle: This state indicates that the session is currently not executing any query and is waiting for a new client command to arrive.

   idle in transaction: This state indicates that the session is currently in a transaction but is not executing any query. It's waiting for the client to issue a new command or to commit or rollback the transaction.

2. 找出了前一步驟結果中 query 和我想砍掉 table 有關的 pid，然後用下面的 query kill，但仍然沒用。

   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   

3. 有鑒於找不到是哪個 uncommitted 的 transaction 影響到我要做的事，索性全砍了。

   DO $$
   DECLARE
       rec RECORD;
   BEGIN
       FOR rec IN
           SELECT pid
           FROM pg_stat_activity
           WHERE state = 'idle in transaction'
           AND pid <> pg_backend_pid()
       LOOP
           PERFORM pg_terminate_backend(rec.pid);
       END LOOP;
   END $$;
   

4. 再次執行 drop table 就成功了，終於。