함수·프로시저·트리거

src/content/documents/data-analysis/database-procedure-function-trigger.json

한 줄 정의

함수는 값을 계산해 SQL 식이나 SELECT에서 사용하고, 프로시저는 CALL로 작업을 수행하며, 트리거는 특정 테이블 사건에 반응해 자동 실행된다.

세 기능 모두 데이터베이스 안에서 코드를 실행하지만 서로 대체품은 아니다. 게임 점검 보상을 예로 들면 “보상량 계산”은 함수, “지급 작업 진입점”은 프로시저, “골드 변경 감사”는 트리거가 자연스럽다. 하지만 전체 지급 정책과 외부 메시지 전송까지 DB에 숨기면 운영과 테스트가 어려워진다.

세 기능의 차이

구분

호출·반환

트랜잭션·주요 용도

FUNCTION

SELECT 또는 식에서 호출, 타입·행 집합 반환

호출한 트랜잭션 안에서 실행, 계산·재사용 질의

PROCEDURE

CALL, OUT·INOUT 매개변수 가능

작업 명령; 조건을 만족할 때 내부 COMMIT·ROLLBACK 가능

TRIGGER

INSERT·UPDATE·DELETE 등 사건이 자동 호출

원래 명령과 같은 트랜잭션, 불변식·감사

PostgreSQL 프로시저의 트랜잭션 제어에는 호출 문맥 제약이 있다. 예를 들어 명시적 트랜잭션 블록 안에서 CALL한 프로시저는 트랜잭션 제어문을 실행할 수 없다. 또한 SECURITY DEFINER 프로시저와 SET 절이 붙은 프로시저에도 제한이 있다. “프로시저니까 언제나 COMMIT 가능”으로 기억하면 안 된다.

실습 스키마: 중복 없는 보상과 감사 로그

request_key는 점검 보상 캠페인과 플레이어를 결합한 요청 식별자다. 같은 네트워크 요청이 재시도돼도 UNIQUE 제약이 두 번째 원장 행을 막는다. 보상 원장은 지급 여부의 근거이고, 감사 로그는 골드가 실제로 어떻게 바뀌었는지 기록한다.

DROP TABLE IF EXISTS player_gold_audit, reward_grants, players CASCADE;
DROP FUNCTION IF EXISTS audit_player_gold();
DROP FUNCTION IF EXISTS calculate_reward(integer, integer);
DROP PROCEDURE IF EXISTS grant_reward(text, bigint, integer, integer);

CREATE TABLE players (
  player_id bigint PRIMARY KEY,
  nickname text NOT NULL UNIQUE,
  gold bigint NOT NULL DEFAULT 0 CHECK (gold >= 0)
);
CREATE TABLE reward_grants (
  grant_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  request_key text NOT NULL UNIQUE,
  player_id bigint NOT NULL REFERENCES players(player_id),
  amount bigint NOT NULL CHECK (amount > 0),
  granted_at timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE TABLE player_gold_audit (
  audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  player_id bigint NOT NULL,
  old_gold bigint NOT NULL,
  new_gold bigint NOT NULL,
  changed_at timestamptz NOT NULL DEFAULT clock_timestamp(),
  db_user text NOT NULL DEFAULT current_user
);
INSERT INTO players VALUES (1, '루나', 1000);

FUNCTION: 보상량을 계산해 반환한다

점검 시간당 100골드에 VIP 등급당 10%를 더한다고 하자. 입력만으로 결과가 정해지는 작은 계산은 함수로 분리하면 SELECT, 테스트, 프로시저에서 재사용할 수 있다.

reward(h,v)=⌊100h(1+0.1v)⌋reward(h,v)=\left\lfloor100h(1+0.1v)\right\rfloor
CREATE OR REPLACE FUNCTION calculate_reward(
  maintenance_hours integer, vip_level integer
) RETURNS bigint
LANGUAGE sql
IMMUTABLE
STRICT
RETURN floor(100 * maintenance_hours * (1 + 0.1 * vip_level))::bigint;

SELECT calculate_reward(3, 2) AS reward_gold;
 reward_gold
-------------
         360

IMMUTABLE은 같은 인수에 같은 결과를 반환하며 DB 상태를 보지 않는다는 약속이다. 잘못 표시하면 옵티마이저가 부정확하게 최적화할 수 있다. 현재 시각이나 테이블을 읽는 함수를 IMMUTABLE로 선언하면 안 된다.

TRIGGER: 모든 골드 변경을 같은 트랜잭션에서 감사한다

트리거 함수는 trigger 타입을 반환하고 NEW·OLD 행을 사용한다. AFTER UPDATE OF gold 트리거는 어떤 애플리케이션 경로가 골드를 바꾸더라도 감사 행을 남긴다. 원래 UPDATE가 롤백되면 감사 INSERT도 함께 롤백된다.

CREATE OR REPLACE FUNCTION audit_player_gold()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.gold IS DISTINCT FROM OLD.gold THEN
    INSERT INTO player_gold_audit (player_id, old_gold, new_gold)
    VALUES (NEW.player_id, OLD.gold, NEW.gold);
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER players_gold_audit
AFTER UPDATE OF gold ON players
FOR EACH ROW
EXECUTE FUNCTION audit_player_gold();

PROCEDURE: 원장 기록과 잔액 변경을 하나로 묶는다

프로시저는 원장 INSERT가 실제로 성공한 경우에만 플레이어 골드를 증가시킨다. ON CONFLICT DO NOTHING 뒤 ROW_COUNT가 0이면 이미 처리한 요청이므로 아무 일도 하지 않는다. 두 명령은 CALL을 둘러싼 같은 트랜잭션에서 모두 성공하거나 모두 취소된다.

CREATE OR REPLACE PROCEDURE grant_reward(
  p_request_key text,
  p_player_id bigint,
  p_maintenance_hours integer,
  p_vip_level integer
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_amount bigint;
  v_inserted integer;
BEGIN
  IF p_maintenance_hours <= 0 OR p_vip_level < 0 THEN
    RAISE EXCEPTION 'invalid reward inputs';
  END IF;

  v_amount := calculate_reward(p_maintenance_hours, p_vip_level);

  INSERT INTO reward_grants (request_key, player_id, amount)
  VALUES (p_request_key, p_player_id, v_amount)
  ON CONFLICT (request_key) DO NOTHING;
  GET DIAGNOSTICS v_inserted = ROW_COUNT;

  IF v_inserted = 1 THEN
    UPDATE players SET gold = gold + v_amount
    WHERE player_id = p_player_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'player % not found', p_player_id;
    END IF;
  END IF;
END;
$$;

이 프로시저는 내부 COMMIT을 일부러 하지 않는다. 호출자가 다른 게임 상태 변경과 함께 하나의 트랜잭션 경계를 정할 수 있게 한다. 장시간 배치에서 구간별 COMMIT이 꼭 필요하면 별도 프로시저로 설계하고 재시작 지점·부분 성공 의미를 명시한다.

중복 호출로 멱등성을 검증한다

멱등성은 같은 요청을 여러 번 적용해도 한 번 적용한 상태와 같다는 성질이다.

f(f(S,k),k)=f(S,k)f(f(S,k),k)=f(S,k)

여기서 S는 DB 상태, k는 request_key다. UNIQUE 제약이 동시 요청까지 직렬화해 애플리케이션의 “먼저 SELECT하고 없으면 INSERT” 경쟁 조건을 피한다.

CALL grant_reward('maintenance-20260811:p1', 1, 3, 2);
CALL grant_reward('maintenance-20260811:p1', 1, 3, 2);

SELECT player_id, gold FROM players WHERE player_id = 1;
SELECT request_key, amount FROM reward_grants;
SELECT old_gold, new_gold FROM player_gold_audit ORDER BY audit_id;
 player_id | gold
-----------+-----
         1 | 1360

 request_key                 | amount
-----------------------------+-------
 maintenance-20260811:p1     |   360

 old_gold | new_gold
----------+---------
     1000 |     1360

두 번째 CALL은 원장·골드·감사 행을 추가하지 않는다.

원자성과 오류 처리

지급의 성공 조건은 원장과 잔액이 함께 반영되는 것이다. 트리거 감사 행도 같은 트랜잭션에 포함된다.

Commit  ⟺  GrantLedger∧GoldUpdate∧AuditLogCommit\iff GrantLedger\land GoldUpdate\land AuditLog

존재하지 않는 player_id로 CALL하면 원장 INSERT의 외래 키 또는 명시적 예외가 발생하고 전체 문장이 롤백된다. 오류를 잡아 무시하면서 부분 상태를 남기지 않는다. 클라이언트가 응답을 못 받았더라도 같은 request_key로 재시도하면 결과를 안전하게 확인할 수 있다.

보상 로직을 앱과 DB 어디에 둘까

DB에 두기 좋은 것

앱에 두기 좋은 것

제약·중복 방지·원자적 데이터 변경

캠페인 대상 선정·비즈니스 정책 조합

모든 쓰기 경로에 필요한 짧은 감사

메일·푸시·메시지 브로커 같은 외부 I/O

데이터 가까이에서 집합 단위로 가능한 변경

재시도·관찰·속도 제한·배포가 복잡한 흐름

실무에서는 앱이 대상과 request_key를 정하고 DB가 유일성과 원자적 반영을 강제하는 혼합 방식이 흔하다. 외부 알림은 DB 트랜잭션 안에서 직접 보내지 말고 outbox 행을 함께 커밋한 뒤 별도 작업자가 전송하면 DB 롤백과 외부 발송의 불일치를 줄일 수 있다.

트리거를 과도하게 쓰면 생기는 문제

  • SQL 한 줄이 보이지 않는 연쇄 쓰기를 일으켜 원인 추적과 성능 예측이 어려워진다.

  • 행 단위 트리거에서 큰 쿼리나 외부 호출을 하면 대량 UPDATE의 각 행마다 반복된다.

  • 여러 트리거의 실행 순서와 재귀 갱신이 얽히면 교착 상태와 무한 순환 위험이 커진다.

  • 업무 정책이 앱 코드와 트리거에 중복되면 어느 쪽이 원본인지 불명확해진다.

트리거는 작고 결정적이며 빠르게 유지하고, 이름·문서·테스트로 자동 동작을 드러낸다. 감사 로그처럼 모든 쓰기 경로에서 동일하게 필요한 횡단 규칙은 적합하지만, 시즌별 보상 정책 전체를 수십 개 트리거로 분산시키는 것은 피한다.

참고 자료

댓글 0

댓글을 불러오는 중…