PostgreSQL Invalid SQLSTATE Returned (SQLSTATE 39001)

When an external routine (such as a PL/Java or another language handler) raises an error and passes back an SQLSTATE code that is not a valid five-character alphanumeric string, PostgreSQL raises:

ERROR:  invalid SQLSTATE code

The SQLSTATE code is 39001, the condition name is invalid_sqlstate_returned, and it belongs to error class 39 — External Routine Invocation Exception.

What This Error Means

SQLSTATE codes in SQL are always exactly five characters, consisting of two characters for the error class followed by three characters for the subclass (e.g., 23505 for unique-violation). When a trusted or untrusted procedural language handler propagates an exception back to PostgreSQL's error subsystem, it is expected to supply a valid five-character SQLSTATE. If the code provided by the external routine is malformed — wrong length, contains invalid characters, or is otherwise not a legitimate SQLSTATE — PostgreSQL cannot map it to a known condition and instead raises 39001.

Error class 39 covers the family of "external routine invocation exceptions" defined in the SQL standard. PostgreSQL reserves this class for problems that originate inside foreign or procedural language call handlers rather than inside the core engine itself. The four defined conditions in this class are 39000 (external_routine_invocation_exception, the generic catch-all), 39001 (invalid_sqlstate_returned), 39004 (null_value_not_allowed), and 39P01 (trigger_protocol_violated).

When 39001 is raised, the current transaction is aborted. Any work performed within the same transaction before the offending call must be rolled back before the connection can be reused.

Common Causes

1. Buggy language handler or extension: A PL/Java stored procedure (or another JVM-based language bridge) catches an exception and re-throws it with a custom error code that does not conform to the five-character alphanumeric format, such as an empty string, an integer, or a string shorter or longer than five characters.

2. Handwritten C-language function using ereport() incorrectly: A C-language function calling ereport() or elog() passes a raw integer that does not correspond to any valid SQLSTATE, or manually constructs an SQLSTATE string that is malformed.

3. Outdated or incompatible language extension version: An older version of a procedural language extension (e.g., PL/Java, PL/R) may have a known bug where error propagation passes a malformed SQLSTATE. Upgrading the extension resolves the issue.

4. Custom exception classes in PL/Python or PL/Perl that map to invalid codes: User-defined exception hierarchies in PL/Python (plpy.Error) or PL/Perl that explicitly set a sqlstate attribute to a non-standard value.

How to Fix invalid_sqlstate_returned

1. Identify the offending external routine. The error will typically appear in the context of a specific function call. Check the full error output including the CONTEXT line to identify which function triggered the error:

   -- Run the failing call and capture the full error context
   DO $$
   BEGIN
     PERFORM your_external_function();
   EXCEPTION WHEN OTHERS THEN
     RAISE NOTICE 'SQLSTATE: %, Message: %', SQLSTATE, SQLERRM;
   END;
   $$;
   

2. Fix the SQLSTATE in the external routine. Ensure every exception raised by the external code uses a valid five-character SQLSTATE. For PL/Java, use a legitimate code such as 'P0001' (the conventional SQLSTATE for user-defined exceptions in PostgreSQL):

   // PL/Java example — correct way to raise a user exception
   throw new SQLException("Something went wrong", "P0001");
   

   In PL/Python, set the sqlstate property to a valid code:

   # PL/Python example
   e = plpy.Error("Something went wrong")
   # Use a valid 5-character SQLSTATE; 'P0001' is the PostgreSQL convention
   raise plpy.SPIError("Something went wrong")
   

3. Upgrade the procedural language extension. If the error originates in a third-party extension like PL/Java or PL/R, check the project's issue tracker for known bugs related to SQLSTATE propagation and upgrade to the latest stable release.

4. Audit C-language functions. For functions written in C that call ereport(), verify that the errcode() argument uses a proper macro from utils/errcodes.h or the MAKE_SQLSTATE macro rather than an arbitrary integer:

   /* Correct usage in a C extension */
   ereport(ERROR,
       (errcode(ERRCODE_RAISE_EXCEPTION),   /* P0001 */
        errmsg("user-defined error: %s", detail)));
   

Additional Information

• SQLSTATE class 39 (External Routine Invocation Exception) is defined in the SQL standard and is distinct from class 38 (External Routine Exception), which covers errors within external routines rather than errors returned by them.
• Related SQLSTATE codes in the same class:
  • 39000 — external_routine_invocation_exception (generic)
  • 39004 — null_value_not_allowed (external routine received a NULL where one is prohibited)
  • 39P01 — trigger_protocol_violated (trigger function returned an invalid result)
• This error is rarely encountered with built-in PostgreSQL procedural languages (PL/pgSQL, PL/Tcl) because their error-raising mechanisms always generate valid SQLSTATE codes internally. It is most commonly seen with JVM-based language bridges (PL/Java) and C-language extensions.
• JDBC and libpq drivers surface this as a SQLException/PSQLException with getSQLState() returning "39001". Application-level code catching PSQLException can inspect the SQLSTATE to distinguish it from other errors.
• This error has been part of PostgreSQL since the SQL standard defined class 39. No version-specific behavioral changes are documented for this condition.

Frequently Asked Questions

What is a valid SQLSTATE code? A valid SQLSTATE is exactly five characters long. The first two characters identify the error class (e.g., 23 for integrity constraint violation) and the last three identify the specific subclass. Characters must be uppercase letters (A–Z) or digits (0–9). PostgreSQL reserves subclasses beginning with P or X for its own vendor extensions.

Why does this error abort my transaction? Any ERROR-severity condition in PostgreSQL aborts the current transaction. SQLSTATE 39001 has ERROR severity, so all work in the transaction is rolled back. You must issue a ROLLBACK (or rely on the client to do so automatically) before starting new work on the same connection.

Can I catch SQLSTATE 39001 in PL/pgSQL? Yes. You can trap it using the external_routine_invocation_exception condition name or the specific SQLSTATE '39001':

BEGIN
  PERFORM my_external_function();
EXCEPTION
  WHEN SQLSTATE '39001' THEN
    RAISE WARNING 'External routine returned an invalid SQLSTATE: %', SQLERRM;
END;

Is this error related to database authentication or connection problems? No. SQLSTATE 39001 has nothing to do with authentication or network connectivity. It is exclusively about a procedural or external routine supplying a malformed error code when raising an exception.