PostgreSQL External Routine: Prohibited SQL Statement Attempted (SQLSTATE 38003)

When an external routine (such as a PL/Java or PL/Python stored procedure) tries to execute a SQL statement that is forbidden given its declared SQL access level, PostgreSQL raises:

ERROR:  prohibited SQL statement attempted
SQLSTATE: 38003

The condition name is prohibited_sql_statement_attempted, and it belongs to error class 38 — External Routine Exception.

What This Error Means

SQLSTATE class 38 covers errors originating inside external language routines — functions or procedures written in languages outside the core PostgreSQL engine, such as PL/Java, PL/Python, PL/Perl, or PL/R. Within this class, error 38003 specifically signals that the routine attempted to execute a SQL statement that is explicitly disallowed by the routine's declared SQL interaction level.

In the SQL standard (and PostgreSQL's implementation), functions can declare one of four SQL access levels: NO SQL, CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA. When a routine is declared with a restrictive level (most often NO SQL or CONTAINS SQL), the runtime enforces that contract — any attempt to issue a statement that violates it produces SQLSTATE 38003.

In PostgreSQL's internal PL languages (PL/pgSQL, PL/Tcl, PL/Perl), this particular error is rarely triggered because PostgreSQL does not enforce the SQL-standard purity declarations as rigidly. The error appears most consistently when using PL/Java via the PL/Java extension, which applies Java's SQL:2003-level enforcement of declared SQL access categories. After the error is raised the current transaction is put into an aborted state, and all subsequent statements until ROLLBACK will fail with "current transaction is aborted."

Common Causes

1. Function declared NO SQL issues a query. The function's CREATE FUNCTION statement (or the Java method annotation) declares NO SQL, but the function body executes a SELECT, INSERT, UPDATE, DELETE, or any other SQL statement.

2. Function declared CONTAINS SQL tries to read or modify data. CONTAINS SQL means SQL is used only for non-data-access purposes (e.g., SET or CALL with no data access). Issuing a SELECT inside such a function violates the contract and raises 38003.

3. PL/Java method annotation mismatch. In PL/Java, SQL access level can be annotated on the Java method with @Function(effects=IMMUTABLE) or similar. If the annotation restricts SQL access more tightly than the actual method body, the JVM enforces it at runtime.

4. Calling a restricted function indirectly. A function declared at a permissive level calls another function declared NO SQL, and that inner function attempts a query. The enforcement happens at the point of the restricted function's boundary.

How to Fix prohibited_sql_statement_attempted

1. Correct the SQL access level declaration. If the function legitimately needs to query or modify data, change its declared access level to match:

   -- Change from NO SQL to READS SQL DATA (for read-only access)
   CREATE OR REPLACE FUNCTION my_func()
     RETURNS text
     LANGUAGE java
     READS SQL DATA
     AS 'com.example.MyClass.myMethod';
   
   -- Or MODIFIES SQL DATA if the function writes to tables
   CREATE OR REPLACE FUNCTION my_func()
     RETURNS void
     LANGUAGE java
     MODIFIES SQL DATA
     AS 'com.example.MyClass.myMethod';
   

2. Remove the prohibited SQL from the function body. If the function is intentionally NO SQL or CONTAINS SQL, audit its body and remove any data-access statements. Move data retrieval to the caller instead.

3. Fix PL/Java method annotations. In PL/Java, check the Java source for @Function annotations and ensure the effects parameter matches what the method actually does:

   // If the method reads data, declare it appropriately
   @Function(effects = READS_SQL_DATA)
   public static String myMethod() throws SQLException {
       // ... SELECT logic ...
   }
   

4. Review indirect call chains. If the error comes from a nested call, identify which function in the chain has the overly restrictive declaration using the stack trace in the PostgreSQL log, then fix that specific function's declaration.

Additional Information

• SQLSTATE class 38 (External Routine Exception) has four defined conditions in PostgreSQL:

  • 38001 — containing_sql_not_permitted
  • 38002 — modifying_sql_data_not_permitted
  • 38003 — prohibited_sql_statement_attempted
  • 38004 — reading_sql_data_not_permitted These four errors form a family; if you see 38003, review the related codes to understand the full scope of restrictions.

• This error is most commonly encountered when using the PL/Java extension (CREATE EXTENSION pljava). Vanilla PL/pgSQL functions do not typically produce this error because PostgreSQL does not strictly enforce SQL access declarations for its built-in procedural languages.

• The SQL access level keywords (NO SQL, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA) originate from the SQL:2003 standard's definition of external routine behavior. PostgreSQL accepts them in CREATE FUNCTION syntax for compatibility but enforces them primarily for external language functions.

• JDBC drivers and Java ORMs (Hibernate, jOOQ) will surface this as a java.sql.SQLException with SQLState 38003. The exception message will be "prohibited SQL statement attempted".

• If you are catching this error in application code, always ROLLBACK (or allow the connection pool to reset the connection) before retrying, since the transaction is in an aborted state after the error.

Frequently Asked Questions

Why does this error only appear in my PL/Java functions, not in PL/pgSQL? PostgreSQL enforces the SQL standard's routine SQL access levels specifically for external language functions (class 38 errors). PL/pgSQL is tightly integrated into the PostgreSQL engine and bypasses this enforcement layer. PL/Java, running in a separate JVM, applies the full SQL:2003 enforcement contract.

Can I just declare all my functions as MODIFIES SQL DATA to avoid this error? Technically yes, but it's bad practice. The access level declaration is a contract that PostgreSQL and the JVM use to make optimization and concurrency decisions. Overstating access levels can prevent the planner from inlining functions, disable certain caching opportunities, and may cause issues inside read-only transactions or parallel query workers. Declare the minimum access level that is actually correct for your function.

The error appears inside a transaction — do I need to roll back? Yes. Once SQLSTATE 38003 is raised, the current transaction enters an aborted state. Every subsequent statement in that transaction will return "ERROR: current transaction is aborted, commands ignored until end of transaction block". You must issue ROLLBACK (or ROLLBACK TO SAVEPOINT if you set a savepoint before the call) before issuing new statements on that connection.

How do I find which function has the wrong SQL access declaration? Check the PostgreSQL server log (log_min_error_statement = error in postgresql.conf) — the log entry for SQLSTATE 38003 will include a context line showing the function name and, for PL/Java, the Java class and method. You can also query pg_proc to review declarations:

SELECT proname, prosrc, provolatile
FROM pg_proc
WHERE prolang = (SELECT oid FROM pg_language WHERE lanname = 'java');