Annulations, points de reprise et exceptions

Transactions et gestion des erreurs dans PostgreSQL

Jason Myers

Principal Engineer

Annulation automatique

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

Simuler des points de reprise

DO $$
BEGIN
    -- Bloc 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;

Suite : simulation de point de reprise

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

Petite parenthèse

ensembles de données externes
variables
substitution de champ incorrecte

Passons à la pratique !

Transactions et gestion des erreurs dans PostgreSQL