PostgreSQL Prohibited SQL Statement Attempted (SQLSTATE 2F003)

When PostgreSQL raises this error, the message in your logs or psql session will look like:

ERROR:  prohibited statement attempted in a non-volatile function
SQLSTATE: 2F003

The condition name is prohibited_sql_statement_attempted and the SQLSTATE is 2F003. It occurs when a SQL-language function (or a function body evaluated under SQL execution rules) attempts a statement that its declared volatility category forbids.

What This Error Means

SQLSTATE class 2F is "SQL Routine Exception," which covers errors arising specifically from the execution of SQL-invokable routines — functions and procedures defined in the SQL language. Error 2F003 is one of several conditions in this class, sitting alongside 2F002 (MODIFYING_SQL_DATA_NOT_PERMITTED) and 2F004 (READING_SQL_DATA_NOT_PERMITTED).

PostgreSQL classifies functions by their volatility category: VOLATILE, STABLE, or IMMUTABLE. These categories carry strict behavioral guarantees:

• IMMUTABLE — the function cannot access the database at all and always returns the same result for the same inputs.
• STABLE — the function may read the database but must not modify it within a single transaction scan.
• VOLATILE — the function may read and modify the database freely (the default).

When a function is declared IMMUTABLE or STABLE, the planner may call it at plan time, cache its results, or inline it into queries. To enforce these guarantees, PostgreSQL checks whether the statements inside the function body are compatible with the declared volatility. A 2F003 error surfaces when the runtime behavior contradicts what was declared — for example, a data-modification statement (INSERT, UPDATE, DELETE, TRUNCATE) is executed inside a function not declared VOLATILE.

The error terminates the current statement. The surrounding transaction is left in an aborted state and must be rolled back before any further work can proceed.

Common Causes

1. DML inside an IMMUTABLE or STABLE function. The most direct cause: a function body contains an INSERT, UPDATE, DELETE, or TRUNCATE but the function is declared STABLE or IMMUTABLE. PostgreSQL enforces the purity contract at execution time.

2. Calling a volatile function from within an IMMUTABLE or STABLE function. Even if your outer function contains no DML directly, calling another function that is VOLATILE (or that itself modifies data) from inside a STABLE/IMMUTABLE function can trigger this error.

3. Transaction control statements inside a non-VOLATILE SQL function. Statements such as COMMIT, ROLLBACK, SAVEPOINT, or SET TRANSACTION are prohibited in functions that are not VOLATILE — and in some contexts are prohibited even in VOLATILE SQL-language functions (they are only permitted in procedures called via CALL).

4. Incorrect volatility declaration during function creation. A developer marks a function IMMUTABLE for performance reasons without fully understanding the restriction, then adds DML logic to it later — or the function relies on a sequence (nextval) which is inherently volatile.

How to Fix prohibited_sql_statement_attempted

1. Change the function's volatility to VOLATILE. If the function legitimately needs to modify data, drop the STABLE or IMMUTABLE declaration:

   CREATE OR REPLACE FUNCTION update_audit_log(record_id int)
   RETURNS void
   LANGUAGE sql
   VOLATILE   -- explicitly volatile, or omit: VOLATILE is the default
   AS $$
     INSERT INTO audit_log (record_id, logged_at) VALUES (record_id, now());
   $$;
   

2. Remove the DML from the function and restructure the logic. If the function must remain STABLE or IMMUTABLE (e.g., it is used in an index expression or a CHECK constraint), move all data-modification logic to a separate VOLATILE function or to the calling context:

   -- Keep this STABLE for use in index expressions
   CREATE OR REPLACE FUNCTION compute_score(val numeric)
   RETURNS numeric
   LANGUAGE sql
   STABLE
   AS $$
     SELECT val * 1.15;
   $$;
   
   -- Separate VOLATILE function handles writes
   CREATE OR REPLACE FUNCTION record_and_score(val numeric)
   RETURNS numeric
   LANGUAGE sql
   VOLATILE
   AS $$
     INSERT INTO score_log (val, computed_at) VALUES (val, now());
     SELECT compute_score(val);
   $$;
   

3. Replace sequences with deterministic logic in IMMUTABLE functions. Any call to nextval(), currval(), or random-number generators inside an IMMUTABLE function will trigger this error. Replace with deterministic computation or move the sequence call outside:

   -- WRONG: nextval is volatile
   -- CREATE OR REPLACE FUNCTION bad_id() RETURNS bigint LANGUAGE sql IMMUTABLE AS $$ SELECT nextval('my_seq'); $$;
   
   -- RIGHT: generate the ID before calling the function
   SELECT nextval('my_seq') AS new_id, compute_score(42);
   

4. Use PROCEDURE with CALL for transaction control. If you need COMMIT/ROLLBACK inside a routine, convert it to a procedure (introduced in PostgreSQL 11) and invoke it with CALL rather than as a function in a query:

   CREATE OR REPLACE PROCEDURE batch_process()
   LANGUAGE plpgsql
   AS $$
   BEGIN
     -- ... DML ...
     COMMIT;
     -- ... more DML ...
   END;
   $$;
   
   CALL batch_process();
   

Additional Information

• SQLSTATE class 2F (SQL Routine Exception) contains several related conditions: 2F000 (generic), 2F002 (MODIFYING_SQL_DATA_NOT_PERMITTED), 2F003 (PROHIBITED_SQL_STATEMENT_ATTEMPTED), and 2F004 (READING_SQL_DATA_NOT_PERMITTED). The precise code raised depends on what the function tried to do versus what its purity level allows.
• PostgreSQL introduced STABLE and IMMUTABLE volatility categories early in its history; the purity enforcement and these SQLSTATE codes have been present since at least PostgreSQL 8.x.
• Most client drivers (libpq, psycopg2, JDBC) surface this as a PSQLException (Java) or psycopg2.errors.ProhibitedSqlStatementAttempted with the 2F003 SQLSTATE. ORMs typically wrap it in their own exception hierarchy but preserve the underlying SQLSTATE.
• Functions declared IMMUTABLE can be used in index expressions (CREATE INDEX ... ON t (f(col))). A 2F003 error during index creation or rebuild usually means the function is incorrectly declared.

Frequently Asked Questions

Why does PostgreSQL only raise this error at runtime and not at function creation time? PostgreSQL performs a limited static check when a function is created or replaced, but it cannot always detect violations statically — especially when the function calls other functions whose volatility might be lower than expected, or when conditional code paths exist. The runtime check is the authoritative enforcement point.

Can an IMMUTABLE function ever write to the database? No. IMMUTABLE is the strictest volatility level. It signals to the planner that the function is a pure mathematical transformation. Any attempt to write data — or even call a volatile function — violates this contract and will raise 2F003 (or a related 2F error).

Is STABLE safe for functions that call now() or current_timestamp? Yes, with a nuance. STABLE functions are allowed to read the database and may use now() because PostgreSQL treats the transaction start time as constant within a statement. However, they still cannot modify data. Using clock_timestamp() (which changes within a statement) inside a STABLE function is technically incorrect by semantics, though PostgreSQL may not always raise an error for it.

My function is VOLATILE but I am still getting 2F003 — why? This can happen if the function is invoked in a context that imposes additional restrictions, such as inside a CHECK constraint expression, a generated column definition, a partial index predicate, or a view that is accessed by a rule rewrite. In those contexts, even VOLATILE functions may be restricted to read-only operations.