CodeGym /課程 /SQL SELF /分析目前的查詢和交易

分析目前的查詢和交易

SQL SELF

等級 55 , 課堂 4

開放

我們先從基礎開始。PostgreSQL 給你一堆超好用的工具來分析 SQL 查詢和交易。像是內建的 current_query() 跟 txid_current() 這些 function，可以讓你：

拿到目前正在執行的 SQL 查詢。
知道這個查詢是在什麼交易裡跑的。
記錄 SQL 操作，之後可以分析。
追蹤交易的問題，像是你的 code 以為會發生某件事，結果卻完全不是這樣。

這些都超有用，尤其是當一般 debug 輸出沒幫上忙，或你想要「事後」分析查詢行為的時候。

內建 function 總覽

Function `current_query()`

current_query() 會回傳目前這個連線正在執行的 SQL 查詢文字。你可能會問：「它怎麼知道？」PostgreSQL 其實很會追蹤每個連線的狀態，這個 function 就是讓你偷看「幕後」發生什麼事。

語法：

SELECT current_query();

執行範例：

-- 在 function 裡執行查詢
DO $$
BEGIN
    RAISE NOTICE '目前查詢：%', current_query();
END;
$$;

-- 結果：
-- NOTICE: 目前查詢：DO $$ BEGIN RAISE NOTICE '目前查詢：%', current_query(); END; $$;

從這個例子可以看到，current_query() 會告訴你現在執行的查詢內容。這資訊對分析複雜的 procedure 超有幫助：你可以知道此刻到底在跑什麼！

Function `txid_current()`

說到交易，txid_current() 這個 function 就超級好用。它會回傳目前交易的唯一識別碼。這在你想追蹤同一個交易裡的操作順序時特別有用。

語法：

SELECT txid_current();

執行範例：

BEGIN;

-- 取得目前交易的 ID
SELECT txid_current();

-- 輸出：
-- 564（舉例來說，這是識別碼）

-- 結束交易
COMMIT;

這些交易 ID 可以拿來對應 log、分析操作順序，甚至 debug 多人系統的問題。

實戰應用範例

執行過程中記錄目前查詢。

有時候一個 procedure 或 function 裡面有一堆 SQL 查詢。要搞清楚哪裡出錯，可以直接記錄目前的 SQL 查詢。像這樣：

DO $$
DECLARE
    current_txn_id BIGINT;
BEGIN
    current_txn_id := txid_current();
    RAISE NOTICE '目前交易 ID：%', current_txn_id;

    RAISE NOTICE '目前查詢：%', current_query();

    -- 這裡可以加你自己的操作
END;
$$;

這段 code 會在 console 印出交易 ID 跟目前查詢的內容。這樣你就能精確知道現在在跑什麼。

分析交易找出問題。

想像一下，有人抱怨批次更新時資料會不見。你寫了幾個 procedure，每個都在同一個交易裡跑。要怎麼知道是哪個出包？來看個例子：

BEGIN;

-- 加入交易記錄
DO $$
BEGIN
    RAISE NOTICE '目前交易 ID：%', txid_current();
END;
$$;

-- 執行「有問題」的 SQL 查詢
UPDATE orders
SET status = 'processed'
WHERE id IN (SELECT order_id FROM pending_orders);

COMMIT;

如果更新沒成功，你馬上就能看到是哪個交易 ID 造成的。這不只方便找 bug，也能幫你判斷有沒有交易衝突。

記錄查詢做歷史分析。

有時候你不只要解決當下的問題，還想記下曾經執行過哪些 SQL 查詢。你可以建一個 log table：

CREATE TABLE query_log (
    log_time TIMESTAMP DEFAULT NOW(),
    query_text TEXT,
    txn_id BIGINT
);

這樣就能用 current_query() 跟 txid_current() 來記錄查詢：

DO $$
BEGIN
    INSERT INTO query_log (query_text, txn_id)
    VALUES (current_query(), txid_current());
END;
$$;

現在 query_log 這個 table 就會存下每個查詢和它所屬的交易。這對分析資料庫運作超有幫助。

實用情境範例

範例 1：交易稽核

假設你在分析多人系統的操作。記錄交易 ID（txid_current）可以讓你把同一個交易的動作分組。

DO $$
DECLARE
    txn_id BIGINT;
BEGIN
    txn_id := txid_current();
    RAISE NOTICE '交易開始，ID：%', txn_id;

    -- 某個操作
    UPDATE users SET last_login = NOW() WHERE id = 123;

    RAISE NOTICE '目前查詢：%', current_query();
END;
$$;

範例 2：簡化 procedure 除錯

你呼叫了一個很複雜的 procedure，結果出錯。你可以在 function 的不同階段插入 current_query() 的 log，這樣就能看到每一步執行了什麼查詢：

CREATE OR REPLACE FUNCTION debugged_function() RETURNS VOID AS $$
BEGIN
    RAISE NOTICE '更新前的查詢：%', current_query();
    UPDATE data_table SET field = 'debugging';
    RAISE NOTICE '更新後的查詢：%', current_query();
END;
$$ LANGUAGE plpgsql;

等 function 執行完，你會收到兩個通知，分別顯示對應的 SQL 查詢。