PostgreSQL External Routine: Modifying SQL Data Not Permitted (SQLSTATE 38002)

PostgreSQL raises ERROR: modifying SQL data not permitted with SQLSTATE 38002 when an external language function declared with READS SQL DATA (or a more restrictive data access level) attempts to execute a data-modifying SQL statement such as INSERT, UPDATE, DELETE, or TRUNCATE. The condition name is modifying_sql_data_not_permitted and it belongs to SQLSTATE class 38 — "External Routine Exception".

What This Error Means

SQLSTATE class 38 covers exceptions raised within external routines — functions written in languages outside of PL/pgSQL, such as PL/Java, PL/Perl, PL/Python, PL/R, or PL/Tcl. Within this class, 38002 specifically signals a mismatch between a function's declared SQL data access level and what the function body actually tries to do at runtime.

When you define an external function, you can annotate it with one of four SQL data access characteristics:

• NO SQL — the function does not execute any SQL at all
• CONTAINS SQL — the function may execute SQL but does not read or modify table data
• READS SQL DATA — the function may read table data but must not modify it
• MODIFIES SQL DATA — the function may both read and modify table data

If a function is declared READS SQL DATA (or the even more restrictive CONTAINS SQL or NO SQL) but its body issues a data-modifying statement, PostgreSQL detects the violation and raises 38002. The transaction is still active when this error occurs, but the offending statement is rolled back; depending on your error-handling setup, the entire transaction may need to be rolled back as well.

This mechanism exists because the SQL standard and PostgreSQL's optimizer use these declarations to reason about function side effects, parallelism eligibility, and safe inlining.

Common Causes

1. Incorrect data access declaration at function creation time. The function was created with READS SQL DATA but its body contains INSERT, UPDATE, DELETE, or TRUNCATE statements. This is the most common cause — a developer copied a function template without adjusting the access level.

2. A helper function called from the external routine performs writes. The external function itself may not issue DML directly, but it calls a PL/pgSQL or SQL helper function that does. The declared level of the outer function must accommodate everything that executes transitively.

3. A framework or ORM generates the function DDL automatically with a conservative (read-only) access level, but the application logic later requires writes.

4. Explicit SECURITY DEFINER + READS SQL DATA combination. Some developers add READS SQL DATA hoping it will restrict what callers can do, not realizing it also restricts the function itself.

How to Fix modifying_sql_data_not_permitted

1. Upgrade the function's data access level to MODIFIES SQL DATA.

   This is the correct fix when the function intentionally performs writes:

   -- Check current definition
   \df+ my_external_function
   
   -- Recreate or alter the function with the appropriate access level
   CREATE OR REPLACE FUNCTION my_external_function(p_id integer)
   RETURNS void
   LANGUAGE pljava
   MODIFIES SQL DATA
   AS 'com.example.MyClass.myMethod';
   

   For functions where the language supports ALTER FUNCTION, you can update the declaration without replacing the body:

   ALTER FUNCTION my_external_function(integer) MODIFIES SQL DATA;
   

2. Remove the data-modifying statements from the function if they are unintentional.

   If the function genuinely should be read-only, audit its body for any DML statements and remove them. Move write operations to a caller or a separate function with the appropriate access level.

3. Split the function into a read function and a write function.

   When you need both reads and writes but want to keep the access contract narrow for the read path, create two functions:

   -- Read-only function
   CREATE FUNCTION fetch_data(p_id integer)
   RETURNS text
   LANGUAGE pljava
   READS SQL DATA
   AS 'com.example.MyClass.fetchData';
   
   -- Write function
   CREATE FUNCTION update_data(p_id integer, p_value text)
   RETURNS void
   LANGUAGE pljava
   MODIFIES SQL DATA
   AS 'com.example.MyClass.updateData';
   

4. Check transitive callees. If the external function calls other PostgreSQL functions, verify that no callee issues DML unless the outer function is declared MODIFIES SQL DATA.

Additional Information

• SQLSTATE class 38 is defined by the SQL standard for external routine exceptions. PostgreSQL implements the full set: 38000 (external_routine_exception), 38001 (containing_sql_not_permitted), 38002 (modifying_sql_data_not_permitted), and 38003 (prohibited_sql_statement_attempted).
• The sibling code 38001 (containing_sql_not_permitted) is raised when a function declared NO SQL attempts any SQL statement at all — including reads.
• 38003 (prohibited_sql_statement_attempted) is a related condition for statements forbidden in the current context for other reasons.
• In practice, 38002 is most frequently seen with PL/Java and PL/Perl, where the data access declaration in the DDL is easy to overlook.
• PostgreSQL uses the declared data access level to determine whether a function can run in parallel workers. MODIFIES SQL DATA functions are marked PARALLEL UNSAFE by default; if you upgrade from READS SQL DATA to MODIFIES SQL DATA, verify that any query plan depending on parallel execution is still valid.
• Most PostgreSQL client drivers (libpq, psycopg2, JDBC, node-postgres) surface this as a generic DatabaseError or SQLException with the SQLSTATE 38002 in the error object's sqlstate / getSQLState() field.

Frequently Asked Questions

Why does PostgreSQL even have these data access level declarations?

They originate from the SQL standard and serve two purposes: they let the query optimizer reason about function side effects (for safe inlining and parallelism), and they enforce a contract that helps catch unintentional writes in read-only functions early — at runtime if not at definition time.

Can I set MODIFIES SQL DATA on a built-in or core function?

No. ALTER FUNCTION only applies to user-defined functions. Built-in functions have fixed access levels determined by their implementation.

Does this error affect the current transaction?

Yes. The error aborts the current statement. If you are not using a savepoint, the entire transaction is placed in an aborted state and must be rolled back before issuing further commands.

Is READS SQL DATA the default for external language functions?

The default depends on the language. For most external procedural languages in PostgreSQL (PL/Java, PL/Perl, etc.), if you omit the data access clause the default is typically CONTAINS SQL. Explicitly specifying the level is best practice to avoid surprises.