PostgreSQL 2F003 오류 원인과 해결 방법 완벽 가이드

2F003 prohibited sql statement attempted 는?

PostgreSQL 에러 코드 2F003: prohibited sql statement attempted는 PL/pgSQL 또는 다른 절차적 언어(PL/Python, PL/Perl 등)로 작성된 함수 내부에서, 해당 함수의 변동성(Volatility) 속성이나 보안 컨텍스트상 허용되지 않는 SQL 구문을 실행하려 할 때 발생합니다. 대표적으로 STABLE 또는 IMMUTABLE로 선언된 함수 안에서 데이터를 변경하는 INSERT, UPDATE, DELETE, TRUNCATE 같은 DML 문을 사용할 때 이 에러가 트리거됩니다. 이 에러는 PostgreSQL이 함수의 실행 계획 최적화와 트랜잭션 일관성을 보호하기 위해 내부적으로 강제하는 제약이므로, 단순한 문법 오류가 아닌 설계 수준의 문제로 접근해야 합니다.

---

주요 발생 원인

1. IMMUTABLE 또는 STABLE 함수 내에서 데이터 변경 DML 사용

가장 흔한 원인입니다. IMMUTABLE 함수는 동일한 입력에 대해 항상 동일한 결과를 반환해야 하며, 데이터베이스 상태를 변경해서는 안 됩니다. STABLE 함수 역시 단일 트랜잭션 내에서는 결과가 일관되어야 하므로 데이터 변경 DML이 금지됩니다. 개발자가 편의를 위해 VOLATILE로 선언해야 할 함수를 실수로 IMMUTABLE이나 STABLE로 지정했을 때 이 에러가 발생합니다.

2. 트리거 함수에서 트리거를 발생시킨 테이블을 직접 수정하려는 시도

AFTER 또는 BEFORE 트리거 함수 내에서 트리거를 발생시킨 동일 테이블에 INSERT/UPDATE/DELETE를 직접 수행하면 이 에러가 발생할 수 있습니다. PostgreSQL은 트리거 실행 컨텍스트에서 특정 SQL 조작을 제한하며, 특히 BEFORE 트리거의 경우 행 수준의 직접 변경이 제한됩니다. 잘못 설계된 트리거 로직이 무한 루프 방지 목적의 내부 제약과 충돌하면서 이 에러를 유발합니다.

3. 읽기 전용 트랜잭션(READ ONLY) 또는 복제 슬롯 컨텍스트에서의 쓰기 시도

SET TRANSACTION READ ONLY 또는 세션 수준의 SET default_transaction_read_only = on으로 설정된 환경에서 함수 내부에 데이터 변경 로직이 포함되어 있을 때 이 에러가 발생합니다. PostgreSQL 스트리밍 복제의 스탠바이 서버나, pg_read_only 권한만 부여된 사용자가 쓰기 함수를 호출할 때도 유사하게 발생합니다. 이 경우 에러 메시지가 2F003과 함께 읽기 전용 트랜잭션 관련 메시지를 동반하는 경우가 많습니다.

---

해결 방법

원인 1 해결: 함수 Volatility 속성 수정

IMMUTABLE 또는 STABLE로 잘못 선언된 함수를 VOLATILE로 변경합니다.

-- 문제가 되는 함수 예시 (잘못된 선언)
CREATE OR REPLACE FUNCTION update_user_login(p_user_id INT)
RETURNS VOID
LANGUAGE plpgsql
IMMUTABLE  -- ❌ 잘못된 Volatility 선언
AS $$
BEGIN
    UPDATE users
    SET last_login = NOW()
    WHERE user_id = p_user_id;
END;
$$;

-- 해결: VOLATILE로 변경 (DML이 포함된 함수의 올바른 선언)
CREATE OR REPLACE FUNCTION update_user_login(p_user_id INT)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE  -- ✅ 올바른 Volatility 선언
AS $$
BEGIN
    UPDATE users
    SET last_login = NOW()
    WHERE user_id = p_user_id;
END;
$$;

-- 현재 함수의 Volatility 확인 방법
SELECT proname, provolatile
FROM pg_proc
WHERE proname = 'update_user_login';
-- provolatile: 'i' = IMMUTABLE, 's' = STABLE, 'v' = VOLATILE

원인 2 해결: 트리거 함수 로직 재설계

트리거 함수 내에서 같은 테이블을 직접 수정하지 않도록 로직을 분리합니다.

-- 문제가 되는 트리거 함수 (자기 참조 수정 시도)
CREATE OR REPLACE FUNCTION trg_update_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- ❌ BEFORE 트리거에서 동일 테이블 직접 수정 시도
    UPDATE orders
    SET updated_at = NOW()
    WHERE order_id = NEW.order_id;
    RETURN NEW;
END;
$$;

-- 해결: NEW 레코드를 직접 수정하여 반환 (BEFORE 트리거의 올바른 패턴)
CREATE OR REPLACE FUNCTION trg_update_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- ✅ NEW 레코드를 직접 변경 후 반환
    NEW.updated_at := NOW();
    NEW.updated_by := current_user;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_audit
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_update_audit();

-- 만약 AFTER 트리거에서 별도 이력 테이블에 기록해야 한다면
CREATE OR REPLACE FUNCTION trg_log_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- ✅ 다른 테이블(이력 테이블)에 기록하는 것은 허용
    INSERT INTO orders_audit_log(order_id, changed_at, changed_by, action)
    VALUES (NEW.order_id, NOW(), current_user, TG_OP);
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_orders_log
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_log_audit();

원인 3 해결: 트랜잭션 모드 및 권한 확인

-- 현재 세션의 트랜잭션 읽기/쓰기 설정 확인
SHOW default_transaction_read_only;
SHOW transaction_read_only;

-- 세션 수준에서 읽기 전용 해제 (권한이 있는 경우)
SET SESSION default_transaction_read_only = off;

-- 트랜잭션 수준에서 읽기/쓰기 모드 명시
BEGIN;
SET TRANSACTION READ WRITE;
SELECT update_user_login(42);
COMMIT;

-- 스탠바이 서버 여부 확인
SELECT pg_is_in_recovery();
-- TRUE이면 스탠바이 서버 → 쓰기 작업은 프라이머리 서버에서만 수행

-- 함수를 특정 역할로 실행하도록 SECURITY DEFINER 활용
CREATE OR REPLACE FUNCTION safe_write_function(p_id INT)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER  -- 함수 소유자 권한으로 실행
VOLATILE
AS $$
BEGIN
    UPDATE some_table SET col = 'value' WHERE id = p_id;
END;
$$;
-- 함수 소유자 설정 확인
ALTER FUNCTION safe_write_function(INT) OWNER TO postgres;

---

예방 방법

1. 함수 생성 시 Volatility 속성 명시 및 코드 리뷰 프로세스 수립

함수를 생성하거나 수정할 때 Volatility 속성(VOLATILE, STABLE, IMMUTABLE)을 항상 명시적으로 선언하고, 그 의미를 팀 전체가 이해하도록 교육하는 것이 중요합니다. CI/CD 파이프라인에 다음과 같은 검증 쿼리를 포함하여, IMMUTABLE/STABLE로 선언된 함수가 DML을 포함하고 있는지 사전에 탐지하는 자동화된 검사를 도입하세요.

-- IMMUTABLE/STABLE 함수 중 DML 관련 키워드를 포함하는 함수 탐지
SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    CASE p.provolatile
        WHEN 'i' THEN 'IMMUTABLE'
        WHEN 's' THEN 'STABLE'
        WHEN 'v' THEN 'VOLATILE'
    END AS volatility,
    pg_get_functiondef(p.oid) AS function_definition
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.provolatile IN ('i', 's')
  AND pg_get_functiondef(p.oid) ~* '\m(INSERT|UPDATE|DELETE|TRUNCATE)\M'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

2. 개발/스테이징 환경에서 함수 테스트 시 Volatility 위반 시나리오 포함

함수 단위 테스트(pgTAP 등 사용)를 작성할 때, 의도한 Volatility 등급에 맞는 동작을 검증하는 테스트 케이스를 반드시 포함하세요. 특히 IMMUTABLE 함수는 동일 입력에 대해 항상 동일한 결과를 반환하는지, 사이드 이펙트(데이터 변경)가 없는지를 자동화 테스트로 확인하는 습관을 가져야 합니다. 또한 PostgreSQL의 check_function_bodies 설정을 ON으로 유지하여 함수 생성 시 기본적인 문법 오류를 사전에 차단하세요.

-- 함수 본문 검사 활성화 확인
SHOW check_function_bodies;
-- 활성화 설정
SET check_function_bodies = ON;

---

관련 에러

• 2F000 (sql_routine_exception): SQL 루틴 실행 중 발생하는 일반적인 예외의 상위 클래스입니다. 2F003은 이 클래스에 속하는 세부 에러입니다.
• 2F002 (modifying_sql_data_not_permitted): READS SQL DATA로 선언된 SQL 함수에서 데이터 수정을 시도할 때 발생하며, 2F003과 유사한 맥락에서 발생합니다.
• 25006 (read_only_sql_transaction): 읽기 전용 트랜잭션에서 쓰기 작업을 시도할 때 발생하는 에러로, 원인 3번과 함께 나타나는 경우가 많습니다.
• P0001 (raise_exception): 트리거나 함수 내부에서 명시적으로 RAISE EXCEPTION을 호출할 때 발생하며, 잘못 설계된 함수 로직을 디버깅할 때 함께 확인해야 합니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.