CodeGym /課程 /SQL SELF /PL/pgSQL 除錯與優化常見錯誤解析

PL/pgSQL 除錯與優化常見錯誤解析

SQL SELF

等級 56 , 課堂 4

開放

今天，為了結束這趟 PL/pgSQL 史詩級之旅，我們來聊聊你在除錯和優化 function、procedure 時最容易遇到的那些坑。知道這些錯誤，不只可以讓你未來少踩雷，還能讓你 debug 時更有效率。

除錯與優化時的常見錯誤

1. 變數用錯

寫 PL/pgSQL function 最常見的錯誤之一，就是變數宣告或使用不對。比如你忘了明確指定變數型別，或是搞混了參數傳進來的值。來看個實際例子：

CREATE OR REPLACE FUNCTION calculate_discount(order_total NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    discount_rate NUMERIC;
BEGIN
    -- 哎呀！忘記初始化 discount_rate 變數
    RETURN order_total * discount_rate;
END;
$$ LANGUAGE plpgsql;

呼叫這個 function 時，你會遇到計算時用到 NULL 的錯誤，因為 discount_rate 一開始沒給值。

怎麼避免：

宣告變數時就給預設值：

   DECLARE
       discount_rate NUMERIC := 0.1; -- 預設值

用 RAISE NOTICE 檢查變數內容，確保它們是你預期的：

RAISE NOTICE 'discount_rate 的值: %', discount_rate;

2. 沒有 log 錯誤

另一個常見問題，就是沒加 log。出錯時你沒 log function 的執行情況，就像在黑暗房間裡找黑貓，還不知道房間裡有沒有貓。

這是沒加 log 的 function 範例：

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    -- 一些複雜的訂單處理邏輯
    UPDATE orders SET status = 'processed' WHERE id = order_id;
END;
$$ LANGUAGE plpgsql;

如果 order_id 傳錯了？或 orders 表根本沒這筆資料？

怎麼避免：加上 RAISE NOTICE 或 RAISE EXCEPTION，log 關鍵步驟：

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    -- log 輸入資料
    RAISE NOTICE '正在處理訂單 ID %', order_id;

    -- 複雜處理邏輯
    UPDATE orders SET status = 'processed' WHERE id = order_id;

    -- log 結果
    RAISE NOTICE '訂單狀態已更新，ID %', order_id;
END;
$$ LANGUAGE plpgsql;

這樣你就能很快追蹤錯誤發生在哪。

3. 忽略查詢效能

這是所有 DB 開發者的頭號敵人。你寫的 function 看起來沒問題，但執行起來超慢。通常慢的原因就是沒 index 或查詢計劃很爛。

慢查詢範例：

CREATE OR REPLACE FUNCTION get_large_orders()
RETURNS TABLE(order_id INT, total NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT id, total FROM orders WHERE total > 1000;
END;
$$ LANGUAGE plpgsql;

如果 orders 表的 total 欄沒 index，查詢會掃整張表，超沒效率。

怎麼避免：

用 EXPLAIN ANALYZE 看查詢效能：

EXPLAIN ANALYZE SELECT id, total FROM orders WHERE total > 1000;

常用欄位記得加 index：

CREATE INDEX idx_orders_total ON orders(total);

4. 用錯 transaction 隔離等級

執行複雜 procedure 時，有時會因為搞不清楚 transaction 隔離等級出錯。比如兩個 transaction 同時要更新同一筆資料，就可能 deadlock。

可能 deadlock 的例子：

BEGIN;
UPDATE orders SET status = 'processed' WHERE id = 1;

-- 等另一個 transaction 解鎖
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
COMMIT;

如果另一個 transaction 反過來操作，就會互鎖。

怎麼避免：

想好操作順序，大家都照同一個順序來。
必要時用 SERIALIZABLE 隔離等級。

5. 沒有錯誤處理

錯誤處理不只是好習慣，也是讓你 code 穩定的關鍵。下面這段 code 就沒處理可能的錯誤：

CREATE OR REPLACE FUNCTION add_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders (id, status) VALUES (order_id, 'new');
END;
$$ LANGUAGE plpgsql;

如果 order_id 已經存在，你會遇到 duplicate key value violates unique constraint 的錯誤。

怎麼避免：用 exception 處理區塊：

CREATE OR REPLACE FUNCTION add_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders (id, status) VALUES (order_id, 'new');
EXCEPTION WHEN unique_violation THEN
    RAISE NOTICE 'ID % 的訂單已經存在啦！', order_id;
END;
$$ LANGUAGE plpgsql;

錯誤範例與修正

錯誤 1：查詢沒加 index，超慢

情境：你有個查詢會用某欄位過濾，但那欄沒 index。

修正：幫那個欄位加個 index。

錯誤 2：function 邏輯太亂，難 debug

情境： function 裡面塞太多邏輯，沒拆成小 function。

修正：把複雜 function 拆成小 function，這樣比較好讀也好 debug。

錯誤 3：RAISE EXCEPTION 用錯

情境：你所有錯誤都用 RAISE EXCEPTION，連小事也一樣。

修正：資訊用 RAISE NOTICE，只有嚴重錯誤才用 RAISE EXCEPTION。

RAISE NOTICE '一切 ok — function 這階段結束。';
RAISE EXCEPTION '出事啦！請檢查輸入參數。';

避免錯誤的建議

加 log： function 關鍵步驟用 RAISE NOTICE 追蹤執行狀況。
多測試 function：常用測試資料檢查 function 跟 procedure。
保持 code 好讀：複雜 function 拆成小 function 跟 procedure。
分析效能：用 EXPLAIN ANALYZE 確認查詢效率。
隨時準備應對意外：一定要加 exception 處理區塊。

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION '發生未知錯誤: %', SQLERRM;