PostgreSQL SQL Routine Exception (SQLSTATE 2F000)

When an SQL function or procedure in PostgreSQL violates a restriction on the type of SQL statements it may execute, PostgreSQL raises an error in the 2F SQLSTATE class. The base condition name is sql_routine_exception (SQLSTATE 2F000), and you will typically see a more specific subclass code in practice, such as 2F002 (modifying_sql_data_not_permitted) or 2F005 (prohibited_sql_statement_attempted).

What This Error Means

SQLSTATE class 2F covers errors that occur inside an SQL-language routine — a function or procedure defined with LANGUAGE SQL — when that routine attempts to execute a statement that is prohibited by the routine's declared volatility or data access characteristics.

PostgreSQL SQL functions carry attributes that govern what kinds of SQL they may execute. The CONTAINS SQL, NO SQL, READS SQL DATA, and MODIFIES SQL DATA attributes (along with the more commonly used VOLATILE, STABLE, and IMMUTABLE volatility markers) define the contract between the function and the planner. When the body of a function violates that contract at runtime — for example, a function declared READS SQL DATA attempts an INSERT — PostgreSQL raises a 2F-class error.

The 2F000 code is the parent (catch-all) for this class. In practice you are more likely to encounter one of the named subconditions:

• 2F002 — modifying_sql_data_not_permitted: the routine tried to modify table data but is not declared to do so.
• 2F003 — prohibited_sql_statement_attempted: the routine attempted a statement that is entirely prohibited in its context (for example, a transaction control statement inside a function).
• 2F004 — reading_sql_data_not_permitted: the routine tried to read table data but is declared NO SQL.
• 2F005 — function_executed_no_return_statement: a function with a return type reached the end of its body without executing a RETURN.

The error aborts the current statement and, unless caught by an exception handler, the surrounding transaction is marked as aborted.

Common Causes

1. Transaction control inside a non-procedure SQL routine. Calling COMMIT or ROLLBACK inside an SQL function (not a PROCEDURE) raises 2F003. Transaction control is only allowed inside procedures invoked with CALL.

2. A function declared IMMUTABLE or STABLE performing writes. If a function's body contains INSERT, UPDATE, or DELETE but the function is declared STABLE or IMMUTABLE, PostgreSQL may raise 2F002 depending on the execution context, because those volatility levels imply read-only access.

3. A function with a declared return type missing a RETURN statement. If execution reaches the end of the function body without hitting a RETURN, PostgreSQL raises 2F005. This is common when all code paths are inside IF branches and one branch falls through.

4. Calling a function declared NO SQL that actually queries a table. If a function is explicitly declared with NO SQL (an SQL standard attribute sometimes set via C-language function definitions or extensions), any attempt to access tables raises 2F004.

How to Fix sql_routine_exception

1. Check the specific SQLSTATE subcode. The error message and SQLSTATE code from your logs or client will point to the exact subcondition. Use that to narrow down the cause before changing anything.

   -- In psql, \errverbose after an error shows the SQLSTATE
   \errverbose
   

2. Move transaction control to a procedure. If you need COMMIT/ROLLBACK inside a routine, convert the function to a PROCEDURE and call it with CALL:

   -- Wrong: COMMIT inside a function
   CREATE FUNCTION process_batch() RETURNS void LANGUAGE sql AS $$
     INSERT INTO log(msg) VALUES ('start');
     COMMIT;  -- raises 2F003
   $$;
   
   -- Correct: use a procedure
   CREATE PROCEDURE process_batch() LANGUAGE sql AS $$
     INSERT INTO log(msg) VALUES ('start');
     COMMIT;
   $$;
   
   CALL process_batch();
   

3. Align the function's volatility with its actual behavior. A function that writes data must be VOLATILE (the default). A function that only reads data can be STABLE. A function that accesses no tables and returns a deterministic result can be IMMUTABLE. Incorrect volatility declarations cause both correctness issues and 2F-class errors:

   -- Correct: writing function must be VOLATILE (default, but explicit here)
   CREATE OR REPLACE FUNCTION archive_old_rows() RETURNS void
   LANGUAGE sql VOLATILE AS $$
     DELETE FROM events WHERE created_at < now() - interval '90 days';
   $$;
   

4. Ensure every code path returns a value. For functions with a non-void return type, every branch must execute a RETURN. In LANGUAGE sql, the last statement's result is returned implicitly, but make sure no path exits the function without a SELECT/RETURN:

   -- Potentially missing return if no row found
   CREATE FUNCTION get_user_name(uid int) RETURNS text LANGUAGE sql AS $$
     SELECT name FROM users WHERE id = uid;
     -- If no row matches, returns NULL implicitly — this is fine in SQL functions
     -- but in PL/pgSQL you would need an explicit RETURN
   $$;
   

5. Catch the error in PL/pgSQL when appropriate. If you are calling a routine that may raise a 2F-class error and you want to handle it gracefully:

   DO $$
   BEGIN
     PERFORM my_sql_function();
   EXCEPTION
     WHEN sql_routine_exception THEN
       RAISE NOTICE 'Caught sql_routine_exception: %', SQLERRM;
   END;
   $$;
   

Additional Information

• The 2F error class is defined by the SQL standard and has been part of PostgreSQL's error code table since at least PostgreSQL 8.0.
• PostgreSQL's error code reference lists all 2F subcodes in the appendix of the documentation under "Error Codes."
• Most client drivers (psycopg2, pgx, JDBC) surface 2F-class errors as a generic DatabaseError or ProgrammingError with the SQLSTATE available as a separate field — check e.pgcode (psycopg2) or getSQLState() (JDBC) for the exact code.
• ORMs that wrap functions in implicit transactions (e.g., Django with ATOMIC_REQUESTS) can mask 2F003 errors; the underlying cause is still a COMMIT/ROLLBACK inside a function.
• 2F005 (function_executed_no_return_statement) is the most common subcondition developers encounter, usually caught at development time when testing newly written PL/pgSQL or SQL functions.

Frequently Asked Questions

Why do I see 2F000 in my logs instead of a more specific code like 2F003? 2F000 is the generic parent code for the class. You will see it when PostgreSQL raises the error without being able to assign a more specific subcode, or when your logging/monitoring tool is grouping by error class rather than the full SQLSTATE. Check the full error message text alongside the code — it usually contains a description that identifies the exact problem.

Can I use COMMIT inside a PostgreSQL function? No. Transaction control statements (COMMIT, ROLLBACK, SAVEPOINT) are not allowed inside SQL functions or PL/pgSQL functions. They are only permitted inside stored procedures invoked via CALL. This is a deliberate design: functions are meant to execute within the caller's transaction, while procedures can manage their own transaction boundaries.

My function is declared VOLATILE but I still get a 2F error — why? VOLATILE is a PostgreSQL-specific volatility marker, not the same as the SQL standard MODIFIES SQL DATA attribute. If you are using a C extension or a foreign data wrapper that sets function attributes using the SQL standard access category, the mismatch can still raise 2F002. Check the function definition with \df+ function_name in psql to see all attributes.

How do I find which function triggered the error? The error message usually includes the function name and the line number within the function body. In psql you can run \errverbose immediately after the error to see the full detail, hint, and context fields, which include the call stack through nested function invocations.