PostgreSQL External Routine: Reading SQL Data Not Permitted (SQLSTATE 38004)

PostgreSQL raises ERROR: reading SQL data not permitted with SQLSTATE 38004 and condition name reading_sql_data_not_permitted when an external routine (a function written in a language like PL/Java, PL/Python, or another trusted/untrusted external language) that was declared with NO SQL data access level attempts to read data from the database via a SQL query.

What This Error Means

SQLSTATE 38004 belongs to class 38 — "External Routine Exception." This entire class covers errors originating inside external (non-SQL) procedural language functions: the database itself is fine, but the behavior of the external function violates a constraint declared at function creation time.

When you create a function in PostgreSQL, you can optionally declare its SQL data access level using one of four options: NO SQL, CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA. These declarations tell PostgreSQL — and any optimizer or permission enforcement layer — what kind of SQL interaction the function is allowed to perform. When a function is declared NO SQL, PostgreSQL enforces that it must not issue any SQL statement at all during its execution.

If the function attempts to execute a SELECT (or any other data-reading SQL statement) despite that NO SQL declaration, PostgreSQL raises SQLSTATE 38004. The transaction is not necessarily aborted by this error alone — it depends on the error-handling behavior of the calling context — but the external function call fails and an exception is propagated to the caller.

Common Causes

1. Mismatch between declared and actual SQL access: A function was originally written as a pure computation (no SQL) but was later modified to include a SELECT query without updating its CREATE FUNCTION definition to READS SQL DATA.

2. Incorrect NO SQL declaration at creation time: When porting a function from another database system or writing a new external function, the developer specified NO SQL by mistake (or by default) even though the function body issues queries.

3. Framework or ORM-generated function definitions: Some code generators or migration tools emit NO SQL as a default data access clause when creating external routines, and the generated function body later includes data-reading logic.

4. Calling a helper that reads data: An external function declared NO SQL internally calls a helper routine or library method that in turn issues a SQL query, indirectly triggering the violation.

How to Fix reading_sql_data_not_permitted

1. Update the function's SQL access level to READS SQL DATA.
   If the function legitimately needs to read data, alter its definition:

   CREATE OR REPLACE FUNCTION my_external_func(arg integer)
   RETURNS text
   LANGUAGE pljava
   READS SQL DATA
   AS 'com.example.MyClass.myMethod';
   

   If the function was already created with NO SQL, you must drop and recreate it (or use CREATE OR REPLACE FUNCTION) with the corrected access level.

2. Verify the correct access level for what the function does.
   Match the declared level to the actual behaviour:

   Function behaviour	Correct declaration
   No SQL at all	NO SQL
   Executes SQL but reads no data	CONTAINS SQL
   Reads data (SELECT)	READS SQL DATA
   Reads and writes data	MODIFIES SQL DATA

3. Remove the SQL data-reading code if NO SQL was intentional.
   If the function genuinely should not read any data, review the function body and remove or refactor any SELECT statements or calls to routines that issue queries.

4. Check for indirect SQL access in dependencies.
   If the external function delegates to helper classes or libraries, trace all code paths to identify which call is issuing the SQL read. Update either the function's declaration or the code path accordingly.

Additional Information

• SQLSTATE class 38 covers the full set of external routine exceptions. Related sibling codes include 38001 (containing_sql_not_permitted) for functions declared NO SQL that attempt any SQL, 38002 (modifying_sql_data_not_permitted) for functions declared READS SQL DATA that attempt to write, and 38003 (prohibited_sql_statement_attempted) for functions that attempt a prohibited SQL statement type.
• The SQL data access clause (NO SQL, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA) follows the SQL standard (ISO/IEC 9075) and has been supported in PostgreSQL since external language functions were introduced. PostgreSQL enforces these declarations at runtime when the function is actually called.
• This error is most commonly encountered with PL/Java and PL/Python functions that make JDBC or plpy.execute() calls. In PL/Python specifically, using plpy.execute("SELECT ...") inside a function declared NO SQL will trigger 38004.
• Client drivers surface this as a SQLException (JDBC), psycopg2.errors.ReadingSqlDataNotPermitted, or similar driver-specific exception. The SQLSTATE 38004 is included in the error so applications can catch it specifically.

Frequently Asked Questions

What is the difference between SQLSTATE 38001 and 38004?
38001 (containing_sql_not_permitted) is raised when any SQL statement whatsoever is executed inside a NO SQL function, while 38004 (reading_sql_data_not_permitted) is raised specifically when a function declared CONTAINS SQL — which allows non-data SQL like SET — attempts a data-reading operation such as SELECT. In practice, 38004 fires when the declared level permits some SQL but not data access.

Will this error roll back my transaction?
Not automatically on its own. The error propagates as an exception from the function call. Whether the surrounding transaction is aborted depends on how the caller handles the exception — an unhandled exception in PL/pgSQL will abort the transaction, but a BEGIN ... EXCEPTION WHEN others THEN ... block can catch it and allow the transaction to continue.

How do I check a function's current SQL access declaration?
Query the pg_proc catalog. The provolatile column tracks volatility, but the data access level is stored in prosqlbody for SQL functions and in function-language-specific metadata for external languages. For PL/Java and similar languages the access level is reflected in the CREATE FUNCTION DDL you can retrieve with \sf function_name in psql or via pg_get_functiondef(oid).

Can I set READS SQL DATA on any external language function?
Yes — the SQL data access clause is independent of the procedural language. You can apply it to PL/Java, PL/Python, PL/Perl, C-language functions, and other external languages. The enforcement is done by the PostgreSQL function call infrastructure, not by the language handler itself.