Откаты, точки сохранения и исключения

Транзакции и обработка ошибок в 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