SQLSTATE 2F002 appears when a statement tries to change data in a READ ONLY context such as a function or transaction block declared as read only.
PostgreSQL Error 2F002 (modifying_sql_data_not_permitted) occurs when an INSERT, UPDATE, DELETE, or TRUNCATE runs inside a read-only function or transaction. Convert the function or block to VOLATILE/MODIFIES SQL DATA or remove the read-only setting to resolve the issue.
PostgreSQL Error 2F002
PostgreSQL raises the condition modifying_sql_data_not_permitted (SQLSTATE 2F002) when an SQL command that changes data executes inside a context that forbids data modification.
The error protects database integrity by ensuring that functions, procedures, or transactions declared as READ ONLY do not perform INSERT, UPDATE, DELETE, or TRUNCATE operations.
The server checks the volatility and access mode of the surrounding block.
If it is declared STRICTLY READ ONLY, any statement that touches rows violates the contract and triggers 2F002.
Typical offenders are write statements inside IMMUTABLE or STABLE functions, read-only transaction blocks started with START TRANSACTION READ ONLY, or pglogical replication apply workers.
Identify the object or transaction that enforces read-only mode.
Change its declaration to VOLATILE or MODIFIES SQL DATA, or remove the READ ONLY clause, then rerun the write statement.
When modifying production code, favour minimal scope: wrap only the required write in a separate VOLATILE helper function or commit and reopen a read-write transaction.
Write inside function: redeclare the function as VOLATILE or add the CALLED ON NULL INPUT property to allow modification.
Write inside BEGIN READ ONLY: end the transaction, start a new read-write transaction, and execute the DML.
Always match function volatility to its behaviour.
Use IMMUTABLE/STABLE for pure reads only. Version control function code in Galaxy Collections to catch accidental writes during code review.
During long analytical sessions, start transactions without READ ONLY unless absolutely necessary, or open a second session for DML.
SQLSTATE 25006 (read_only_sql_transaction) fires when writes run in SERIALIZABLE READ ONLY DEFERRABLE mode. SQLSTATE 2F003 (prohibited_sql_statement_attempted) appears when you run transaction commands inside a function declared SECURITY DEFINER.
.
PostgreSQL forbids data changes in IMMUTABLE or STABLE functions.
Any DML inside triggers SQLSTATE 2F002.
A manual or driver-initiated read-only transaction blocks all data-changing statements until commit or rollback.
Some connection pools enforce global read-only mode, leading unsuspecting code to trigger the error.
Apply workers run in read-only mode for safety; user DML inside their callbacks will fail with 2F002.
.
No. Any data-changing operation invalidates STABLE semantics. Split the logic: one STABLE function for reads and a separate VOLATILE function for logging.
SET ROLE alone does not, but the new role may have default_transaction_read_only = on, indirectly triggering 2F002 when you attempt DML.
VOLATILE disables certain planner optimizations. For infrequent calls, the impact is negligible. Benchmark critical paths before and after the change.
Galaxy’s AI copilot flags DML inside read-only contexts during query review, while version-controlled Collections ensure vetted function definitions stay consistent.
.avif)
.avif){kind=logo}
