여러 단계가 있는 프로시저 만들기: 데이터 검증, 삽입, 그리고 로깅

실제 비즈니스 상황에서는 단일 작업만 하는 게 아니라 여러 동작을 체인처럼 연결해서 처리해야 해. 예를 들어 주문이 들어오면 — 고객 데이터 검증, 주문 저장, 그리고 감사용 로그 남기기 같은 거지. 여러 단계로 된 프로시저는 이런 단계를 하나의 로직으로 묶어서 트랜잭션으로 무결성을 보장해줘: 어느 한 단계라도 실패하면 전체가 롤백돼.

PostgreSQL의 새로운 버전, 특히 별도의 프로시저(CREATE PROCEDURE)와 트랜잭션 확장 기능이 나오면서, PL/pgSQL의 함수와 프로시저의 차이, 그리고 세이브포인트(SAVEPOINT), 롤백, 에러 블록을 제대로 다루는 게 중요해졌어.

여러 단계 프로시저의 기본 구조

일반적인 비즈니스 프로시저는 다음과 같은 단계로 구성돼:

1. 데이터 검증 — 입력 인자, 고객/상품 존재 여부 등 확인
2. 데이터 삽입 — 실제로 레코드(들)를 추가(또는 갱신)
3. 로깅 또는 감사 — 성공/실패에 대한 정보 기록

각 단계는 하나의 트랜잭션(원자적으로) 안에서 처리할 수도 있고, 프로세스가 "길거나" 부분 에러 처리가 필요하면 세이브포인트(SAVEPOINT)를 만들고, 예외 처리 블록으로 로컬 롤백을 할 수도 있어.

예시: 무결성 체크와 함께 주문 추가하기

다음과 같은 상황을 보자 — 세 개의 테이블이 있어:

• customers — 고객
• orders — 주문
• order_log — 주문 로그

스키마를 준비해보자:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP NOT NULL DEFAULT NOW(),
    amount NUMERIC(10,2) NOT NULL
);

CREATE TABLE order_log (
    log_id SERIAL PRIMARY KEY,
    order_id INT,
    log_message TEXT NOT NULL,
    log_date TIMESTAMP NOT NULL DEFAULT NOW()
);

여러 단계 프로시저 만들기: 함수 or 프로시저?

중요!

• 트랜잭션을 완전히 제어해야 한다면(세이브포인트, 명시적 COMMIT/ROLLBACK 등) — CREATE PROCEDURE를 써.
• 프로시저가 논리적으로 원자적이고("모두 아니면 아무것도") 다른 SQL 쿼리에서 호출된다면 — 함수를 써.

함수 버전(원자적 로직):

CREATE OR REPLACE FUNCTION add_order(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- 1. 고객 검증
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'ID가 %인 고객이 존재하지 않아', p_customer_id;
    END IF;

    -- 2. 주문 삽입
    INSERT INTO orders (customer_id, amount)
    VALUES (p_customer_id, p_amount)
    RETURNING order_id INTO v_order_id;

    -- 3. 로깅
    INSERT INTO order_log (order_id, log_message)
    VALUES (v_order_id, '주문이 성공적으로 생성됨.');

    RAISE NOTICE '주문 %이(가) 고객 %에게 성공적으로 추가됨', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

특징: PostgreSQL의 함수는 항상 하나의 외부 트랜잭션 안에서 실행돼. 함수 안에서는 트랜잭션 제어(COMMIT, ROLLBACK, SAVEPOINT)를 쓸 수 없어. 롤백이나 커밋은 바깥에서 일어나.

에러 처리와 에러 로깅이 추가된 버전:

CREATE OR REPLACE FUNCTION add_order_with_error_logging(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    BEGIN
        -- 고객 검증
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'ID가 %인 고객이 존재하지 않아', p_customer_id;
        END IF;

        -- 주문 삽입
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;

        -- 로깅
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, '주문이 성공적으로 생성됨.');

        RAISE NOTICE '주문 %이(가) 고객 %에게 성공적으로 추가됨', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('에러: %', SQLERRM));
            RAISE; -- 함수 트랜잭션 전체 롤백
    END;
END;
$$ LANGUAGE plpgsql;

BEGIN ... EXCEPTION ... END 블록: PL/pgSQL에서 함수나 프로시저 안에서 이 블록은 가상 세이브포인트를 만들어. 블록 안에서 에러가 나면 그 블록의 변경사항만 롤백돼.

부분 커밋과 단계별 처리: 프로시저가 필요한 이유

단계별 커밋(진짜 부분 커밋)이 필요하다면 — 프로시저를 써!

PostgreSQL 11 버전부터는 별도의 프로시저(CREATE PROCEDURE)를 쓸 수 있고, 서버 쪽에서 트랜잭션과 세이브포인트를 직접 제어할 수 있어. 오직 프로시저에서만(함수에서는 안 됨!) COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT를 명시적으로 쓸 수 있어. 하지만: PL/pgSQL 프로시저에서 ROLLBACK TO SAVEPOINT는 금지야 — 예외 처리기를 써야 해.

단계별 처리와 에러 처리가 있는 프로시저 예시

CREATE OR REPLACE PROCEDURE add_order_step_by_step(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- 첫 번째 블록: 고객 검증
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'ID가 %인 고객이 존재하지 않아', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('에러(검증): %', SQLERRM));
            RETURN;
    END;

    -- 두 번째 블록: 주문 삽입
    BEGIN
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('에러(주문): %', SQLERRM));
            RETURN;
    END;

    -- 세 번째 블록: 성공 로깅
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, '주문이 성공적으로 생성됨.');
    EXCEPTION
        WHEN OTHERS THEN
            -- 여기서는 로깅이 안 돼도 상관없어
            RAISE NOTICE '주문 %에 대한 로그 기록 실패', v_order_id;
    END;

    RAISE NOTICE '주문 %이(가) 고객 %에게 성공적으로 추가됨 (프로시저)', v_order_id, p_customer_id;
END;
$$;

프로시저 호출:

CALL add_order_step_by_step(1, 150.50);

트랜잭션과 프로시저 다룰 때 좋은 팁

• 원자적 비즈니스 작업에는 함수를 써 — "모두 아니면 아무것도"가 필요할 때.
• 단계별 커밋이나 단계별 롤백이 필요하면 프로시저를 써 그리고 명시적 트랜잭션 없이(autocommit 모드) 호출해.
• "부분 롤백"에는 BEGIN ... EXCEPTION ... END 블록을 써 — 이 블록 안에서 PL/pgSQL이 세이브포인트를 만들어주고, 에러 나면 그 블록만 롤백돼.
• 에러는 꼭 로깅해 — 뭐가 안 들어갔는지, 왜 안 됐는지 알 수 있는 최고의 방법이야.
• PL/pgSQL 프로시저에서 ROLLBACK TO SAVEPOINT 쓰지 마 — PostgreSQL 17+에서는 문법 에러 나니까.

테스트: 성공/실패 시나리오

-- 고객 추가
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

-- 함수 호출(성공해야 함)
SELECT add_order(1, 300.00);

-- 없는 고객으로 함수 호출(에러 발생)
SELECT add_order(999, 100.00);

-- 로그 테이블 확인
SELECT * FROM order_log;