Dicas para lidar com exceções aninhadas

Transações e Tratamento de Erros no PostgreSQL

Jason Myers

Principal Engineer

Emulando savepoints com blocos aninhados

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

Emulando savepoint — Bloco 2

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

Blocos aninhados com stacked diagnostics

DO $$
DECLARE
   exc_message text;
   exc_detail text;
   exc_context text;
BEGIN
    -- Bloco 1
    BEGIN
        UPDATE inventory SET cost = 2.33 WHERE name = 'Linga';
        UPDATE inventory SET cost = 2.33 WHERE name = 'Petit-Beurre';
    EXCEPTION
    WHEN others THEN
        GET STACKED DIAGNOSTICS exc_message = MESSAGE_TEXT,
                                exc_detail = PG_EXCEPTION_DETAIL,
                                exc_context = PG_EXCEPTION_CONTEXT;
       INSERT INTO errors (msg,detail, context) 
           VALUES (exc_message, exc_detail, exc_context);
    END; 
    -- Bloco 2
    BEGIN
        UPDATE inventory SET cost = 35.0 WHERE name = 'Macaron';
    EXCEPTION
    WHEN others THEN
       GET STACKED DIAGNOSTICS exc_message = MESSAGE_TEXT,
                               exc_detail = PG_EXCEPTION_DETAIL,
                               exc_context = PG_EXCEPTION_CONTEXT;
       INSERT INTO errors (msg,detail, context) 
           VALUES (exc_message, exc_detail, exc_context);
    END; 
END$$;

Blocos aninhados com stacked diagnostics

DO $$
DECLARE
   exc_message text;
   exc_detail text;
   exc_context text;
BEGIN

Blocos aninhados com stacked diagnostics: bloco 2

    -- Bloco 1
    BEGIN
        UPDATE inventory SET cost = 2.33 WHERE name = 'Linga';
        UPDATE inventory SET cost = 2.33 WHERE name = 'Petit-Beurre';
    EXCEPTION
    WHEN others THEN
        GET STACKED DIAGNOSTICS exc_message = MESSAGE_TEXT,
                                exc_detail = PG_EXCEPTION_DETAIL,
                                exc_context = PG_EXCEPTION_CONTEXT;
       INSERT INTO errors (msg,detail, context) 
           VALUES (exc_message, exc_detail, exc_context);
    END;

Blocos aninhados com stacked diagnostics: bloco 2

-- Bloco 2
    BEGIN
        UPDATE inventory SET cost = 35.0 WHERE name = 'Macaron';
    EXCEPTION
    WHEN others THEN
       GET STACKED DIAGNOSTICS exc_message = MESSAGE_TEXT,
                               exc_detail = PG_EXCEPTION_DETAIL,
                               exc_context = PG_EXCEPTION_CONTEXT;
       INSERT INTO errors (msg,detail, context) 
           VALUES (exc_message, exc_detail, exc_context);
    END; 
END$$;

Resultados

INFO:  Message: new row for relation "inventory" violates check constraint 
"cost_check" | Details: Failing row contains (7, 35, Macaron). | Context SQL 
statement "UPDATE inventory SET cost = 35.0 WHERE name = 'Macaron'"
PL/pgSQL function inline_code_block line 23 at SQL statement
DO
postgres=# \x on
Expanded display is on.
postgres=# select * from errors;
-[ RECORD 1 ]---------------------------------------------------------------------
error_id | 15
state    |
msg      | new row for relation "inventory" violates check constraint "cost_check"
detail   | Failing row contains (7, 35, Macaron).
context  | SQL statement "UPDATE inventory SET cost = 35.0 WHERE name = 'Macaron'"+
         | PL/pgSQL function inline_code_block line 23 at SQL statement

Tratamento de exceções personalizado vs stacked diagnostics

Personalizado

Limpar o contexto do erro
Condição de erro esperada
Mensagem padrão muito genérica

Stacked Diagnostics

Precisar obter mais contexto do erro
Muitas possíveis condições de erro
Depuração
Generalizar o tratamento de exceções

Vamos praticar!

Transações e Tratamento de Erros no PostgreSQL