錯誤處理與回復原始狀態：EXCEPTION, RAISE

在 PostgreSQL 裡，錯誤可能因為很多原因發生：違反限制（NOT NULL、UNIQUE、CHECK）、語法錯誤、重複值等等。如果你沒攔截或處理這些錯誤，整個外部 transaction 可能會被完全回滾。為了讓商業操作更穩定，正確處理這些錯誤很重要。

PL/pgSQL 提供很強大的 BEGIN ... EXCEPTION ... END 區塊機制，讓你在 function 跟 procedure 裡攔截和處理錯誤。這跟 Python 或 Java 的 try-catch 很像，但在 PostgreSQL 的 transaction context 下有自己的特點。

重點提醒：

每個 BEGIN ... EXCEPTION ... END 區塊都像是「虛擬 savepoint」。如果發生 exception，這個區塊裡的所有變更會自動回滾。這是 唯一正確的方式 在 PL/pgSQL 的 function 跟 procedure 裡做部分回滾。

用 EXCEPTION 處理錯誤的語法

BEGIN
    -- 主要程式碼
EXCEPTION
    WHEN 錯誤類型 THEN
        -- 處理特定錯誤
    WHEN 其他錯誤類型 THEN
        -- 其他處理器
    WHEN OTHERS THEN
        -- 處理所有其他錯誤
END;

在 function/procedure 裡的範例

DO $$
BEGIN
    RAISE NOTICE '現在會發生錯誤...';
    PERFORM 1 / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE '除以零已攔截！';
END;
$$;

範例：帶錯誤處理與回滾的更新

假設有個訂單表：

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    amount NUMERIC NOT NULL,
    status TEXT NOT NULL
);

我們來寫個 function，會更新訂單狀態，如果出錯就什麼都不改：

CREATE OR REPLACE FUNCTION update_order_status(order_id INT, new_status TEXT)
RETURNS VOID AS $$
BEGIN
    BEGIN
        UPDATE orders
        SET status = new_status
        WHERE id = order_id;

        -- 模擬錯誤
        IF new_status = 'FAIL' THEN
            RAISE EXCEPTION '模擬錯誤！';
        END IF;

        RAISE NOTICE '訂單狀態 % 已更新', order_id;

    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE '更新訂單 % 時發生錯誤: %', order_id, SQLERRM;
            -- 這個區塊裡的所有變更會自動回滾！
            -- 如果需要，可以再次丟出錯誤給外部處理
            RAISE;
    END;
END;
$$ LANGUAGE plpgsql;

在有明確 transaction 控制的 procedure 裡怎麼運作

在 procedure（CREATE PROCEDURE）裡可以用 COMMIT、ROLLBACK、SAVEPOINT，但不能用 ROLLBACK TO SAVEPOINT。如果你只想回滾 procedure 裡的一部分操作，還是要用 BEGIN ... EXCEPTION ... END：

CREATE OR REPLACE PROCEDURE pay_order(order_id INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 整個 procedure 可以用 COMMIT/ROLLBACK，但如果只想回滾某個步驟：
    BEGIN
        UPDATE accounts
        SET balance = balance - amount
        WHERE id = (SELECT account_id FROM orders WHERE id = order_id);

        -- 錯誤
        IF amount < 0 THEN
            RAISE EXCEPTION '金額不能是負數！';
        END IF;

        UPDATE orders SET status = 'PAID' WHERE id = order_id;

    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE '處理訂單付款 % 時發生錯誤: %', order_id, SQLERRM;
            -- 這個區塊的變更會自動回滾
    END;

    COMMIT; -- 只有在 procedure 裡才能明確結束 transaction！
END;
$$;

錯誤日誌紀錄

不只要攔截錯誤，還要把錯誤存下來方便之後分析。

CREATE TABLE error_log (
    id SERIAL PRIMARY KEY,
    order_id INT,
    error_message TEXT,
    error_time TIMESTAMP DEFAULT now()
);

在 function 或 procedure 裡：

EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO error_log (order_id, error_message)
        VALUES (order_id, SQLERRM);
        RAISE NOTICE '錯誤已寫入日誌: %', SQLERRM;
        RAISE;

PostgreSQL 17 的重要限制與細節

在 function（CREATE FUNCTION ... ）裡不能用 transaction 控制指令（BEGIN、COMMIT、ROLLBACK、SAVEPOINT、ROLLBACK TO SAVEPOINT、RELEASE SAVEPOINT）！ 所有 function 都會在 外部 transaction 裡一次執行完。

在 procedure（CREATE PROCEDURE ... ）裡可以明確寫 SAVEPOINT、RELEASE SAVEPOINT、COMMIT、ROLLBACK。但：ROLLBACK TO SAVEPOINT — 禁止！（如果你在 PL/pgSQL procedure 裡用 ROLLBACK TO SAVEPOINT，會直接報語法錯誤）

在 function 跟 procedure 裡要回滾「部分程式碼」就只能用 BEGIN ... EXCEPTION ... END 區塊。 發生錯誤時，這個區塊裡的東西會自動回滾，然後繼續執行後面的程式碼。

Procedure（CREATE PROCEDURE） 不能在 function 裡或用 SELECT 執行 — 只能用 CALL ... 單獨呼叫。

PL/pgSQL 裡怎麼實作「部分回滾」？

唯一可行的方式就是用 BEGIN ... EXCEPTION ... END 區塊來處理錯誤。這種區塊會自動建立 savepoint，發生錯誤時只回滾這個區塊，不會影響 procedure/function 其他部分。

EXCEPTION 範例（推薦做法）：

CREATE OR REPLACE PROCEDURE demo_savepoint()
LANGUAGE plpgsql
AS $$
BEGIN
    -- 一些程式碼
    BEGIN
        -- 這裡的錯誤不會讓整個 procedure 回滾，
        -- 只會回滾這個區塊！
        INSERT INTO demo VALUES ('bad data'); -- 可能會出錯
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE '錯誤已處理，這個區塊的變更已取消';
    END;
    -- 這裡會繼續執行！
END;
$$;

範例：批次資料匯入，防止整個流程回滾

CREATE OR REPLACE PROCEDURE load_big_batch()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM import_table LOOP
        BEGIN
            INSERT INTO target_table (col1, col2)
            VALUES (rec.col1, rec.col2);
        EXCEPTION WHEN OTHERS THEN
            INSERT INTO import_errors (err_msg)
            VALUES ('記錄錯誤: ' || rec.col1 || ': ' || SQLERRM);
            -- 這個區塊的變更已取消！
        END;
    END LOOP;
    COMMIT;  -- 只有 procedure 在外部沒明確 transaction 時才可以！
END;
$$;

-- 呼叫 procedure
CALL load_big_batch();

注意：如果你從 client 端呼叫這個 procedure，而 client 已經有開 transaction（像 Python autocommit=False），那在 procedure 裡用 COMMIT 或 SAVEPOINT 會報錯。

巢狀 savepoint 跟 EXCEPTION 的小技巧

1. 不要在 PL/pgSQL 裡用 ROLLBACK TO SAVEPOINT！ 這會直接報語法錯誤。
2. 要做部分回滾就用巢狀的 BEGIN ... EXCEPTION ... END 區塊。
3. 記得 COMMIT 跟 ROLLBACK 在 procedure 裡會重啟 transaction — 只有在 procedure 是 autocommit 模式下才可以用！
4. 錯誤要記錄到獨立的 table，這樣才不會漏掉有問題的資料。
5. 如果商業操作一定要全有全無（all-or-nothing）— 就寫成 function，裡面不要用 COMMIT/ROLLBACK；如果要分階段處理 — 就寫成 procedure。

範例：批次匯入，部分處理

CREATE OR REPLACE PROCEDURE import_batch()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM staging_table LOOP
        BEGIN
            INSERT INTO data_table (data)
            VALUES (rec.data);
        EXCEPTION
            WHEN unique_violation THEN
                INSERT INTO import_log (msg)
                VALUES ('重複: ' || rec.data);
            WHEN OTHERS THEN
                INSERT INTO import_log (msg)
                VALUES ('錯誤: ' || rec.data || ' — ' || SQLERRM);
        END;
    END LOOP;
END;
$$;

PostgreSQL 17 最重要的事：

在 PL/pgSQL procedure 裡允許 SAVEPOINT、RELEASE SAVEPOINT、COMMIT、ROLLBACK，但 ROLLBACK TO SAVEPOINT — 不行。

function 跟 procedure 裡的「部分回滾」只能靠巢狀 BEGIN ... EXCEPTION ... END 區塊來做。

transaction 最好在外部控制（用連線參數跟 autocommit），procedure 裡就用這些錯誤處理機制就好。