The function tried to run INSERT, UPDATE, DELETE, or DDL while marked STABLE or IMMUTABLE, so PostgreSQL blocked the write and raised SQLSTATE 38002.
modifying_sql_data_not_permitted (PostgreSQL error 38002) appears when a function marked STABLE or IMMUTABLE issues INSERT, UPDATE, DELETE, or other data-changing SQL. Redefine the routine as VOLATILE or move the write logic outside the function to solve the problem.
modifying_sql_data_not_permitted
PostgreSQL raises SQLSTATE 38002 when a routine attempts to change table data while declared STABLE, IMMUTABLE, PARALLEL SAFE, or LEAKPROOF. Those volatility levels promise the planner the function will not alter the database, so any write statement breaks that contract and triggers the error.
The message most users see is: ERROR: 38002: cannot modify SQL data in a non-volatile function.
The call halts immediately, leaving the transaction unmodified.
The error shows up during CREATE FUNCTION/CREATE PROCEDURE execution time if the body contains writable SQL, or at run-time when PL/pgSQL, SQL, or C functions carry out INSERT, UPDATE, DELETE, MERGE, TRUNCATE, or DDL while flagged as read-only.
It also appears inside generated columns, indexes on expressions, or CHECK constraints that call offending functions.
Leaving the error unresolved blocks application code, ETL jobs, and extensions that depend on the function.
Continuous failures can fill logs, obscure other problems, and cause partial deployments.
Correcting function volatility or refactoring logic restores smooth execution and protects data integrity.
The most common cause is defining a PL/pgSQL or SQL function as IMMUTABLE or STABLE and later adding UPDATE or INSERT statements to it.
Marking a function PARALLEL SAFE but issuing writes inside will also trigger the error because parallel-safe functions must be read-only.
Calling a data-modifying function from inside a view, rule, or constraint that demands read-only behavior propagates the same SQLSTATE 38002.
First, decide if the routine truly needs to modify data.
If not, remove the write statements.
If it must write, redefine the function as VOLATILE or create two separate functions: one read-only, one write-capable.
After updating the function definition, reload or deploy the change and rerun the original call to confirm the error is gone.
STABLE function performing UPDATE – change STABLE to VOLATILE or move UPDATE outside.
IMMUTABLE function with nextval() – redefine as VOLATILE or replace nextval() with a deterministic expression.
Parallel query using write-capable function – mark the function PARALLEL UNSAFE and VOLATILE.
Classify volatility accurately when first creating functions.
If the body might ever write, use VOLATILE.
Review functions during code-review to ensure updates, inserts, or DDL are not hidden inside STABLE/IMMUTABLE routines.
Automate checks with pg_catalog.pg_proc and pg_get_functiondef to flag risky volatility mismatches in CI pipelines. Galaxy’s AI copilot can surface those mismatches before merge.
read_only_sql_transaction (SQLSTATE 25006) – attempting writes in a transaction declared READ ONLY. Commit or start a writable transaction.
invalid_transaction_state (SQLSTATE 25000) – writing while in a failed transaction block.
Issue ROLLBACK then retry.
feature_not_supported (SQLSTATE 0A000) – executing unsupported command inside a function, usually due to language or version limits.
.
No. STABLE means the function will not change database state. PostgreSQL enforces this guarantee and throws SQLSTATE 38002 if you try.
VOLATILE functions may be executed more often because the planner cannot cache their results, but the performance hit is usually negligible compared to correctness.
The failing statement is aborted and the transaction enters an error state. You must ROLLBACK or RELEASE SAVEPOINT before proceeding.
Galaxy’s linting and AI copilot flag volatility mismatches during editing, preventing commits that would later fail in production.
.avif)
.avif){kind=logo}
