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.
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.
First, locate which function is causing the error. The error message should indicate the function name. Query the function definition:
SELECT proname, provolatile, prosrc
FROM pg_proc
WHERE proname = 'your_function_name';The provolatile column shows: 'v' (VOLATILE), 's' (STABLE), or 'i' (IMMUTABLE).
Examine the function definition to see its declared volatility:
-- View the complete function definition
\df+ your_function_nameLook for keywords STABLE, IMMUTABLE, or VOLATILE in the function declaration. If missing, the function defaults to VOLATILE.
If your function needs to modify data, it must be declared as VOLATILE. Recreate the function with the correct volatility:
-- 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.
If the function should remain STABLE or IMMUTABLE, remove any data-modifying statements:
-- 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.
After making changes, test the function to ensure it works correctly:
-- 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.
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.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL