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

2F004 reading sql data not permitted 는?

PostgreSQL 에러 코드 2F004는 reading sql data not permitted 오류로, SQL 함수 또는 PL/pgSQL 함수 내부에서 데이터를 읽으려 했지만 해당 함수의 선언된 속성이 이를 허용하지 않을 때 발생합니다. 즉, 함수가 NO SQL 또는 READS SQL DATA보다 더 제한적인 속성으로 정의되어 있음에도 불구하고 내부에서 SELECT 문이나 테이블 접근 쿼리를 실행하려 할 때 이 에러가 트리거됩니다. 주로 함수의 CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA 속성을 잘못 설정했을 때 실무 현장에서 자주 마주치는 에러입니다.

---

주요 발생 원인

• 함수 선언 시 NO SQL 속성을 지정했지만 내부에서 SQL 조회를 시도하는 경우

함수를 생성할 때 NO SQL 속성을 명시하면 PostgreSQL은 해당 함수가 어떠한 SQL 문도 실행하지 않는다고 가정합니다. 그런데 함수 본문에 SELECT, INSERT, UPDATE 등 SQL 문이 포함되어 있으면 실행 시점에 2F004 에러가 발생합니다. 특히 초기 개발 단계에서 함수 속성을 제대로 이해하지 못한 채 복사-붙여넣기 방식으로 함수를 작성하다 보면 이 문제가 빈번하게 생깁니다.

• CONTAINS SQL로 선언된 함수 내에서 데이터 읽기(SELECT) 시도

CONTAINS SQL은 함수가 SQL 문을 포함하고 있지만 데이터를 읽거나 수정하지 않는다는 의미입니다. 예를 들어 SET 명령어처럼 데이터를 조회하지 않는 SQL은 허용되지만, 실제 테이블에서 행을 읽어오는 SELECT 문을 사용하면 2F004 에러가 발생합니다. 이 속성과 READS SQL DATA의 차이를 혼동하는 경우가 많아 실수가 자주 발생합니다.

• 외부 라이브러리 또는 래퍼(Wrapper) 함수에서 잘못된 속성 상속

외부 C 라이브러리나 Foreign Data Wrapper(FDW)를 통해 정의된 함수가 내부적으로 SQL 데이터를 읽으려 하지만, 함수 속성이 NO SQL로 잘못 선언된 경우입니다. 이런 상황은 써드파티 확장 모듈을 설치하거나 DBA가 직접 C로 작성한 커스텀 함수를 등록할 때 종종 발생하며, 에러 원인을 파악하기 더 어렵기 때문에 주의가 필요합니다.

---

해결 방법

원인 1 해결: NO SQL을 올바른 속성으로 변경

함수 선언에서 NO SQL을 제거하고 실제 동작에 맞는 속성으로 교체합니다.

-- 문제가 있는 함수 (NO SQL인데 SELECT를 시도)
CREATE OR REPLACE FUNCTION get_user_name(p_user_id INT)
RETURNS TEXT
LANGUAGE plpgsql
NO SQL  -- 잘못된 속성
AS $$
DECLARE
    v_name TEXT;
BEGIN
    SELECT name INTO v_name
    FROM users
    WHERE user_id = p_user_id;
    RETURN v_name;
END;
$$;
-- 위 함수 실행 시 ERROR: 2F004 reading sql data not permitted 발생

-- 수정된 함수: READS SQL DATA 또는 속성 제거
CREATE OR REPLACE FUNCTION get_user_name(p_user_id INT)
RETURNS TEXT
LANGUAGE plpgsql
READS SQL DATA  -- 올바른 속성으로 변경
AS $$
DECLARE
    v_name TEXT;
BEGIN
    SELECT name INTO v_name
    FROM users
    WHERE user_id = p_user_id;
    RETURN v_name;
END;
$$;

PL/pgSQL 함수의 경우 기본적으로 속성을 명시하지 않아도 되지만, 명시할 경우 반드시 실제 동작과 일치해야 합니다.

---

원인 2 해결: CONTAINS SQL을 READS SQL DATA로 수정

-- 문제 함수: CONTAINS SQL로 선언했지만 SELECT 포함
CREATE OR REPLACE FUNCTION get_product_count()
RETURNS BIGINT
LANGUAGE sql
CONTAINS SQL  -- SELECT가 있으므로 이 속성은 부적절
AS $$
    SELECT COUNT(*) FROM products;
$$;

-- 수정: READS SQL DATA로 변경
CREATE OR REPLACE FUNCTION get_product_count()
RETURNS BIGINT
LANGUAGE sql
READS SQL DATA  -- 데이터를 읽으므로 올바른 속성
AS $$
    SELECT COUNT(*) FROM products;
$$;

-- 함수 속성 확인 쿼리
SELECT
    proname AS function_name,
    provolatile AS volatility,
    proparallel AS parallel_safety,
    prosrc AS function_body
FROM pg_proc
WHERE proname = 'get_product_count';

---

원인 3 해결: 외부 함수의 속성 재정의

-- 기존에 잘못 등록된 외부 함수 확인
SELECT proname, provolatile
FROM pg_proc
WHERE proname = 'my_external_func';

-- 함수 속성 수정 (ALTER FUNCTION 사용)
ALTER FUNCTION my_external_func(INT)
    CALLED ON NULL INPUT
    RETURNS NULL ON NULL INPUT;

-- 아예 함수를 다시 등록해야 하는 경우
DROP FUNCTION IF EXISTS my_external_func(INT);

CREATE OR REPLACE FUNCTION my_external_func(p_id INT)
RETURNS TEXT
LANGUAGE plpgsql
READS SQL DATA  -- 데이터를 읽는 경우 명시
AS $$
DECLARE
    v_result TEXT;
BEGIN
    SELECT some_column INTO v_result
    FROM some_table
    WHERE id = p_id;
    RETURN v_result;
END;
$$;

-- 함수 속성 전체 검토 쿼리 (NO SQL 혹은 CONTAINS SQL로 잘못 설정된 함수 탐지)
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 full_definition
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema_name, function_name;

---

예방 방법

• 함수 생성 시 반드시 SQL 데이터 접근 속성을 명시적으로 선언하고 코드 리뷰에 포함시키기

모든 사용자 정의 함수를 생성할 때 READS SQL DATA, MODIFIES SQL DATA, CONTAINS SQL, NO SQL 중 하나를 명시적으로 선언하는 팀 내 컨벤션을 수립하세요. 단순히 실행이 된다고 해서 속성이 올바른 것이 아니므로, 코드 리뷰 체크리스트에 “함수 SQL 데이터 접근 속성이 실제 본문과 일치하는가?”를 필수 항목으로 포함시키는 것이 좋습니다. 아래 예시처럼 속성을 명확히 문서화하면 유지보수성도 크게 향상됩니다.

“`sql

— Best Practice: 항상 속성을 명시적으로 선언

CREATE OR REPLACE FUNCTION calculate_discount(p_price NUMERIC)

RETURNS NUMERIC

LANGUAGE plpgsql

STABLE — 같은 트랜잭션에서 동일 결과 반환

READS SQL DATA — 데이터를 읽음을 명시

AS $$

DECLARE

v_rate NUMERIC;

BEGIN

SELECT discount_rate INTO v_rate

FROM discount_policy

WHERE is_active = TRUE

LIMIT 1;

RETURN p_price * (1 – v_rate);

END;

$$;

“`

• 정기적인 함수 속성 감사(Audit) 스크립트를 스케줄러에 등록하여 자동 점검 체계 구축하기

운영 환경에서는 시간이 지남에 따라 함수가 수정되면서 속성과 실제 동작이 불일치할 수 있습니다. 아래와 같은 감사 쿼리를 pg_cron이나 외부 스케줄러(cron)에 등록해 정기적으로 실행하고, 의심스러운 함수가 발견되면 즉시 알림을 받는 체계를 구축하세요.

“`sql

— 함수 속성 감사 쿼리: NO SQL이지만 SELECT 키워드가 포함된 함수 탐지

SELECT

n.nspname AS schema_name,

p.proname AS function_name,

pg_get_functiondef(p.oid) AS definition

FROM pg_proc p

JOIN pg_namespace n ON n.oid = p.pronamespace

WHERE n.nspname NOT IN (‘pg_catalog’, ‘information_schema’)

AND pg_get_functiondef(p.oid) ILIKE ‘%SELECT%’

AND pg_get_functiondef(p.oid) ILIKE ‘%NO SQL%’

ORDER BY schema_name, function_name;

“`

---

관련 에러

• 2F000 (sql_routine_exception): SQL 루틴 실행 중 발생하는 일반적인 예외의 부모 에러 클래스입니다. 2F004는 이 클래스의 하위 에러입니다.
• 2F002 (modifying_sql_data_not_permitted): 데이터를 수정하려 했지만 함수 속성이 허용하지 않을 때 발생하는 유사한 에러로, READS SQL DATA로 선언된 함수에서 INSERT/UPDATE/DELETE를 시도할 때 나타납니다.
• 2F003 (prohibited_sql_statement_attempted): 함수 컨텍스트에서 허용되지 않는 SQL 문을 실행하려 할 때 발생합니다.
• 42501 (insufficient_privilege): 권한 부족으로 인한 접근 거부 에러로, 2F004와 혼동되기 쉽지만 원인은 함수 속성이 아닌 사용자 권한 문제입니다.

---

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