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

38002
2026년 06월 29일 | DBMS Error 가이드

이 글에서 다루는 내용

38002 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

38002 modifying sql data not permitted 는?

PostgreSQL 에러 코드 38002는 MODIFYING SQL DATA NOT PERMITTED 에러로, SQL 함수 또는 프로시저 내에서 데이터 변경이 허용되지 않는 상황에서 발생합니다. 주로 CONTAINS SQL 또는 READS SQL DATA로 선언된 함수 내부에서 INSERT, UPDATE, DELETE, TRUNCATE 등의 DML 문을 실행하려 할 때 발생합니다. 이 에러는 함수의 보안 및 트랜잭션 무결성을 보호하기 위한 PostgreSQL의 엄격한 규칙 적용 결과입니다.

주요 발생 원인

1. 함수 선언 시 잘못된 데이터 접근 수준 지정

PostgreSQL 함수를 생성할 때 READS SQL DATA 또는 CONTAINS SQL로 선언하면, 해당 함수 내에서는 데이터 수정 작업이 허용되지 않습니다. 그럼에도 불구하고 함수 본문에 INSERT, UPDATE, DELETE 같은 DML 문이 포함되면 이 에러가 발생합니다. 이는 함수 정의의 의도와 실제 구현이 불일치하는 경우 발생하는 전형적인 원인입니다.

2. PL/pgSQL 이외의 언어(PL/Python, PL/Perl 등)로 작성된 함수에서의 제한 위반

PL/Python, PL/Perl과 같은 Procedural Language로 작성된 함수에서도 함수의 속성이 읽기 전용으로 설정되어 있는 경우 데이터 변경 시 이 에러가 발생할 수 있습니다. 특히 기존에 작성된 레거시 함수를 재사용하거나 외부 라이브러리에서 제공된 함수를 사용할 때 함수의 선언 속성을 확인하지 않고 사용하면 문제가 발생합니다. 함수의 provolatile, proretset 등 시스템 카탈로그 속성을 반드시 확인해야 합니다.

3. 트리거 함수 또는 보안 정의 함수(SECURITY DEFINER)에서의 권한 및 속성 충돌

SECURITY DEFINER로 선언된 함수나 트리거 함수에서 데이터 수정 속성이 올바르게 설정되지 않은 경우에도 이 에러가 발생할 수 있습니다. 특히 복잡한 함수 체인(function call chain)에서 내부적으로 호출되는 함수가 읽기 전용 속성을 가지고 있을 때, 해당 함수 내에서 DML을 수행하면 에러가 전파됩니다. 이러한 경우는 디버깅이 어렵기 때문에 함수 호출 스택 전체를 검토해야 합니다.

해결 방법

원인 1 해결: 함수 선언의 데이터 접근 수준 수정

함수가 데이터를 수정해야 한다면 함수 선언에서 적절한 속성을 명시해야 합니다. MODIFIES SQL DATA 속성으로 함수를 재선언하거나, PostgreSQL 표준 방식으로 함수를 올바르게 작성하세요.

-- 잘못된 함수 선언 예시 (에러 발생)
CREATE OR REPLACE FUNCTION update_user_status(p_user_id INT, p_status TEXT)
RETURNS VOID
LANGUAGE plpgsql
-- READS SQL DATA 속성이 내부적으로 충돌 발생
AS $$
BEGIN
    UPDATE users SET status = p_status WHERE user_id = p_user_id;
END;
$$;

-- 올바른 함수 선언 예시 (VOLATILE 명시 - 기본값이지만 명시적으로 선언)
CREATE OR REPLACE FUNCTION update_user_status(p_user_id INT, p_status TEXT)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE  -- 데이터 변경이 가능한 함수임을 명시
AS $$
BEGIN
    UPDATE users SET status = p_status WHERE user_id = p_user_id;
    RAISE NOTICE 'User % status updated to %', p_user_id, p_status;
END;
$$;

-- 함수 호출 테스트
SELECT update_user_status(101, 'active');

원인 2 해결: 시스템 카탈로그에서 함수 속성 확인 및 수정

기존 함수의 속성을 확인하고, 필요하다면 ALTER FUNCTION을 통해 수정합니다.

-- 현재 함수의 속성 확인
SELECT 
    p.proname AS function_name,
    p.provolatile AS volatility,  -- 'v'=VOLATILE, 's'=STABLE, 'i'=IMMUTABLE
    p.prosecdef AS security_definer,
    p.proisstrict AS strict,
    pg_get_functiondef(p.oid) AS function_definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public'
  AND p.proname = 'update_user_status';

-- 함수의 VOLATILE 속성 변경 (데이터 수정 허용)
ALTER FUNCTION update_user_status(INT, TEXT) VOLATILE;

-- STABLE로 잘못 선언된 함수를 VOLATILE로 변경하는 예시
ALTER FUNCTION calculate_and_log_score(INT) VOLATILE;

-- 변경 후 확인
SELECT proname, 
       CASE provolatile 
           WHEN 'v' THEN 'VOLATILE (데이터 수정 가능)'
           WHEN 's' THEN 'STABLE (읽기 전용 권장)'
           WHEN 'i' THEN 'IMMUTABLE (완전 읽기 전용)'
       END AS volatility_description
FROM pg_proc
WHERE proname IN ('update_user_status', 'calculate_and_log_score');

원인 3 해결: SECURITY DEFINER 함수에서의 올바른 선언

-- SECURITY DEFINER 함수에서 데이터 수정이 필요한 경우의 올바른 작성 방법
CREATE OR REPLACE FUNCTION admin_update_salary(
    p_employee_id INT,
    p_new_salary NUMERIC
)
RETURNS TEXT
LANGUAGE plpgsql
VOLATILE
SECURITY DEFINER
-- search_path 보안 설정 (SECURITY DEFINER 함수에서 권장)
SET search_path = public, pg_temp
AS $$
DECLARE
    v_old_salary NUMERIC;
    v_result TEXT;
BEGIN
    -- 기존 급여 조회
    SELECT salary INTO v_old_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION '직원 ID %를 찾을 수 없습니다.', p_employee_id;
    END IF;

    -- 급여 업데이트 (VOLATILE이므로 허용됨)
    UPDATE employees
    SET salary = p_new_salary,
        updated_at = NOW()
    WHERE employee_id = p_employee_id;

    -- 감사 로그 기록
    INSERT INTO salary_audit_log (employee_id, old_salary, new_salary, changed_at)
    VALUES (p_employee_id, v_old_salary, p_new_salary, NOW());

    v_result := FORMAT('직원 %s의 급여가 %s에서 %s로 변경되었습니다.',
                       p_employee_id, v_old_salary, p_new_salary);
    RETURN v_result;
END;
$$;

-- 함수 실행 권한 부여 (필요한 역할에만)
REVOKE ALL ON FUNCTION admin_update_salary(INT, NUMERIC) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION admin_update_salary(INT, NUMERIC) TO hr_admin_role;

트리거 함수에서의 올바른 선언 방법:

-- 트리거 함수는 반드시 VOLATILE이어야 하며, RETURNS TRIGGER로 선언
CREATE OR REPLACE FUNCTION trg_update_modified_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
VOLATILE  -- 트리거 함수는 VOLATILE 속성 필수
AS $$
BEGIN
    NEW.updated_at = NOW();
    NEW.updated_by = current_user;
    RETURN NEW;
END;
$$;

-- 트리거 생성
CREATE TRIGGER tr_employees_update
    BEFORE UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION trg_update_modified_timestamp();

예방 방법

1. 함수 작성 시 휘발성 속성(Volatility) 명시적 선언 표준화

팀 내 코딩 표준으로 모든 함수에 VOLATILE, STABLE, IMMUTABLE 속성을 명시적으로 선언하는 규칙을 수립하세요. DML(INSERT, UPDATE, DELETE)을 포함하는 함수는 반드시 VOLATILE로, 단순 조회만 수행하는 함수는 STABLE 또는 IMMUTABLE로 선언하는 가이드라인을 문서화하고 코드 리뷰 시 이를 검증하는 체크리스트를 활용하면 사전에 에러를 방지할 수 있습니다.

-- 함수 속성 주기적 감사 쿼리 (CI/CD 파이프라인에 통합 권장)
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    CASE p.provolatile 
        WHEN 'v' THEN 'VOLATILE'
        WHEN 's' THEN 'STABLE'
        WHEN 'i' THEN 'IMMUTABLE'
    END AS volatility,
    p.prosecdef AS is_security_definer
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, p.proname;

2. 개발/스테이징 환경에서 함수 단위 테스트 자동화

pgTAP 등의 테스트 프레임워크를 활용하여 모든 함수에 대한 단위 테스트를 작성하고, 배포 전 자동으로 실행되도록 CI/CD 파이프라인에 통합하세요. 특히 함수가 DML을 수행하는지 여부를 검증하는 테스트를 포함시키면, 속성 불일치로 인한 에러를 프로덕션 환경에 도달하기 전에 조기에 발견할 수 있습니다.

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

38002 modifying sql data not permitted 는?

주요 발생 원인

해결 방법

예방 방법

관련 에러

댓글 남기기 응답 취소