PostgreSQL 2F002 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2F002: modifying sql data not permitted

PostgreSQL error 2F002 (modifying sql data not permitted) occurs when a function or procedure attempts to execute data-modifying statements (INSERT, UPDATE, DELETE) in a context that prohibits such operations. This typically happens when a function is declared with incorrect volatility settings, or when write operations are attempted inside a read-only transaction context. Understanding the root cause is essential because this error can surface in subtle ways during production deployments.

Top 3 Causes and Fixes

1. Incorrect Function Volatility Declaration

The most common cause. Declaring a function as IMMUTABLE or STABLE while including DML statements inside it will immediately trigger 2F002. PostgreSQL treats these declarations as a promise that the function will not modify data.

-- ❌ Problematic: STABLE function with DML inside
CREATE OR REPLACE FUNCTION record_event(event_name TEXT)
RETURNS VOID
LANGUAGE plpgsql
STABLE  -- Wrong! This function modifies data
AS $$
BEGIN
    INSERT INTO event_log (event_name, created_at)
    VALUES (event_name, NOW());
END;
$$;

-- ✅ Fix: Change volatility to VOLATILE
CREATE OR REPLACE FUNCTION record_event(event_name TEXT)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE  -- Correct: functions that modify data must be VOLATILE
AS $$
BEGIN
    INSERT INTO event_log (event_name, created_at)
    VALUES (event_name, NOW());
END;
$$;

-- Alternatively, alter the existing function
ALTER FUNCTION record_event(TEXT) VOLATILE;

2. Calling a Write Function Inside a Read-Only Transaction

Any function that performs DML will fail if invoked within a READ ONLY transaction. This also applies to connections made to PostgreSQL hot standby (read replica) servers.

-- ❌ Problematic: calling a write function in a read-only transaction
BEGIN READ ONLY;
    SELECT record_event('USER_LOGIN');  -- Triggers 2F002
COMMIT;

-- ✅ Fix: Use a READ WRITE transaction (default)
BEGIN;  -- READ WRITE is the default
    SELECT record_event('USER_LOGIN');  -- Works correctly
COMMIT;

-- Check current transaction mode
SHOW transaction_read_only;

-- Check session-level default
SHOW default_transaction_read_only;

-- Reset to read-write if needed
SET default_transaction_read_only = OFF;

3. DML Inside Restricted Trigger Contexts

Certain trigger contexts, particularly CONSTRAINT TRIGGER, can restrict data modifications. Placing complex DML logic inside a BEFORE trigger that conflicts with PostgreSQL's execution context rules may produce this error.

-- ❌ Problematic pattern in a constraint trigger
CREATE CONSTRAINT TRIGGER trg_check_stock
AFTER INSERT ON order_items
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION validate_stock();

-- ✅ Fix: Use a standard AFTER trigger with VOLATILE function
CREATE OR REPLACE FUNCTION sync_inventory()
RETURNS TRIGGER
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
    UPDATE inventory
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;

    IF NOT FOUND THEN
        RAISE WARNING 'Product % not found in inventory', NEW.product_id;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_sync_inventory
AFTER INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION sync_inventory();

Quick Prevention Tips

Audit your function volatility regularly using the system catalog to catch mismatches before they reach production:

-- Audit all non-system functions for volatility settings
SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    CASE p.provolatile
        WHEN 'i' THEN 'IMMUTABLE'
        WHEN 's' THEN 'STABLE'
        WHEN 'v' THEN 'VOLATILE'
    END AS volatility
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND p.prokind = 'f'
ORDER BY volatility, function_name;

Always explicitly declare volatility when creating functions that contain DML, and include read-only transaction context tests in your CI/CD pipeline to catch 2F002 issues before deployment.

Related Errors

2F000 – General SQL routine exception (parent class of 2F002)
2F003 – Prohibited SQL statement attempted in a restricted routine
2F004 – Reading SQL data not permitted (read-side equivalent)
25006 – Read-only SQL transaction; often appears alongside 2F002 when connecting to a replica

📖 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