How to fix 39P02: srf_protocol_violated in PostgreSQL

PostgreSQLADVANCEDHIGHUpdated Dec 22, 2025· 83 views

The 39P02 error occurs when a set-returning function (SRF) or table function violates PostgreSQL's internal protocol requirements, typically when the function doesn't properly implement the expected return mode or fails to follow the correct sequence for returning multiple rows.

Quick fix

Fixes 39P02

-- Check the full error context
-- Example error:
-- ERROR: table-function protocol for materialize mode was not followed
-- CONTEXT: SQL function "list_postgres_log_files"

-- Identify if it's a system function or custom function
SELECT proname, prosrc, prorettype, proretset
FROM pg_proc
WHERE proname = 'your_function_name';
  1. Identify the problematic function
  2. Recreate extensions after PostgreSQL upgrades
  3. Fix PL/pgSQL function return statements
  4. Update C-language table functions to check allowedModes
postgresql — error
-- Check the full error context
-- Example error:
-- ERROR: table-function protocol for materialize mode was not followed
-- CONTEXT: SQL function "list_postgres_log_files"
-- Identify if it's a system function or custom function
SELECT proname, prosrc, prorettype, proretset
FROM pg_proc
WHERE proname = 'your_function_name';
39P02: srf_protocol_violated

What this error means

The PostgreSQL error code 39P02 (SRF_PROTOCOL_VIOLATED) is part of the "External Routine Exception" class (39xxx) and indicates that a set-returning function (SRF) has violated PostgreSQL's internal protocol for returning multiple rows. SRFs are functions that can return more than one row, such as RETURN NEXT, RETURN QUERY, or C-language table functions. This error most commonly occurs in two scenarios: First, when a C-language table function doesn't properly check whether the calling context supports the required return mode (like materialize mode) before attempting to return data. Second, when upgrading PostgreSQL versions (especially from v13 to v16+) where internal protocol changes may expose previously working but technically incorrect function implementations. The error can also manifest as "table-function protocol for materialize mode was not followed" when a table function attempts to use materialized returns without verifying that the caller supports this mode. This is a lower-level error than typical SQL mistakes—it usually indicates issues with function implementation or extension code rather than query syntax.

Typical symptoms

  • Error message displays "39P02: srf_protocol_violated" when executing queries
  • Message states "table-function protocol for materialize mode was not followed"
  • Functions that previously worked fail after PostgreSQL version upgrade
  • Custom C-language or extension functions fail when returning result sets
  • System catalog functions like list_postgres_log_files() fail unexpectedly
  • PL/pgSQL functions returning SETOF fail with protocol error
  • Error occurs during function execution, not during function creation

Common causes

  1. C-language table functions not checking rsinfo->allowedModes before materializing results
  2. PostgreSQL version upgrade exposing previously tolerated protocol violations
  3. Extensions or foreign data wrappers with outdated function implementations
  4. Incorrect use of RETURN NEXT or RETURN QUERY in PL/pgSQL functions
  5. Missing final RETURN statement in functions declared to return SETOF
  6. Function attempting to return data in a mode not supported by the calling context
  7. Corrupted or incompatible extension binaries after database upgrade

How to fix "39P02: srf_protocol_violated"

1Identify the problematic function

Review the error message to determine which function is causing the protocol violation:

sql
-- Check the full error context
-- Example error:
-- ERROR: table-function protocol for materialize mode was not followed
-- CONTEXT: SQL function "list_postgres_log_files"

-- Identify if it's a system function or custom function
SELECT proname, prosrc, prorettype, proretset
FROM pg_proc
WHERE proname = 'your_function_name';

If the function is part of an extension, note the extension name for the next steps.

2Recreate extensions after PostgreSQL upgrades

If the error started after a PostgreSQL version upgrade, recreate the affected extensions:

sql
-- For admin functions like list_postgres_log_files()
DROP EXTENSION IF EXISTS adminpack CASCADE;
CREATE EXTENSION adminpack;

-- For custom extensions
DROP EXTENSION IF EXISTS your_extension CASCADE;
CREATE EXTENSION your_extension;

-- If using foreign data wrappers
DROP EXTENSION IF EXISTS postgres_fdw CASCADE;
CREATE EXTENSION postgres_fdw;

Important: Use CASCADE carefully—it will drop dependent objects. Review dependencies first:

sql
SELECT dependent.relname
FROM pg_depend
JOIN pg_extension ON pg_depend.refobjid = pg_extension.oid
JOIN pg_class dependent ON pg_depend.objid = dependent.oid
WHERE pg_extension.extname = 'your_extension';
3Fix PL/pgSQL function return statements

For PL/pgSQL functions returning SETOF, ensure proper use of RETURN NEXT/QUERY and final RETURN:

Before (missing final RETURN):

sql
CREATE OR REPLACE FUNCTION get_users()
RETURNS SETOF users AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE active = true;
    -- Missing final RETURN to signal completion
END;
$$ LANGUAGE plpgsql;

After (correct protocol):

sql
CREATE OR REPLACE FUNCTION get_users()
RETURNS SETOF users AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE active = true;
    RETURN;  -- Signal function completion
END;
$$ LANGUAGE plpgsql;

For functions with conditional returns:

sql
CREATE OR REPLACE FUNCTION conditional_rows(limit_rows BOOLEAN)
RETURNS SETOF integer AS $$
BEGIN
    IF limit_rows THEN
        RETURN QUERY SELECT generate_series(1, 10);
    ELSE
        RETURN QUERY SELECT generate_series(1, 100);
    END IF;
    RETURN;  -- Always end with RETURN
END;
$$ LANGUAGE plpgsql;
4Update C-language table functions to check allowedModes

If you maintain C-language extensions, ensure proper protocol checking:

Correct implementation pattern:

c
#include "postgres.h"
#include "funcapi.h"

Datum
my_table_function(PG_FUNCTION_ARGS)
{
    FuncCallContext *funcctx;
    ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;

    // Check if caller supports materialize mode
    if (!rsinfo || !(rsinfo->allowedModes & SFRM_Materialize))
        ereport(ERROR,
            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
             errmsg("materialize mode required, but not supported in this context")));

    if (SRF_IS_FIRSTCALL())
    {
        funcctx = SRF_FIRSTCALL_INIT();
        // Initialize result tuple descriptor
        // Build tuplestore for materialized results
    }

    funcctx = SRF_PERCALL_SETUP();

    // Return rows...

    SRF_RETURN_DONE(funcctx);
}

This ensures the function only operates in supported modes.

5Verify function definition and calling context

Ensure functions are called in appropriate contexts:

Problematic contexts for SRFs (pre-PostgreSQL 10):

sql
-- SRF in SELECT clause (deprecated in PostgreSQL 10+)
SELECT id, generate_series(1, 5) FROM users;

-- Better: Use LATERAL join
SELECT u.id, g.n
FROM users u
CROSS JOIN LATERAL generate_series(1, 5) AS g(n);

Check function return type matches usage:

sql
-- Verify function signature
SELECT
    proname,
    pg_get_function_result(oid) AS return_type,
    proretset AS returns_set
FROM pg_proc
WHERE proname = 'your_function';

-- Ensure calling query expects set-returning results
6Test with simplified function versions

Create a minimal test version to isolate the issue:

sql
-- Simple test function
CREATE OR REPLACE FUNCTION test_srf()
RETURNS SETOF integer AS $$
BEGIN
    RETURN QUERY SELECT generate_series(1, 3);
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- Test in different contexts
SELECT * FROM test_srf();  -- Should work
SELECT test_srf();         -- Also should work

-- If test function works but original doesn't, compare implementations

For table functions returning composite types:

sql
CREATE TYPE test_result AS (id INT, name TEXT);

CREATE OR REPLACE FUNCTION test_table_func()
RETURNS SETOF test_result AS $$
BEGIN
    RETURN QUERY SELECT 1, 'first'::TEXT;
    RETURN QUERY SELECT 2, 'second'::TEXT;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM test_table_func();

Advanced notes

Understanding PostgreSQL SRF Protocol Changes:

PostgreSQL 10 introduced significant changes to how set-returning functions are evaluated. Prior to v10, SRFs in SELECT clauses had unpredictable behavior. From v10 onward, SRFs are evaluated before scalar expressions, essentially treating them as if they were in a LATERAL FROM clause.

Set-Returning Function Modes:

PostgreSQL supports three modes for returning sets:
1. ValuePerCall (SFRM_ValuePerCall): Function returns one row per call
2. Materialize (SFRM_Materialize): Function builds complete result set in memory/disk
3. Materialize_Random (SFRM_Materialize_Random): Materialized with random access

C-language functions must explicitly check which modes are supported by the calling context using:

c
if (rsinfo->allowedModes & SFRM_Materialize) {
    // Materialize mode is allowed
}

Debugging Protocol Violations:

Enable detailed logging to capture function execution:

sql
SET client_min_messages = DEBUG1;
SET log_error_verbosity = VERBOSE;

-- Execute problematic function to see detailed error context

Check if function volatility is correctly declared:

sql
SELECT
    proname,
    CASE provolatile
        WHEN 'i' THEN 'IMMUTABLE'
        WHEN 's' THEN 'STABLE'
        WHEN 'v' THEN 'VOLATILE'
    END AS volatility,
    proretset
FROM pg_proc
WHERE proname = 'your_function';

Extension Version Compatibility:

After major PostgreSQL upgrades, always check extension versions:

sql
SELECT * FROM pg_available_extensions WHERE name = 'your_extension';

-- Compare installed vs available version
SELECT
    extname,
    extversion AS installed,
    (SELECT version FROM pg_available_extension_versions
     WHERE name = extname AND installed = false
     ORDER BY version DESC LIMIT 1) AS available
FROM pg_extension;

-- Upgrade if needed
ALTER EXTENSION your_extension UPDATE TO 'new_version';

Creating Custom SRF Wrapper Functions:

If you can't modify the underlying C function, create a SQL wrapper:

sql
CREATE OR REPLACE FUNCTION safe_log_files()
RETURNS TABLE(filetime TIMESTAMP, filename TEXT) AS $$
BEGIN
    BEGIN
        RETURN QUERY SELECT * FROM pg_ls_logdir();
    EXCEPTION
        WHEN OTHERS THEN
            RAISE WARNING 'Log directory listing failed: %', SQLERRM;
            RETURN;
    END;
END;
$$ LANGUAGE plpgsql;

Alternative Approaches:

If the function continues to fail:
1. Replace with equivalent SQL-only implementation using CTEs and window functions
2. Use table-valued functions with explicit OUT parameters instead of SETOF
3. Implement as a view or materialized view if data source is queryable
4. For external data, consider foreign data wrapper alternatives

Frequently asked questions

What does "39P02" mean?
The PostgreSQL error code 39P02 (SRF_PROTOCOL_VIOLATED) is part of the "External Routine Exception" class (39xxx) and indicates that a set-returning function (SRF) has violated PostgreSQL's internal protocol for returning multiple rows. SRFs are functions that can return more than one row, such as RETURN NEXT, RETURN QUERY, or C-language table functions. This error most commonly occurs in two scenarios: First, when a C-language table function doesn't properly check whether the calling context supports the required return mode (like materialize mode) before attempting to return data.
How do I fix "39P02"?
The 39P02 error occurs when a set-returning function (SRF) or table function violates PostgreSQL's internal protocol requirements, typically when the function doesn't properly implement the expected return mode or fails to follow the correct sequence for returning multiple rows.
What causes "39P02"?
C-language table functions not checking rsinfo->allowedModes before materializing results PostgreSQL version upgrade exposing previously tolerated protocol violations Extensions or foreign data wrappers with outdated function implementations
Is the "39P02" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading