DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

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 that is not permitted in the current function's context is executed — most commonly when a data-modifying statement (INSERT, UPDATE, DELETE) is attempted inside a function declared as IMMUTABLE or STABLE. This error is enforced by PostgreSQL's internal execution model to protect query optimization and transactional consistency.

Top 3 Causes

1. DML Inside an IMMUTABLE or STABLE Function

This is by far the most common cause. Functions declared IMMUTABLE or STABLE must not modify database state, as PostgreSQL uses these hints to optimize query plans and cache results.

-- ❌ Problematic: DML inside an IMMUTABLE function
CREATE OR REPLACE FUNCTION bad_update(p_id INT)
RETURNS VOID
LANGUAGE plpgsql
IMMUTABLE  -- Wrong! This function modifies data
AS $$
BEGIN
    UPDATE users SET last_login = NOW() WHERE user_id = p_id;
END;
$$;

-- ✅ Fix: Declare it VOLATILE
CREATE OR REPLACE FUNCTION good_update(p_id INT)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE  -- Correct for data-modifying functions
AS $$
BEGIN
    UPDATE users SET last_login = NOW() WHERE user_id = p_id;
END;
$$;

-- Check existing function volatility
SELECT proname,
       CASE provolatile
           WHEN 'i' THEN 'IMMUTABLE'
           WHEN 's' THEN 'STABLE'
           WHEN 'v' THEN 'VOLATILE'
       END AS volatility
FROM pg_proc
WHERE proname = 'good_update';
Enter fullscreen mode Exit fullscreen mode

2. Directly Modifying the Trigger's Own Table in a BEFORE Trigger

In a BEFORE row-level trigger, you cannot directly UPDATE the same table that fired the trigger. PostgreSQL restricts this to prevent recursion and maintain consistency.

-- ❌ Problematic: Directly updating the same table in a BEFORE trigger
CREATE OR REPLACE FUNCTION bad_trigger()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    UPDATE orders SET updated_at = NOW() WHERE order_id = NEW.order_id;
    RETURN NEW;
END;
$$;

-- ✅ Fix: Modify the NEW record directly and return it
CREATE OR REPLACE FUNCTION good_trigger()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := NOW();
    NEW.updated_by := current_user;
    RETURN NEW;  -- PostgreSQL applies this change automatically
END;
$$;

CREATE TRIGGER trg_orders_before
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION good_trigger();
Enter fullscreen mode Exit fullscreen mode

3. Write Operations in a Read-Only Transaction Context

If the session or transaction is set to read-only mode (e.g., on a standby replica or explicitly set), any function containing write operations will fail with this error.

-- Check read-only status
SHOW default_transaction_read_only;
SELECT pg_is_in_recovery();  -- TRUE means you're on a standby server

-- ❌ This will fail in a READ ONLY transaction
BEGIN;
SET TRANSACTION READ ONLY;
SELECT update_user_login(42);  -- Error: 2F003
ROLLBACK;

-- ✅ Fix: Explicitly set READ WRITE mode (on primary server only)
BEGIN;
SET TRANSACTION READ WRITE;
SELECT update_user_login(42);
COMMIT;

-- Detect IMMUTABLE/STABLE functions containing DML keywords
SELECT proname, provolatile
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.provolatile IN ('i', 's')
  AND pg_get_functiondef(p.oid) ~* '\m(INSERT|UPDATE|DELETE)\M'
  AND n.nspname = 'public';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Cause Fix
Wrong volatility declared Change IMMUTABLE/STABLE to VOLATILE
BEFORE trigger modifies same table Use NEW.column := value and RETURN NEW
Read-only transaction/replica Use SET TRANSACTION READ WRITE or redirect to primary

Prevention Tips

1. Always explicitly declare function volatility and audit regularly.
Never rely on the default. Add a CI check that scans for IMMUTABLE/STABLE functions containing DML keywords before deployment.

2. Enable check_function_bodies in all environments.

SET check_function_bodies = ON;  -- Catches basic issues at function creation time
Enter fullscreen mode Exit fullscreen mode

Combine this with unit tests (e.g., using pgTAP) that validate function behavior matches its declared volatility category.

Related Errors

  • 2F000 – Parent class for all SQL routine exceptions
  • 2F002 – modifying_sql_data_not_permitted — similar context, triggered in READS SQL DATA functions
  • 25006 – read_only_sql_transaction — often appears alongside cause #3

📖 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.

Top comments (0)