

## 發生了什麼事

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


## 排查過程

1. 找出未完成的 query

    ```sql
    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，但仍然沒用。
    ```sql
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    ```

3. 有鑒於找不到是哪個 uncommitted 的 transaction 影響到我要做的事，索性全砍了。
    ```sql
    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 就成功了，終於。



Postgres Uncommitted Transactions 處理紀錄

發生了什麼事

排查過程