umzzil nng

Posted on May 29 • Originally published at oraerror.com

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

#postgres #database #dba #sql

02001 no additional dynamic result sets returned 는?

PostgreSQL 에러 코드 02001은 SQLSTATE 클래스 02(No Data)에 속하는 에러로, 저장 프로시저(Stored Procedure)나 함수가 동적 결과 집합(Dynamic Result Sets)을 반환할 것으로 기대했지만, 더 이상 반환할 추가적인 결과 집합이 존재하지 않을 때 발생합니다. 이 에러는 주로 CALL 문을 통해 프로시저를 호출한 후 결과 집합을 반복적으로 읽어오는 과정에서, 실제 반환된 결과 집합의 수보다 더 많은 결과 집합을 요청할 때 나타납니다. 특히 JDBC, ODBC 등의 드라이버 레이어에서 getMoreResults() 같은 메서드를 호출하거나, PL/pgSQL 내부에서 커서 기반의 동적 결과를 처리하는 로직에서 자주 마주치게 됩니다.

주요 발생 원인

1. 프로시저가 반환하는 결과 집합 수와 클라이언트의 기대값 불일치

저장 프로시저가 실제로 1개의 결과 집합만 반환하는데, 클라이언트 애플리케이션 또는 드라이버 코드가 루프를 돌며 2번 이상 추가 결과 집합을 요청하는 경우 이 에러가 발생합니다. 예를 들어, Java의 CallableStatement에서 getMoreResults()를 조건 검사 없이 반복 호출하는 패턴이 대표적인 원인입니다. 프로시저 내부에서 조건 분기에 따라 결과 집합을 반환하거나 반환하지 않을 때 특히 문제가 됩니다.

2. PL/pgSQL 또는 PL/Python 등에서의 REFCURSOR 처리 오류

PostgreSQL에서 동적 결과 집합은 주로 REFCURSOR를 통해 구현됩니다. 프로시저가 REFCURSOR를 OUT 파라미터로 선언하고 반환하는 경우, 호출 측에서 커서를 FETCH 하기 전에 커서가 이미 닫혀 있거나, 트랜잭션 경계가 초기화되어 커서가 무효화된 상태에서 추가 결과를 요청하면 이 에러가 발생할 수 있습니다. 특히 커서의 생명 주기(lifecycle)를 명확히 관리하지 않는 경우 발생 빈도가 높습니다.

3. 동적 SQL(EXECUTE) 사용 시 결과 집합 관리 실패

EXECUTE 문을 사용한 동적 SQL 내부에서 결과를 반환하는 쿼리를 실행하는 경우, 예상치 못한 실행 경로로 인해 결과가 생성되지 않거나 이미 소비된 상태에서 다시 읽으려고 할 때 이 에러가 유발됩니다. 동적 SQL 특성상 컴파일 타임에 결과 집합 수를 미리 알기 어렵기 때문에, 런타임에서의 엄격한 상태 관리가 필요합니다.

해결 방법

원인 1 해결: 결과 집합 수 명시적 확인 후 처리

프로시저를 설계할 때 반환하는 결과 집합의 수를 명확히 문서화하고, 클라이언트에서는 반드시 결과 집합 존재 여부를 확인한 후 처리해야 합니다.

-- 예시: 결과 집합을 명확히 하나만 반환하는 프로시저
CREATE OR REPLACE PROCEDURE get_active_users(
    OUT result REFCURSOR
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 커서를 명확하게 하나만 열어서 반환
    OPEN result FOR
        SELECT user_id, username, email
        FROM users
        WHERE is_active = TRUE
        ORDER BY created_at DESC;
END;
$$;

-- 호출 측에서 결과 집합을 안전하게 처리
BEGIN;
    CALL get_active_users('<cursor_name>');
    FETCH ALL FROM "<cursor_name>";
    CLOSE "<cursor_name>";
COMMIT;

-- 결과 집합이 조건에 따라 달라질 수 있는 경우, 빈 결과라도 커서를 반환하도록 보장
CREATE OR REPLACE PROCEDURE get_users_by_role(
    IN p_role TEXT,
    OUT result REFCURSOR
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 조건에 관계없이 항상 커서를 반환 (빈 결과여도 정상 반환)
    OPEN result FOR
        SELECT user_id, username, email
        FROM users
        WHERE role = p_role
          AND is_active = TRUE;
    -- 결과가 없더라도 커서 자체는 열린 상태로 반환됨
END;
$$;

원인 2 해결: REFCURSOR 생명 주기 명확히 관리

-- 트랜잭션 내에서 REFCURSOR를 올바르게 열고 닫는 패턴
DO $$
DECLARE
    v_cursor REFCURSOR;
    v_row    RECORD;
BEGIN
    -- 커서 열기
    OPEN v_cursor FOR
        SELECT order_id, total_amount
        FROM orders
        WHERE status = 'PENDING';

    -- 커서 유효성 확인 후 FETCH
    LOOP
        FETCH v_cursor INTO v_row;
        EXIT WHEN NOT FOUND;  -- 결과 없을 때 루프 탈출 (02001 방지)
        RAISE NOTICE 'Order: %, Amount: %', v_row.order_id, v_row.total_amount;
    END LOOP;

    -- 반드시 커서 닫기
    CLOSE v_cursor;
EXCEPTION
    WHEN no_data_found THEN
        -- 02001 에러 핸들링
        RAISE NOTICE '더 이상 반환할 결과 집합이 없습니다.';
        IF v_cursor IS NOT NULL THEN
            CLOSE v_cursor;
        END IF;
END;
$$;

-- 다중 REFCURSOR를 반환하는 프로시저의 안전한 패턴
CREATE OR REPLACE PROCEDURE get_dashboard_data(
    OUT cursor_users   REFCURSOR,
    OUT cursor_orders  REFCURSOR
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 각 커서를 명확히 열어 반환
    OPEN cursor_users FOR
        SELECT user_id, username FROM users WHERE is_active = TRUE;

    OPEN cursor_orders FOR
        SELECT order_id, total_amount FROM orders WHERE status = 'PENDING';
END;
$$;

-- 호출 시 두 커서를 모두 처리
BEGIN;
    CALL get_dashboard_data('cur_users', 'cur_orders');

    -- 첫 번째 커서 처리
    FETCH ALL FROM cur_users;
    CLOSE cur_users;

    -- 두 번째 커서 처리
    FETCH ALL FROM cur_orders;
    CLOSE cur_orders;
COMMIT;

원인 3 해결: 동적 SQL에서 결과 집합 존재 여부 체크

-- EXECUTE를 사용한 동적 쿼리에서 결과 집합을 안전하게 처리하는 패턴
CREATE OR REPLACE FUNCTION execute_dynamic_query(
    p_table_name TEXT,
    p_condition  TEXT DEFAULT NULL
)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    v_sql     TEXT;
    v_row     RECORD;
    v_count   INT;
BEGIN
    -- 테이블 존재 여부 사전 확인 (동적 SQL 실행 전 검증)
    SELECT COUNT(*) INTO v_count
    FROM information_schema.tables
    WHERE table_schema = 'public'
      AND table_name = p_table_name;

    IF v_count = 0 THEN
        RAISE EXCEPTION '테이블 [%]이 존재하지 않습니다.', p_table_name;
    END IF;

    -- 안전한 동적 SQL 구성 (SQL Injection 방지 포함)
    v_sql := format('SELECT * FROM %I', p_table_name);

    IF p_condition IS NOT NULL AND p_condition <> '' THEN
        v_sql := v_sql || ' WHERE ' || p_condition;
    END IF;

    -- 결과를 루프로 처리하며 FOUND 플래그 활용
    FOR v_row IN EXECUTE v_sql
    LOOP
        RETURN NEXT v_row;
    END LOOP;

    -- 결과가 없는 경우에도 정상 종료 (에러 발생 방지)
    RETURN;
END;
$$;

-- 에러 코드 02001을 명시적으로 CATCH하는 예외 처리 블록
CREATE OR REPLACE PROCEDURE safe_fetch_results()
LANGUAGE plpgsql
AS $$
DECLARE
    v_cursor REFCURSOR := 'my_dynamic_cursor';
    v_row    RECORD;
BEGIN
    BEGIN
        LOOP
            FETCH v_cursor INTO v_row;
            EXIT WHEN NOT FOUND;
            -- 데이터 처리 로직
            RAISE NOTICE 'Row: %', v_row;
        END LOOP;
    EXCEPTION
        WHEN SQLSTATE '02001' THEN
            -- 추가 결과 집합 없음 - 정상 종료 처리
            RAISE NOTICE '[02001] 추가 동적 결과 집합 없음. 처리 완료.';
        WHEN no_data_found THEN
            RAISE NOTICE '[02000] 데이터 없음. 처리 완료.';
    END;
END;
$$;

예방 방법

1. 프로시저 계약(Contract) 문서화 및 결과 집합 수 고정

저장 프로시저를 작성할 때는 반환하는 결과 집합의 수를 주석이나 별도 문서로 명시하고, 가능하면 OUT 파라미터로 REFCURSOR 개수를 고정하는 설계 원칙을 팀 내 코딩 컨벤션으로 정립해야 합니다. 조건 분기에 따라 결과 집합 수가 달라지는 설계는 반드시 지양하고, 결과가 없는 경우에도 빈 커서를 반환하는 방식으로 일관성을 유지해야 합니다. 또한 pgTAP 등의 테스트 프레임워크를 활용하여 프로시저 반환값에 대한 단위 테스트를 작성하면, 배포 전에 이런 종류의 문제를 사전에 차단할 수 있습니다.

-- 컨벤션 예시: 항상 동일한 수의 커서를 반환하는 표준 패턴
COMMENT ON PROCEDURE get_dashboard_data IS
    '반환 커서: 2개 (cursor_users, cursor_orders). 데이터 없는 경우에도 빈 커서 반환 보장.';

2. 애플리케이션 레이어에서 방어적 코딩 패턴 적용

JDBC나 psycopg2 등 드라이버를 사용하는 애플리케이션에서는 결과 집합을 가져오기 전에 반드시 존재 여부를 확인하는 방어적 코드를 작성해야 합니다. getMoreResults() 같은 메서드는 반환값(true/false)을 반드시 검사하고, PostgreSQL 측에서도 SET check_function_bodies = on; 옵션을 활성화하여 함수 생성 시점에 문법 및 로직 오류를 조기에 감지하는 환경을 구성하는 것이 좋습니다.

-- 개발/스테이징 환경에서 함수 검증 강화 설정
SET check_function_bodies = on;

-- 로그 레벨 설정으로 No Data 관련 경고 모니터링
SET client_min_messages = 'NOTICE';

SQLSTATE	에러명	설명
`02000`	`no_data_found`	쿼리 결과가 전혀 없을 때 발생하는 가장 기본적인 No Data 에러. `02001`과 같은 클래스에 속함
`34000`	`invalid_cursor_name`	존재하지 않는 커서 이름을 참조할 때 발생. REFCURSOR 관련 로직에서 `02001`과 함께 자주 등장
`24000`	`invalid_cursor_state`	이미 닫힌 커서나 잘못된 상태의 커서에서 FETCH를 시도할 때 발생
`P0002`	`no_data_found` (PL/pgSQL)	PL/pgSQL 블록 내에서 SELECT INTO가 결과를 반환하지 못할 때의 PL/pgSQL 전용 에러

DEV Community