回滚、保存点与异常

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
    -- 块 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; 
    -- 块 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
    -- 块 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;

继续：模拟保存点

    -- 块 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';

补充说明

外部数据集
变量
字段替换用法不当

Passons à la pratique !

PostgreSQL 中的事务与错误处理