PostgreSQL 2F003 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2F003: prohibited sql statement attempted

PostgreSQL error 2F003: prohibited sql statement attempted occurs when a SQL statement is executed inside a procedural function (such as PL/pgSQL) that violates the function's volatility classification or the current transaction context. The most common scenario is attempting to run data-modifying statements (INSERT, UPDATE, DELETE) inside a function declared as STABLE or IMMUTABLE, or running write operations against a read-only session or standby server.

Top 3 Causes

1. DML Inside a STABLE or IMMUTABLE Function

PostgreSQL uses three volatility levels: VOLATILE, STABLE, and IMMUTABLE. If a function is declared STABLE or IMMUTABLE but contains data-modifying SQL, PostgreSQL throws 2F003 to protect query plan caching and data consistency.

-- ❌ This will cause error 2F003
CREATE OR REPLACE FUNCTION bad_log_function(user_id INT)
RETURNS VOID
LANGUAGE plpgsql
STABLE  -- Wrong: cannot modify data in a STABLE function
AS $$
BEGIN
    INSERT INTO access_log(user_id, logged_at)
    VALUES (user_id, NOW());
END;
$$;

-- ✅ Fix: change to VOLATILE
CREATE OR REPLACE FUNCTION good_log_function(user_id INT)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE  -- Correct: VOLATILE allows data modification
AS $$
BEGIN
    INSERT INTO access_log(user_id, logged_at)
    VALUES (user_id, NOW());
END;
$$;

-- Check a function's current volatility
SELECT proname,
       CASE provolatile
           WHEN 'v' THEN 'VOLATILE'
           WHEN 's' THEN 'STABLE'
           WHEN 'i' THEN 'IMMUTABLE'
       END AS volatility
FROM pg_proc
WHERE proname = 'bad_log_function';

2. Write Attempts on a Read-Only Transaction or Standby Server

Setting READ ONLY at the transaction or session level, or connecting to a Hot Standby replica, will block any write SQL and trigger this error.

-- Check if current server is a standby (read-only replica)
SELECT pg_is_in_recovery();
-- Returns true = Standby (no writes allowed), false = Primary

-- Check transaction/session read-only status
SHOW transaction_read_only;
SHOW default_transaction_read_only;

-- Fix: explicitly set READ WRITE at the transaction level (Primary only)
BEGIN;
SET TRANSACTION READ WRITE;
INSERT INTO orders(product_id, quantity) VALUES (101, 5);
COMMIT;

-- Fix: update session default (Primary only)
SET SESSION default_transaction_read_only = off;

3. Trigger Function Declared with Wrong Volatility

Trigger functions that perform INSERT, UPDATE, or DELETE must be declared VOLATILE. Declaring a trigger function as STABLE or IMMUTABLE will cause 2F003 at runtime.

-- ❌ Wrong: trigger function declared STABLE but modifies data
CREATE OR REPLACE FUNCTION trg_bad_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
STABLE  -- Wrong volatility for a trigger that writes data
AS $$
BEGIN
    INSERT INTO audit_log(op, changed_at)
    VALUES (TG_OP, NOW());
    RETURN NEW;
END;
$$;

-- ✅ Fix: declare trigger function as VOLATILE
CREATE OR REPLACE FUNCTION trg_good_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
VOLATILE  -- Correct for any trigger that modifies data
AS $$
BEGIN
    INSERT INTO audit_log(op, changed_at)
    VALUES (TG_OP, NOW());
    RETURN NEW;
END;
$$;

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION trg_good_audit();

Quick Fix Checklist

-- Find all STABLE/IMMUTABLE functions that may contain DML
SELECT n.nspname AS schema,
       p.proname AS function_name,
       CASE p.provolatile WHEN 's' THEN 'STABLE' WHEN 'i' THEN 'IMMUTABLE' END AS volatility
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.provolatile IN ('s', 'i')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND (p.prosrc ILIKE '%INSERT%'
    OR p.prosrc ILIKE '%UPDATE%'
    OR p.prosrc ILIKE '%DELETE%');

Prevention Tips

1. Always explicitly declare function volatility. Make it a team coding standard to always specify VOLATILE, STABLE, or IMMUTABLE when creating functions. Add a CI/CD check or code review item: "Does this function contain DML? If yes, is it declared VOLATILE?"

2. Separate read and write connection pools. Use a connection pooler like PgBouncer or HAProxy with target_session_attrs=read-write to ensure write queries are always routed to the Primary server. This prevents accidental write attempts on Standby replicas and eliminates a major source of 2F003 errors in distributed setups.

Related Errors

Error Code	Name	Relation
`2F000`	sql_routine_exception	Parent class of 2F003
`25006`	read_only_sql_transaction	Write on read-only session
`42501`	insufficient_privilege	Permission-related, often confused with 2F003
`0A000`	feature_not_supported	Unsupported feature in PL context

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community