DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 2F002 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2F002: modifying sql data not permitted

PostgreSQL error code 2F002 occurs when a function attempts to modify SQL data (via INSERT, UPDATE, DELETE, or TRUNCATE) in a context where such modifications are not permitted. This typically happens when a function is declared with a restrictive SQL data access attribute like READS SQL DATA, or when it is invoked within a read-only transaction context. Understanding the root cause is essential to resolving it quickly without introducing regressions.

Top 3 Causes

1. Function Declared with Incompatible Data Access Attribute

When a function is declared as READS SQL DATA but contains DML statements internally, PostgreSQL will raise 2F002 at execution time. The declared attribute and the actual behavior must match.

-- Problematic function declaration
CREATE OR REPLACE FUNCTION deactivate_user(p_id INT)
RETURNS VOID
LANGUAGE plpgsql
READS SQL DATA  -- conflicts with the UPDATE below
AS $$
BEGIN
    UPDATE users SET active = false WHERE user_id = p_id;
END;
$$;

-- Fixed version: remove restrictive attribute
CREATE OR REPLACE FUNCTION deactivate_user(p_id INT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET active = false WHERE user_id = p_id;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

2. DML Attempted Inside a Read-Only Transaction

Starting a transaction with READ ONLY and then calling a function that modifies data will trigger this error. This is especially common when connection poolers or ORMs silently set transactions to read-only mode.

-- This will cause error 2F002
BEGIN READ ONLY;
UPDATE orders SET status = 'shipped' WHERE order_id = 99;
-- ERROR: cannot execute UPDATE in a read-only transaction

-- Fix: use READ WRITE explicitly
BEGIN;
SET TRANSACTION READ WRITE;
UPDATE orders SET status = 'shipped' WHERE order_id = 99;
COMMIT;

-- Check if connected to a standby (read-only replica)
SELECT pg_is_in_recovery();
-- Returns true on standby → reconnect to primary for writes
Enter fullscreen mode Exit fullscreen mode

3. Incorrect Trigger or View Context

When a function is called through a view or trigger chain that enforces a read-only context, any DML inside the function will fail. Properly structured INSTEAD OF triggers are the correct approach for modifiable views.

-- Create a view
CREATE VIEW vw_active_products AS
    SELECT product_id, name, price
    FROM products
    WHERE is_active = true;

-- Create an INSTEAD OF trigger function (no restrictive attributes)
CREATE OR REPLACE FUNCTION fn_update_active_product()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE products
    SET name  = NEW.name,
        price = NEW.price
    WHERE product_id = OLD.product_id;
    RETURN NEW;
END;
$$;

-- Attach the trigger
CREATE TRIGGER trg_update_active_product
INSTEAD OF UPDATE ON vw_active_products
FOR EACH ROW EXECUTE FUNCTION fn_update_active_product();
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  • ✅ Remove READS SQL DATA from functions that perform DML
  • ✅ Confirm the transaction is not in READ ONLY mode (SHOW transaction_read_only;)
  • ✅ Verify you are connected to the primary server, not a standby replica
  • ✅ Audit INSTEAD OF triggers on views to ensure no restrictive attributes are set

Prevention Tips

Standardize function attributes in your DDL templates. Always explicitly document and review the data access level of every function before deploying to production. Include a linting step in your CI/CD pipeline that flags functions with READS SQL DATA containing DML keywords.

Separate read and write connection strings at the application level. Route SELECT-only workloads to read replicas and DML workloads strictly to the primary. This architectural boundary prevents accidental write attempts on read-only nodes and makes 2F002-style errors easy to identify and isolate.

Related Error Codes

  • 2F000 – sql_routine_exception: Parent category for all SQL routine errors including 2F002
  • 25006 – read_only_sql_transaction: Raised when DML is attempted in a read-only transaction at the transaction level, closely related to 2F002
  • 2F003 – prohibited_sql_statement_attempted: Triggered when a disallowed SQL statement is executed inside a SQL function

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