PostgreSQL 38000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 38000: External Routine Exception

PostgreSQL error code 38000 (external_routine_exception) occurs when an unhandled exception is raised inside an external routine — functions written in procedural languages such as PL/Python, PL/Perl, PL/Tcl, PL/Java, or C extensions. This error belongs to SQL standard Class 38, and it surfaces when PostgreSQL cannot gracefully handle the exception thrown by the external code. Understanding its root causes and applying proper exception handling patterns will save you significant debugging time in production.

Top 3 Causes

1. Unhandled Exceptions in PL/Python Functions

The most common cause is a Python-level exception that is never caught inside a plpython3u function. When a ZeroDivisionError, TypeError, or any uncaught exception propagates to PostgreSQL, it becomes a 38000 error.

-- Problematic function: no exception handling
CREATE OR REPLACE FUNCTION divide(a FLOAT, b FLOAT)
RETURNS FLOAT
LANGUAGE plpython3u
AS $$
    return a / b  -- Raises ZeroDivisionError if b = 0
$$;

-- This will trigger ERROR: 38000
SELECT divide(10.0, 0.0);

-- Fixed version with proper exception handling
CREATE OR REPLACE FUNCTION divide_safe(a FLOAT, b FLOAT)
RETURNS FLOAT
LANGUAGE plpython3u
AS $$
    try:
        if b == 0:
            plpy.warning("Division by zero — returning NULL")
            return None
        return a / b
    except Exception as e:
        plpy.error(f"Unexpected error in divide_safe: {str(e)}")
$$;

-- Now returns NULL gracefully
SELECT divide_safe(10.0, 0.0);

2. Runtime Errors in C Extension Functions

Custom C-language extensions or third-party shared libraries (e.g., improperly compiled modules) can trigger 38000 when they encounter NULL pointer dereferences, memory access violations, or unhandled signals at runtime.

-- Check all external/C language functions registered in your database
SELECT
    n.nspname   AS schema,
    p.proname   AS function_name,
    l.lanname   AS language
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l  ON p.prolang = l.oid
WHERE l.lanname NOT IN ('sql', 'plpgsql', 'internal')
ORDER BY l.lanname, schema;

-- Wrap calls to C extensions with exception handling in PL/pgSQL
CREATE OR REPLACE FUNCTION safe_c_extension_call(val TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    result TEXT;
BEGIN
    BEGIN
        -- Replace with your actual C extension function call
        result := my_c_extension_func(val);
    EXCEPTION
        WHEN external_routine_exception THEN
            RAISE WARNING 'External routine error: %', SQLERRM;
            result := NULL;
        WHEN OTHERS THEN
            RAISE WARNING 'Unexpected error [%]: %', SQLSTATE, SQLERRM;
            result := NULL;
    END;
    RETURN result;
END;
$$;

3. Chained Exceptions from PL/pgSQL Calling External Routines

When a PL/pgSQL function calls a PL/Python or PL/Perl function that fails, the error propagates upward and gets wrapped as 38000. This makes diagnosis tricky because the root cause is buried in the call stack.

-- Use GET STACKED DIAGNOSTICS to extract full error context
DO $$
DECLARE
    v_state   TEXT;
    v_message TEXT;
    v_context TEXT;
BEGIN
    BEGIN
        PERFORM divide(5.0, 0.0);  -- External function that may fail
    EXCEPTION
        WHEN external_routine_exception THEN
            GET STACKED DIAGNOSTICS
                v_state   = RETURNED_SQLSTATE,
                v_message = MESSAGE_TEXT,
                v_context = PG_EXCEPTION_CONTEXT;

            RAISE NOTICE 'SQLSTATE : %', v_state;
            RAISE NOTICE 'Message  : %', v_message;
            RAISE NOTICE 'Context  : %', v_context;
    END;
END;
$$;

Quick Fix Solutions

Always wrap external language function bodies in try/except (Python) or eval { } (Perl) blocks.
Use plpy.error() or plpy.warning() instead of letting Python/Perl exceptions go unhandled.
Wrap calls to external functions in PL/pgSQL with EXCEPTION WHEN external_routine_exception blocks.
Enable verbose logging in postgresql.conf:

-- Recommended settings for diagnosing 38000 errors
-- In postgresql.conf:
-- log_min_error_statement = error
-- log_error_verbosity = verbose
-- log_min_messages = warning

-- Verify current settings
SHOW log_error_verbosity;
SHOW log_min_error_statement;

Prevention Tips

Standardize exception handling across all external functions. Every PL/Python, PL/Perl, or C extension function should have a top-level try/catch block that logs meaningful error messages before propagating to PostgreSQL. Adopt this as a mandatory code review checklist item.
Test edge cases before deployment. Run automated tests covering NULL inputs, boundary values, and invalid data types against all external functions in a staging environment. Use pg_regress or a custom DO-block test suite to catch 38000-prone scenarios before they hit production.

Related Error Codes

Code	Name	Description
`38001`	`containing_sql_not_permitted`	SQL not allowed inside the external routine
`38002`	`modifying_sql_data_not_permitted`	Data modification not permitted in external routine
`38003`	`prohibited_sql_statement_attempted`	Forbidden SQL statement attempted
`38004`	`reading_sql_data_not_permitted`	Reading SQL data not permitted
`39000`	`external_routine_invocation_exception`	Exception during external routine invocation

📖 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.

DEV Community