Kiat menangani pengecualian bertingkat

Transaksi dan Penanganan Error di PostgreSQL

Jason Myers

Principal Engineer

Mensimulasikan savepoint dengan blok bertingkat

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;

Simulasi savepoint 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';

Blok bertingkat dengan 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$$;

Blok bertingkat dengan stacked diagnostics

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

Blok bertingkat dengan 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;

Blok bertingkat dengan 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$$;

Hasil

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

Penanganan pengecualian kustom vs stacked diagnostics

Kustom

Jelaskan konteks error
Kondisi error yang diharapkan
Pesan error standar terlalu umum

Stacked Diagnostics

Perlu konteks error lebih lengkap
Banyak kemungkinan kondisi error
Debugging
Generalisasi penanganan pengecualian

Ayo berlatih!

Transaksi dan Penanganan Error di PostgreSQL