How to fix 2F002: modifying_sql_data_not_permitted in PostgreSQL

PostgreSQLADVANCEDMEDIUMUpdated Dec 22, 2025· 117 views

PostgreSQL error 2F002 occurs when attempting to execute data-modifying statements (INSERT, UPDATE, DELETE) within a function or routine that is declared as read-only or has volatility restrictions preventing database modifications.

Quick fix

Fixes 2F002: modifying_sql_data_not_permitted

SELECT proname, provolatile, prosrc
FROM pg_proc
WHERE proname = 'your_function_name';
  1. Identify the problematic function
  2. Check function volatility classification
  3. Change function to VOLATILE if it modifies data
  4. Remove data modifications from STABLE/IMMUTABLE functions
postgresql — error
SELECT proname, provolatile, prosrc
FROM pg_proc
WHERE proname = 'your_function_name';
2F002: modifying_sql_data_not_permitted

What this error means

The 2F002 error code belongs to class 2F "SQL Routine Exception" and specifically indicates that a SQL routine (function, procedure, or trigger) attempted to modify database data when such modifications are prohibited by its declaration. PostgreSQL functions have volatility classifications (VOLATILE, STABLE, or IMMUTABLE) that determine their behavior and optimization characteristics. STABLE and IMMUTABLE functions are restricted from modifying the database - they can only read data. When a function declared as STABLE or IMMUTABLE attempts to execute INSERT, UPDATE, DELETE, or other data-modifying statements, PostgreSQL raises this error to enforce these constraints. This error can also occur in external routines or when using SQL/MED (Management of External Data) where the routine's characteristics explicitly prohibit data modifications. The restriction exists to allow the query optimizer to safely optimize query plans based on the function's declared behavior.

Typical symptoms

  • Error message "2F002: modifying_sql_data_not_permitted" when calling a function
  • Functions containing INSERT, UPDATE, or DELETE statements fail unexpectedly
  • Stored procedures that worked previously now fail after volatility changes
  • External routines raise exceptions when attempting database writes
  • Query optimizer errors when functions try to modify data

Common causes

  1. Function declared as STABLE attempting to execute INSERT, UPDATE, or DELETE statements
  2. Function declared as IMMUTABLE trying to modify database tables
  3. External routine with READS SQL DATA characteristic attempting data modifications
  4. Trigger function incorrectly marked as non-volatile while performing writes
  5. Function volatility changed from VOLATILE to STABLE/IMMUTABLE without updating function body
  6. Security-definer function with restrictive SQL characteristics attempting modifications

How to fix "2F002: modifying_sql_data_not_permitted"

1Identify the problematic function

First, locate which function is causing the error. The error message should indicate the function name. Query the function definition:

sql
SELECT proname, provolatile, prosrc
FROM pg_proc
WHERE proname = 'your_function_name';

The provolatile column shows: 'v' (VOLATILE), 's' (STABLE), or 'i' (IMMUTABLE).

2Check function volatility classification

Examine the function definition to see its declared volatility:

sql
-- View the complete function definition
\df+ your_function_name

Look for keywords STABLE, IMMUTABLE, or VOLATILE in the function declaration. If missing, the function defaults to VOLATILE.

3Change function to VOLATILE if it modifies data

If your function needs to modify data, it must be declared as VOLATILE. Recreate the function with the correct volatility:

sql
-- Drop and recreate with VOLATILE
CREATE OR REPLACE FUNCTION your_function_name(param1 type1)
RETURNS return_type
LANGUAGE plpgsql
VOLATILE  -- Allow data modifications
AS $$
BEGIN
  -- Your INSERT/UPDATE/DELETE statements here
  INSERT INTO your_table (column1) VALUES (param1);
  RETURN result;
END;
$$;

VOLATILE is the default and safest option for functions that modify data.

4Remove data modifications from STABLE/IMMUTABLE functions

If the function should remain STABLE or IMMUTABLE, remove any data-modifying statements:

sql
-- For read-only functions, use only SELECT statements
CREATE OR REPLACE FUNCTION calculate_total(order_id int)
RETURNS numeric
LANGUAGE plpgsql
STABLE  -- Read-only, consistent within query
AS $$
DECLARE
  total numeric;
BEGIN
  SELECT SUM(amount) INTO total
  FROM order_items
  WHERE order_id = $1;

  RETURN total;
END;
$$;

STABLE functions can read data but cannot modify it.

5Verify and test the corrected function

After making changes, test the function to ensure it works correctly:

sql
-- Test the function
SELECT your_function_name(test_param);

-- Verify volatility setting
SELECT proname,
       CASE provolatile
         WHEN 'v' THEN 'VOLATILE'
         WHEN 's' THEN 'STABLE'
         WHEN 'i' THEN 'IMMUTABLE'
       END as volatility
FROM pg_proc
WHERE proname = 'your_function_name';

Ensure the function executes without the 2F002 error and returns expected results.

Advanced notes

Understanding Function Volatility Classifications:

- VOLATILE (default): Can modify the database and may return different results on successive calls with the same arguments. Use this for any function that performs INSERT, UPDATE, DELETE, or calls other VOLATILE functions.

- STABLE: Cannot modify the database. Guaranteed to return the same results for the same arguments within a single statement. Useful for functions that read current database state but don't change it (e.g., date/time calculations with current values).

- IMMUTABLE: Cannot modify the database and must always return the same result for the same arguments forever. Only use for pure functions like mathematical calculations (e.g., sqrt(25) always returns 5).

Performance Implications:

The query optimizer uses volatility information to make optimization decisions. Incorrectly marking a VOLATILE function as STABLE or IMMUTABLE can lead to incorrect query results because the optimizer may evaluate the function fewer times than necessary. Conversely, marking a STABLE function as VOLATILE prevents certain optimizations.

Security Considerations:

Functions defined with SECURITY DEFINER execute with the privileges of the function owner. If such functions are marked STABLE or IMMUTABLE but need to modify data, they will fail with 2F002. Ensure SECURITY DEFINER functions that modify data are correctly marked as VOLATILE.

External Routines and SQL/MED:

When working with foreign data wrappers or external routines, the routine characteristics (READS SQL DATA, MODIFIES SQL DATA) must match the actual operations performed. Violating these declarations triggers the 2F002 error to maintain consistency guarantees.

Frequently asked questions

What does "2F002: modifying_sql_data_not_permitted" mean?
The 2F002 error code belongs to class 2F "SQL Routine Exception" and specifically indicates that a SQL routine (function, procedure, or trigger) attempted to modify database data when such modifications are prohibited by its declaration. PostgreSQL functions have volatility classifications (VOLATILE, STABLE, or IMMUTABLE) that determine their behavior and optimization characteristics. STABLE and IMMUTABLE functions are restricted from modifying the database - they can only read data.
How do I fix "2F002: modifying_sql_data_not_permitted"?
PostgreSQL error 2F002 occurs when attempting to execute data-modifying statements (INSERT, UPDATE, DELETE) within a function or routine that is declared as read-only or has volatility restrictions preventing database modifications.
What causes "2F002: modifying_sql_data_not_permitted"?
Function declared as STABLE attempting to execute INSERT, UPDATE, or DELETE statements Function declared as IMMUTABLE trying to modify database tables External routine with READS SQL DATA characteristic attempting data modifications
Is the "2F002: modifying_sql_data_not_permitted" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading