PostgreSQL 39P02 Error: Causes and Solutions Complete Guide

PostgreSQL Error 39P02: srf protocol violated

The 39P02: srf protocol violated error occurs in PostgreSQL when a Set-Returning Function (SRF) violates its internal protocol contract. SRFs are functions declared with RETURNS SETOF or RETURNS TABLE that are designed to return multiple rows. This error is triggered when the function's return sequence deviates from what PostgreSQL's internal state machine expects.

Top 3 Causes

1. Misuse of RETURN NEXT / RETURN QUERY in PL/pgSQL

Using RETURN NEXT inside a function not declared as an SRF, or mixing incompatible return directives, breaks the protocol immediately.

-- WRONG: Using RETURN NEXT in a scalar function
CREATE OR REPLACE FUNCTION bad_example()
RETURNS INTEGER AS $$
BEGIN
    RETURN NEXT 42; -- Protocol violation!
END;
$$ LANGUAGE plpgsql;

-- CORRECT: Declare as RETURNS SETOF
CREATE OR REPLACE FUNCTION good_example()
RETURNS SETOF INTEGER AS $$
BEGIN
    RETURN NEXT 1;
    RETURN NEXT 2;
    RETURN NEXT 3;
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- CORRECT: Using RETURNS TABLE with RETURN QUERY
CREATE OR REPLACE FUNCTION get_users_by_status(active BOOLEAN)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
        SELECT u.id, u.name
        FROM users u
        WHERE u.is_active = active;
END;
$$ LANGUAGE plpgsql;

2. Incorrect C Extension SRF API Implementation

When writing SRFs in C, the macros SRF_IS_FIRSTCALL(), SRF_FIRSTCALL_INIT(), SRF_PERCALL_SETUP(), SRF_RETURN_NEXT(), and SRF_RETURN_DONE() must be called in the correct order. Skipping the first-call check or mismanaging the FuncCallContext causes a protocol violation.

-- SQL declaration for a properly implemented C SRF
CREATE OR REPLACE FUNCTION my_c_srf()
RETURNS SETOF TEXT
AS 'my_extension', 'my_c_srf'
LANGUAGE C STRICT;

-- Verify the function is recognized as an SRF
SELECT proname, proretset
FROM pg_proc
WHERE proname = 'my_c_srf';
-- proretset should be TRUE

3. Calling SRFs in Unsupported Contexts

PostgreSQL restricts where SRFs can be called. Using them inside trigger bodies directly, or in certain subquery positions in older PostgreSQL versions, can trigger this error.

-- RISKY in older PostgreSQL: SRF in WHERE clause
-- SELECT * FROM orders WHERE generate_series(1,5) = id;

-- SAFE: Use LATERAL joins instead
SELECT o.*
FROM orders o
JOIN LATERAL generate_series(1, 5) AS gs(n)
    ON o.id = gs.n;

-- SAFE: Use subquery to isolate SRF from aggregates
SELECT COUNT(*), SUM(val)
FROM (
    SELECT generate_series(1, 100) AS val
) subq;

-- SAFE: CROSS JOIN LATERAL for custom SRFs
SELECT u.name, logs.entry
FROM users u
CROSS JOIN LATERAL get_user_logs(u.id) AS logs(entry);

Quick Fix Solutions

-- Step 1: Find all SRF functions in your database
SELECT
    n.nspname  AS schema_name,
    p.proname  AS function_name,
    pg_get_function_result(p.oid) AS return_type
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proretset = TRUE
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema_name, function_name;

-- Step 2: Validate a specific SRF manually
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM good_example() LOOP
        RAISE NOTICE 'Row: %', rec;
    END LOOP;
END;
$$;

Prevention Tips

1. Enforce consistent function declarations: Always match RETURNS SETOF / RETURNS TABLE with RETURN NEXT / RETURN QUERY. Add function validation to your CI/CD pipeline using pg_dump --schema-only and review SRF declarations before every deployment.

-- Audit SRF return types vs. body directives in pg_proc
SELECT proname, proretset, prosrc
FROM pg_proc
WHERE proretset = TRUE
  AND pronamespace NOT IN (
      SELECT oid FROM pg_namespace
      WHERE nspname IN ('pg_catalog','information_schema')
  );

1. Test SRFs on every major PostgreSQL upgrade: SRF behavior can shift between major versions. Run your full SRF test suite in a staging environment before upgrading production, and use pgTAP for automated regression testing of all set-returning functions.

Related Errors

Code	Name	Relationship
0A000	feature_not_supported	SRF called in unsupported context
42P13	invalid_function_definition	Malformed SRF declaration
XX000	internal_error	Severe C extension SRF failure

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.