PostgreSQL External Routine Invocation Exception (SQLSTATE 39000)

When an external routine — a trigger body, PL/Python function, PL/Perl function, or another procedural language extension — raises an unhandled or uncategorized error, PostgreSQL surfaces it with SQLSTATE 39000 and the condition name external_routine_invocation_exception. The message typically appears as ERROR: <message from the external routine> with a detail or context line indicating which function or trigger was executing.

What This Error Means

SQLSTATE class 39 covers External Routine Invocation Exception, defined in the SQL standard to capture errors that occur specifically when the database engine calls out to code running outside the core SQL execution engine. PostgreSQL maps this class to errors that arise inside procedural language handlers (PL/pgSQL is excluded — it uses class P0), external C functions, or language bridges such as PL/Python, PL/Perl, PL/Java, and PL/v8.

39000 is the parent (generic) code for the class. PostgreSQL does not define many child codes under class 39 the way it does for class 22 (data exceptions), so in practice 39000 is what you see when none of the more-specific sub-codes apply. The SQL standard defines a handful of children (39001 INVALID SQLSTATE RETURNED, 39004 NULL VALUE NOT ALLOWED, etc.), but PostgreSQL's own catalog lists only 39001 and the catch-all 39000.

When this error is raised, the current transaction is marked as aborted. No further SQL statements will execute until the application issues a ROLLBACK (or the connection framework does so automatically). The external routine's side effects are rolled back along with the rest of the transaction, provided the routine did not interact with external systems outside the database.

Common Causes

1. An unhandled Python/Perl/Java exception inside a PL function. If a PL/Python function raises a Python exception that is not caught inside the function body, PostgreSQL converts it to SQLSTATE 39000 and propagates the Python traceback in the DETAIL or CONTEXT fields.

2. A trigger written in an external language raising an error. A BEFORE INSERT trigger implemented in PL/Perl that calls elog(ERROR, ...) or Perl's die will surface as 39000 when the triggering DML statement is executed.

3. A NULL argument passed to an external routine that declares STRICT. Some external language functions use AS $$ ... $$ LANGUAGE plpython3u STRICT;. If all arguments are non-NULL but the function body still encounters an unexpected NULL from within, PostgreSQL may surface it through this class.

4. A misconfigured or missing external language extension. If the procedural language handler itself fails during initialization (e.g., the Python interpreter cannot be loaded, or a required module is absent), the invocation exception is raised before any user code executes.

5. A C extension function signaling an error via ereport(ERROR, ...) without a specific SQLSTATE. Custom C extensions that call ereport without assigning a SQLSTATE will default to XX000 (internal error), but improperly constructed extensions can land in class 39.

How to Fix external_routine_invocation_exception

1. Inspect the full error context. The SQLSTATE 39000 message on its own is vague. Always check the CONTEXT and DETAIL fields. In psql, run with \set VERBOSITY verbose to see all fields:

   \set VERBOSITY verbose
   -- Then re-run the failing statement
   

   In application code, inspect the error's pgcode, pgerror, diag structure (exact attribute names depend on the driver).

2. Add error handling inside the external routine. For PL/Python, wrap risky code in a try/except block and use plpy.error() or plpy.fatal() to emit structured errors:

   CREATE OR REPLACE FUNCTION safe_lookup(input text)
   RETURNS text
   LANGUAGE plpython3u
   AS $$
   try:
       result = some_operation(input)
       return result
   except Exception as e:
       plpy.error(f"safe_lookup failed: {e}")
   $$;
   

3. For PL/Perl, replace bare die with elog. A bare die "something went wrong" in PL/Perl will produce a generic 39000. Using elog(ERROR, "message") lets you attach a cleaner error message:

   CREATE OR REPLACE FUNCTION check_value(val integer)
   RETURNS boolean
   LANGUAGE plperl
   AS $$
     my $v = shift;
     if ($v < 0) {
       elog(ERROR, "check_value: negative values not allowed");
     }
     return 't';
   $$;
   

4. Verify the procedural language is correctly installed. Confirm the language handler is present and the shared library is loadable:

   SELECT * FROM pg_language WHERE lanname = 'plpython3u';
   

   If missing, install it with CREATE EXTENSION plpython3u; and ensure the Python shared library is available on the server host.

5. Check the PostgreSQL server log for the full traceback. For PL/Python errors, the Python traceback is written to the server log at log_min_messages = DEBUG1 or above, even if it is not returned to the client.

Additional Information

• SQLSTATE class 39 is defined in the ISO SQL standard; PostgreSQL follows it closely for external language handlers.
• Related sibling code: 39001 (INVALID_SQLSTATE_RETURNED) is raised when an external routine returns a SQLSTATE that PostgreSQL does not recognize.
• PL/pgSQL errors use class P0 (PLPGSQL_ERROR, P0001 for RAISE, etc.) — not class 39. If you see P0001 or P0002, that is a different error class.
• Most PostgreSQL client drivers (psycopg2, psycopg3, asyncpg, JDBC, node-postgres) expose the SQLSTATE as a property on the exception object. For 39000, psycopg2 raises psycopg2.errors.ExternalRoutineInvocationException.
• Because class 39 errors abort the current transaction, connection poolers (PgBouncer, RDS Proxy) will not recycle the connection until the application sends ROLLBACK. Failure to roll back can exhaust the pool.
• This error class has been part of PostgreSQL's SQLSTATE mapping since at least PostgreSQL 8.0.

Frequently Asked Questions

Why does my PL/Python function show SQLSTATE 39000 but no useful message? The Python traceback is often written to the PostgreSQL server log rather than returned to the client. Connect to the server and check the log file (typically in $PGDATA/log/), or temporarily set log_min_messages = DEBUG1 in postgresql.conf and reload. Alternatively, wrap your Python code in try/except and call plpy.error(str(e)) to surface the message explicitly.

Is 39000 the same as a PL/pgSQL RAISE EXCEPTION? No. RAISE EXCEPTION in PL/pgSQL produces SQLSTATE P0001 (RAISE_EXCEPTION), which belongs to class P0 (PL/pgSQL error). Class 39 is reserved for external procedural languages (PL/Python, PL/Perl, PL/Java, C extensions, etc.).

Can I catch SQLSTATE 39000 inside a PL/pgSQL BEGIN/EXCEPTION block? Yes. You can catch it by condition name or SQLSTATE:

BEGIN
    PERFORM my_plpython_function();
EXCEPTION
    WHEN external_routine_invocation_exception THEN
        -- handle or log the error
        RAISE NOTICE 'External routine failed: %', SQLERRM;
END;

What should I do if the error occurs inside a trigger? Identify which trigger is failing by examining the CONTEXT in the error message — it will name the trigger and table. Then review that trigger's function for unhandled exceptions. Adding logging inside the trigger function (e.g., plpy.log(...) in PL/Python) can help narrow down the exact failure point without aborting the transaction.