중첩 예외 처리 힌트

PostgreSQL에서의 트랜잭션과 오류 처리

Jason Myers

Principal Engineer

중첩 블록으로 세이브포인트 에뮬레이션

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;

세이브포인트 에뮬레이션: 블록 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';

중첩 블록과 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$$;

중첩 블록과 Stacked Diagnostics

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

중첩 블록과 Stacked Diagnostics: 블록 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;

중첩 블록과 Stacked Diagnostics: 블록 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$$;

결과

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

사용자 정의 예외 처리 vs Stacked Diagnostics

사용자 정의

오류 컨텍스트를 명확히 함
예상된 오류 조건
표준 오류 메시지가 너무 일반적임

Stacked Diagnostics

오류에 대한 추가 컨텍스트 필요
가능한 오류 조건이 많음
디버깅
예외 처리 일반화

연습해 봅시다!

PostgreSQL에서의 트랜잭션과 오류 처리