DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

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 routine attempts to execute data-modifying statements (INSERT, UPDATE, DELETE, TRUNCATE) in a context where such operations are explicitly prohibited. This typically happens when a function's declared volatility or SQL access level conflicts with the actual operations performed inside it. Understanding function volatility and access attributes is key to resolving this error quickly.

Top 3 Causes and Fixes

1. Function Declared with Wrong Volatility Category

When a function is declared as STABLE or IMMUTABLE but contains DML statements, PostgreSQL will reject the execution. STABLE and IMMUTABLE functions are expected to be read-only, so any attempt to modify data inside them triggers error 38002.

-- PROBLEMATIC: STABLE function attempting data modification
CREATE OR REPLACE FUNCTION bad_update_function(p_id INT, p_value TEXT)
RETURNS VOID
LANGUAGE plpgsql
STABLE  -- This is wrong for a function that modifies data!
AS $$
BEGIN
    UPDATE my_table SET col = p_value WHERE id = p_id; -- Triggers 38002
END;
$$;

-- FIXED: Declare as VOLATILE (default for PL/pgSQL, but be explicit)
CREATE OR REPLACE FUNCTION good_update_function(p_id INT, p_value TEXT)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE  -- Explicitly allows data modification
AS $$
BEGIN
    UPDATE my_table SET col = p_value WHERE id = p_id;
    RAISE NOTICE 'Row % updated successfully.', p_id;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

2. Incorrect Function Attributes in System Catalog

Legacy functions or functions migrated from other databases may have incorrect volatility attributes stored in pg_proc. You need to inspect and correct them using ALTER FUNCTION.

-- Check current function volatility from system catalog
SELECT 
    proname AS function_name,
    CASE provolatile
        WHEN 'v' THEN 'VOLATILE'
        WHEN 's' THEN 'STABLE'
        WHEN 'i' THEN 'IMMUTABLE'
    END AS volatility,
    prosecdef AS is_security_definer
FROM pg_proc
WHERE proname = 'your_function_name'
  AND pronamespace = 'public'::regnamespace;

-- Fix: Change volatility to VOLATILE to allow data modifications
ALTER FUNCTION your_function_name(INT, TEXT) VOLATILE;

-- Verify the change
SELECT proname, provolatile FROM pg_proc WHERE proname = 'your_function_name';
Enter fullscreen mode Exit fullscreen mode

3. SECURITY DEFINER Function Missing Proper Declaration

Functions declared with SECURITY DEFINER without the correct volatility setting can also trigger this error, especially when they contain DML operations.

-- FIXED: Proper SECURITY DEFINER function with VOLATILE
CREATE OR REPLACE FUNCTION secure_insert_log(
    p_user_id INT,
    p_action TEXT
)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE                        -- Required for DML operations
SECURITY DEFINER
SET search_path = public        -- Security best practice
AS $$
BEGIN
    INSERT INTO audit_log (user_id, action, logged_at)
    VALUES (p_user_id, p_action, NOW());
END;
$$;

-- Restrict execution privileges
REVOKE ALL ON FUNCTION secure_insert_log(INT, TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION secure_insert_log(INT, TEXT) TO app_user;

-- Test the function
SELECT secure_insert_log(42, 'LOGIN');
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist 

-- Step 1: Identify all non-VOLATILE functions in your schema
SELECT proname, provolatile
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace
  AND provolatile IN ('s', 'i');  -- STABLE or IMMUTABLE

-- Step 2: For any function that modifies data, alter it
ALTER FUNCTION function_name(arg_types) VOLATILE;

-- Step 3: Re-run your query or function call to confirm resolution
SELECT your_function_name(args);
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Always explicitly declare volatility. Never rely on defaults. If your function runs INSERT, UPDATE, or DELETE, mark it VOLATILE. If it only reads data consistently within a transaction, use STABLE. If it never touches the database, use IMMUTABLE. Making this a mandatory code review checklist item will prevent 38002 from ever reaching production.

Automate function auditing in CI/CD. Integrate a pre-deployment query that scans pg_proc for functions whose volatility category conflicts with their actual SQL content. Tools like pgTAP can run unit tests against your functions before every deployment, catching attribute mismatches early and keeping your database functions reliable and maintainable.

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