PostgreSQL External Routine Exception (SQLSTATE 38000)

When an unhandled exception propagates out of an external routine — a function or procedure written in C, PL/Python, PL/Perl, PL/Ruby, or any other non-SQL procedural language — PostgreSQL surfaces it as ERROR: error occurred in external routine with SQLSTATE 38000 (external_routine_exception). In logs and client output the message typically looks like:

ERROR:  error occurred in external routine
DETAIL:  <language-specific error message>

What This Error Means

SQLSTATE class 38 groups errors that originate inside external routines — functions whose implementation lives outside the PostgreSQL SQL engine. The full class is defined in the SQL standard and PostgreSQL honours it for all procedural language extensions (PLs). 38000 is the catch-all condition name for this class; more specific sub-codes such as 38001 (containing_sql_not_permitted), 38002 (modifying_sql_data_not_permitted), 38003 (prohibited_sql_statement_attempted), and 38004 (reading_sql_data_not_permitted) exist for more narrowly defined violations, but if the language handler cannot map the exception to a sub-code it falls back to 38000.

When PostgreSQL invokes a C-language function, a PL/Python function, a PL/Perl function, or a function written with any other external language handler, that handler runs inside the backend process. If the handler raises an exception — a Python Exception, a Perl die, a C elog(ERROR, ...) call, or a language-level runtime error — the PL handler catches it and re-raises it to PostgreSQL as an error in class 38. The transaction or sub-transaction that called the function is aborted at that point; any enclosing transaction remains in the aborted state until it is explicitly rolled back.

Because 38000 is a parent class error, the exact failure is always language-specific. The DETAIL or HINT fields of the PostgreSQL error message (visible in logs with log_error_verbosity = verbose, or via the \errverbose metacommand in psql) carry the original exception message from the procedural language runtime.

Common Causes

1. Unhandled Python exception in PL/Python. Any uncaught Python exception — ValueError, KeyError, ImportError, a failed subprocess call, a network error in a requests call — propagates to PostgreSQL as SQLSTATE 38000. The Python traceback is included in the DETAIL field.

2. die or uncaught exception in PL/Perl. A PL/Perl function that calls die "something went wrong" without a surrounding eval block raises 38000. Perl runtime errors (divide by zero, undefined subroutine, etc.) do the same.

3. Bug or assertion failure in a C extension function. A C-language function that calls elog(ERROR, ...) directly generates a more specific SQLSTATE if one is provided, but if the extension uses a generic error path or the error code does not map to a known SQLSTATE, PostgreSQL may log it under class 38.

4. Missing Python/Perl module or shared library. If a PL/Python or PL/Perl function tries to import or use a module that is not installed in the server's runtime environment, the import itself raises an exception that becomes 38000.

5. PL/Python using an incompatible Python version. PostgreSQL is compiled against a specific Python version (2 or 3). Mixing PL/Python2U and PL/Python3U, or importing a module built for a different Python ABI, can raise an ImportError that surfaces as 38000.

How to Fix external_routine_exception

1. Read the DETAIL field. The language-level error message is almost always in DETAIL. In psql, run \errverbose immediately after the error to see the full message including DETAIL and HINT. In application code, inspect the error object's detail property (libpq / most drivers expose it).

   -- In psql, after reproducing the error:
   \errverbose
   

2. Add exception handling inside the PL/Python function. Wrap the body in a try/except block and use plpy.error() or plpy.warning() to emit structured PostgreSQL errors with a meaningful message.

   CREATE OR REPLACE FUNCTION safe_divide(a float, b float)
   RETURNS float
   LANGUAGE plpython3u AS $$
   try:
       if b == 0:
           raise ValueError("divisor must not be zero")
       return a / b
   except Exception as e:
       plpy.error(str(e))
   $$;
   

3. Add exception handling inside PL/Perl functions. Use an eval block and call elog(ERROR, ...) on failure.

   CREATE OR REPLACE FUNCTION safe_json_parse(input text)
   RETURNS text
   LANGUAGE plperl AS $$
   eval {
       use JSON;
       my $decoded = decode_json($_[0]);
       return encode_json($decoded);
   };
   if ($@) {
       elog(ERROR, "JSON parse failed: $@");
   }
   $$;
   

4. Verify the required Python/Perl modules are installed in the server environment. The PostgreSQL backend uses its own library search path, not the user's shell environment. Install the package system-wide or in the Python environment that the PostgreSQL server binary was linked against.

   # For PL/Python3u, install into the system Python 3 used by PostgreSQL:
   sudo pip3 install <module-name>
   # Or, on Debian/Ubuntu:
   sudo apt-get install python3-<module-name>
   

5. Check for PL/Python version mismatches. Confirm which Python version PostgreSQL was built with:

   SELECT prosrc FROM pg_proc
   WHERE proname = 'plpython3_call_handler';
   -- Or check the server logs at startup for "Python version" messages.
   

6. Use EXCEPTION blocks in PL/pgSQL when calling external functions. If you are calling a C or PL function from PL/pgSQL and want to handle failures gracefully, wrap the call:

   DO $$
   BEGIN
       PERFORM my_external_function(arg);
   EXCEPTION
       WHEN external_routine_exception THEN
           RAISE WARNING 'external function failed: %', SQLERRM;
   END;
   $$;
   

Additional Information

• SQLSTATE class 38 (external_routine_exception) is defined in SQL:1999 and all later revisions of the SQL standard. PostgreSQL has honoured it since at least version 7.4.
• Related sub-codes in class 38: 38001 (containing_sql_not_permitted), 38002 (modifying_sql_data_not_permitted), 38003 (prohibited_sql_statement_attempted), 38004 (reading_sql_data_not_permitted). These appear when a language declared with a specific SQL data access level (e.g., NO SQL, READS SQL DATA) violates that contract.
• Most PostgreSQL client drivers (libpq, psycopg2/psycopg3, asyncpg, JDBC, node-postgres) expose the SQLSTATE via the exception object. The Python psycopg2.errors.ExternalRoutineException class maps directly to 38000.
• Because external language functions run in the backend process, a C extension that causes an unrecoverable error (segfault, stack overflow) may terminate the backend entirely rather than raising a recoverable 38000. 38000 specifically means the error was caught and signalled through the normal PostgreSQL error mechanism.
• PL/Python and PL/Perl functions are marked SECURITY DEFINER or SECURITY INVOKER; permission or environment differences between roles can make an external routine fail in production while succeeding in development.

Frequently Asked Questions

Why does my PL/Python function work in psql but raises 38000 from my application? The most common reason is an environment difference. The PostgreSQL backend runs with its own environment variables and library paths; a Python module installed in a virtual environment or in the user's home directory may not be visible to the server process. Install the module system-wide, or set PYTHONPATH in the PostgreSQL service's environment file (e.g., /etc/postgresql/<version>/main/environment on Debian/Ubuntu).

How do I get the original Python traceback, not just "error occurred in external routine"? The Python traceback is stored in the PostgreSQL error's DETAIL field. Enable log_error_verbosity = verbose in postgresql.conf (or set it for your session with SET log_error_verbosity = verbose) and check the server log. In psql, run \errverbose immediately after the error. In psycopg2, access e.diag.message_detail on the caught exception.

Can I catch SQLSTATE 38000 in PL/pgSQL? Yes. Use the condition name external_routine_exception in an EXCEPTION block:

EXCEPTION
    WHEN external_routine_exception THEN
        -- handle it

This catches 38000 and all its sub-codes (38001–38004).

Is SQLSTATE 38000 always fatal to the transaction? The error aborts the current statement and the current (sub-)transaction, but if the call is wrapped in a PL/pgSQL EXCEPTION block or a SAVEPOINT, the surrounding transaction can continue. Without an exception handler, the transaction is left in an aborted state and must be explicitly rolled back before further commands can execute.