PostgreSQL 38002 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 38002: modifying sql data not permitted

PostgreSQL error code 38002 (modifying sql data not permitted) occurs when a function or stored procedure attempts to execute data-modifying statements (INSERT, UPDATE, DELETE) in a context that explicitly prohibits such operations. This typically happens when a function is declared with the wrong volatility or SQL data access level, or when DML is attempted inside a read-only transaction. Understanding the root cause quickly is essential to resolving this error without breaking dependent application logic.

Top 3 Causes and Fixes

1. Wrong Function Volatility Declaration

The most common cause is declaring a function as STABLE or IMMUTABLE while the function body contains data-modifying statements. PostgreSQL enforces the declared access level strictly.

-- WRONG: STABLE function cannot modify data
CREATE OR REPLACE FUNCTION update_inventory(p_item_id INT, p_qty INT)
RETURNS VOID
LANGUAGE plpgsql
STABLE  -- This is the problem!
AS $$
BEGIN
    UPDATE inventory SET quantity = p_qty WHERE item_id = p_item_id;
END;
$$;

-- CORRECT: Use VOLATILE for any data-modifying function
CREATE OR REPLACE FUNCTION update_inventory(p_item_id INT, p_qty INT)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE  -- Allows data modification
AS $$
BEGIN
    UPDATE inventory SET quantity = p_qty WHERE item_id = p_item_id;
END;
$$;

-- Check current volatility of all functions in public schema
SELECT proname,
       CASE provolatile
           WHEN 'v' THEN 'VOLATILE'
           WHEN 's' THEN 'STABLE'
           WHEN 'i' THEN 'IMMUTABLE'
       END AS volatility
FROM pg_proc
JOIN pg_namespace ON pronamespace = pg_namespace.oid
WHERE nspname = 'public';

2. Attempting DML Inside a Read-Only Transaction

If a session or transaction is explicitly set to read-only mode, any write operation will trigger this error.

-- This will cause error 38002 (or 25006)
BEGIN READ ONLY;
    UPDATE orders SET status = 'processed' WHERE order_id = 500;
-- ERROR: cannot execute UPDATE in a read-only transaction
COMMIT;

-- Check current transaction read-only status
SHOW transaction_read_only;

-- Fix: Start the transaction in READ WRITE mode
BEGIN READ WRITE;
    UPDATE orders SET status = 'processed' WHERE order_id = 500;
COMMIT;

-- Fix session-level read-only setting
SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;

3. Trigger Functions Declared with Incorrect Volatility

Trigger functions that modify row data must be declared as VOLATILE. Using STABLE on a trigger function that modifies NEW record values will raise this error.

-- WRONG: Trigger function declared STABLE
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
STABLE  -- Incorrect for a trigger that modifies data
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

-- CORRECT: Trigger functions must be VOLATILE
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

-- Attach the corrected trigger
CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Quick Fix Checklist

Verify function volatility with SELECT proname, provolatile FROM pg_proc WHERE proname = 'your_function';
Change STABLE → VOLATILE for any function that runs DML.
Check session read-only status with SHOW transaction_read_only; and reset if needed.
Always declare trigger functions as VOLATILE.

Prevention Tips

Enforce a volatility naming convention in code reviews. Require every function PR to explicitly justify its volatility setting. Any function containing DML must be tagged VOLATILE without exception. Add an automated check in your CI pipeline that queries pg_proc to flag STABLE or IMMUTABLE functions containing suspicious SQL keywords.
Monitor session and connection pool settings regularly. Misconfigured connection pools can silently set sessions to read-only mode. Periodically audit your pool configuration and run SHOW transaction_read_only; as a health check query on each connection to catch unintended read-only sessions before they cause production incidents.

📖 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