PostgreSQL Modifying SQL Data Not Permitted (SQLSTATE 2F002)

When PostgreSQL raises ERROR: modifying SQL data not permitted, you will see SQLSTATE 2F002 with condition name modifying_sql_data_not_permitted. This error occurs when a SQL-language function or procedure attempts to execute a data-modifying statement (INSERT, UPDATE, DELETE, MERGE, or TRUNCATE) but the function's volatility or data access level declaration prohibits such modifications.

What This Error Means

SQLSTATE class 2F covers "SQL Routine Exception" errors — a category of errors that arise specifically within the body of SQL-language functions and procedures. The 2F002 subcode means that the routine attempted to modify table data, but its declared data access option does not allow writes.

In PostgreSQL, SQL-language functions (LANGUAGE SQL) can be annotated with a data access level:

• NO SQL — the function contains no SQL statements
• CONTAINS SQL — the function may execute SQL, but not read or modify table data
• READS SQL DATA — the function may read but not modify table data
• MODIFIES SQL DATA — the function may read and modify table data

When a function is declared with CONTAINS SQL or READS SQL DATA and its body contains a statement that writes data, PostgreSQL raises 2F002 at execution time. The transaction remains open but the current statement is aborted; in most client configurations the transaction will need to be rolled back before further work can proceed.

Note that PostgreSQL does not enforce these annotations by default for LANGUAGE SQL functions the way the SQL standard requires — the annotations are largely advisory. However, when used with SECURITY DEFINER functions or when called from certain contexts (such as from within another function that enforces the restriction), the check can be triggered.

Common Causes

1. Function declared READS SQL DATA but contains a write statement. A developer annotated a function with READS SQL DATA to signal read-only intent, but the function body includes an INSERT, UPDATE, or DELETE.

2. Function declared CONTAINS SQL used for data modification. A function intended only to perform calculations or return computed values was later extended with a DML statement without updating its declaration.

3. Calling a write-capable function from a context that enforces read-only restrictions. Some PostgreSQL extensions or wrappers inspect the declared data access level and block calls that could modify data — for example, certain foreign data wrappers or parallel query execution paths.

4. Procedural language wrapper mismatch. A LANGUAGE SQL function wraps a stored procedure, and the outer function's declared access level conflicts with the inner procedure's actual behavior.

How to Fix modifying_sql_data_not_permitted

1. Update the function's data access declaration to MODIFIES SQL DATA.

   If the function legitimately needs to modify data, change its declaration:

   CREATE OR REPLACE FUNCTION update_user_status(user_id int, new_status text)
   RETURNS void
   LANGUAGE SQL
   MODIFIES SQL DATA
   AS $$
     UPDATE users SET status = new_status WHERE id = user_id;
   $$;
   

2. Remove the data-modifying statement if it was added by mistake.

   If the function was designed to be read-only, remove the offending DML statement and keep the READS SQL DATA or CONTAINS SQL annotation:

   CREATE OR REPLACE FUNCTION get_user_name(user_id int)
   RETURNS text
   LANGUAGE SQL
   READS SQL DATA
   AS $$
     SELECT name FROM users WHERE id = user_id;
   $$;
   

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

   When a function must remain read-only but a side effect is needed, extract the write operation into a separate PROCEDURE or a function declared MODIFIES SQL DATA, then call both from application code:

   -- Read function stays clean
   CREATE OR REPLACE FUNCTION get_next_sequence_value(seq_name text)
   RETURNS bigint
   LANGUAGE SQL
   READS SQL DATA
   AS $$
     SELECT last_value FROM pg_sequences WHERE sequencename = seq_name;
   $$;
   
   -- Write operation lives in its own routine
   CREATE OR REPLACE PROCEDURE advance_and_log_sequence(seq_name text)
   LANGUAGE SQL
   AS $$
     SELECT nextval(seq_name);
     INSERT INTO sequence_audit_log (seq_name, accessed_at)
     VALUES (seq_name, now());
   $$;
   

4. Check the calling context for access-level enforcement.

   If the error appears unexpectedly on a function that looks correct, investigate whether it is being called from a foreign data wrapper, a parallel worker, or an extension that enforces SQL standard data access levels. Review extension documentation for any restrictions on function volatility or data access.

Additional Information

• SQLSTATE class 2F (SQL Routine Exception) includes related conditions: 2F000 (sql_routine_exception), 2F003 (prohibited_sql_statement_attempted), 2F004 (reading_sql_data_not_permitted), and 2F005 (function_executed_no_return_statement).
• PostgreSQL's enforcement of CONTAINS SQL and READS SQL DATA restrictions has historically been loose for plain LANGUAGE SQL functions. The error is more commonly encountered when using the SQL/MED layer (foreign tables) or certain procedural language validators.
• PL/pgSQL functions use a different mechanism for volatility (VOLATILE, STABLE, IMMUTABLE) rather than SQL-standard data access levels; the 2F002 error is specific to LANGUAGE SQL routines.
• Most PostgreSQL client libraries (psycopg2, asyncpg, JDBC, node-postgres) surface this as a standard DatabaseError or SQLException with the SQLSTATE code available in the exception object, allowing programmatic handling.

Frequently Asked Questions

Why does PostgreSQL not always enforce READS SQL DATA for SQL functions? PostgreSQL treats the SQL-standard data access annotations on LANGUAGE SQL functions as documentation rather than strict enforcement in most contexts. The actual enforcement depends on the calling environment. This differs from the SQL standard's intent, where the annotations are mandatory constraints. You may not see 2F002 at all unless you are using an extension or FDW that actively checks these declarations.

What is the difference between 2F002 and 2F003 (prohibited_sql_statement_attempted)? 2F003 is raised when a function executes a SQL statement that is categorically not allowed in that context (for example, issuing a DDL statement inside a function declared NO SQL). 2F002 is specifically about data-modification statements (DML) inside a function declared as read-only (CONTAINS SQL or READS SQL DATA).

Can I encounter this error with PL/pgSQL functions? No. PL/pgSQL functions use the volatility classification system (VOLATILE, STABLE, IMMUTABLE) rather than SQL-standard data access levels. The 2F002 error code is reserved for LANGUAGE SQL routines. PL/pgSQL has its own restrictions, but violations result in different error codes.

My function was working before — why did it start raising this error? A common trigger is migrating to a new PostgreSQL major version, installing an extension that validates function metadata (such as certain FDW extensions), or refactoring a function from LANGUAGE plpgsql to LANGUAGE SQL while keeping a restrictive access annotation that was previously ignored.