Tips voor geneste exceptions afhandelen

Transacties en foutafhandeling in PostgreSQL

Jason Myers

Principal Engineer

Savepoints emuleren met geneste blokken

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

Savepoint emulatie blok 2

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

Geneste blokken met stacked diagnostics

DO $$
DECLARE
   exc_message text;
   exc_detail text;
   exc_context text;
BEGIN
    -- Block 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; 
    -- Block 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$$;

Geneste blokken met stacked diagnostics

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

Geneste blokken met stacked diagnostics: blok 2

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

Geneste blokken met stacked diagnostics: blok 2

-- Block 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$$;

Resultaten

INFO:  Bericht: 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

Aangepaste afhandeling vs. stacked diagnostics

Aangepast

Duidelijke foutcontext
Verwachte foutconditie
Standaardfoutmelding te algemeen

Stacked diagnostics

Meer foutcontext nodig
Veel mogelijke foutcondities
Debuggen
Exception handling generaliseren

Laten we oefenen!

Transacties en foutafhandeling in PostgreSQL