回復、儲存點與例外狀況

PostgreSQL 的交易與錯誤處理

Jason Myers

Principal Engineer

自動回復

DO $$
BEGIN
    UPDATE cookies SET deliciousness = 11 where name = 'Cats Tongue';
    UPDATE cookies SET deliciousness = 12 where name = 'Gingerbread';
EXCEPTION
WHEN others THEN
   INSERT INTO errors (msg) VALUES ('Deliciousness only goes to 11!');
   RAISE INFO 'Deliciousness only goes to 11!';
END; 
$$ language 'plpgsql';

¹ https://www.postgresql.org/docs/current/plpgsql-transactions.html

DO $$
BEGIN
    -- Block 1
    BEGIN
        UPDATE inventory SET cost = 2.33 WHERE name = 'Linga';
        UPDATE inventory SET cost = 2.33 WHERE name = 'Petit-Beurre';
        UPDATE inventory SET cost = 2.33 WHERE name = 'Rosette';
    EXCEPTION
    WHEN others THEN
       INSERT INTO errors (msg) VALUES ('Max cost is 10!');
       RAISE INFO 'Max cost is 10!';
    END; 
    -- Block 2
    BEGIN
        UPDATE inventory SET cost = 35.0 WHERE name = 'Macaron';
        UPDATE inventory SET cost = 3.50 WHERE name = 'Panellets';
    EXCEPTION
    WHEN others THEN
       INSERT INTO errors (msg) VALUES ('Max cost is 10!');
       RAISE INFO 'Max cost is 10!';
    END; 
END;
$$ language 'plpgsql';

模擬儲存點

DO $$
BEGIN
    -- Block 1
    BEGIN
        UPDATE inventory SET cost = 2.33 WHERE name = 'Linga';
        UPDATE inventory SET cost = 2.33 WHERE name = 'Petit-Beurre';
        UPDATE inventory SET cost = 2.33 WHERE name = 'Rosette';
    EXCEPTION
    WHEN others THEN
       INSERT INTO errors (msg) VALUES ('Max cost is 10!');
       RAISE INFO 'Max cost is 10!';
    END;

延伸：模擬儲存點

    -- Block 2
    BEGIN
        UPDATE inventory SET cost = 35.0 WHERE name = 'Macaron';
        UPDATE inventory SET cost = 3.50 WHERE name = 'Panellets';
    EXCEPTION
    WHEN others THEN
       INSERT INTO errors (msg) VALUES ('Max cost is 10!');
       RAISE INFO 'Max cost is 10!';
    END; 
END;
$$ language 'plpgsql';

快速補充

外部資料集
變數
欄位代入使用錯誤

一起來練習吧！

PostgreSQL 的交易與錯誤處理