PostgreSQL 2F004 Error: Causes and Solutions Complete Guide

PostgreSQL Error 2F004: Reading SQL Data Not Permitted

PostgreSQL error 2F004 (reading_sql_data_not_permitted) occurs when a function or procedure attempts to read SQL data but is declared with a data access level that prohibits it — typically NO SQL or CONTAINS SQL. This is a runtime error belonging to error class 2F (SQL Routine Exception) and is most commonly encountered in PL/pgSQL, PL/Python, or PL/Java functions where the function declaration and its actual behavior are mismatched.

---

Top 3 Causes

1. Function Declared with NO SQL While Containing a SELECT Statement

The most common cause is a straightforward mismatch: a developer declares a function with NO SQL for performance reasons, then later adds a SELECT query inside the body.

-- ❌ Problematic: NO SQL declared but SELECT used inside
CREATE OR REPLACE FUNCTION get_product_price(p_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
NO SQL  -- This declaration forbids reading SQL data
AS $$
DECLARE
    v_price NUMERIC;
BEGIN
    -- This SELECT will trigger error 2F004
    SELECT price INTO v_price
    FROM products
    WHERE product_id = p_id;
    RETURN v_price;
END;
$$;

-- ✅ Fix: Change to READS SQL DATA
CREATE OR REPLACE FUNCTION get_product_price(p_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
READS SQL DATA
STABLE
AS $$
DECLARE
    v_price NUMERIC;
BEGIN
    SELECT price INTO v_price
    FROM products
    WHERE product_id = p_id;
    RETURN v_price;
END;
$$;

2. Nested Function Calls with Conflicting Access Levels

When a function declared as NO SQL calls another function that internally reads SQL data, PostgreSQL detects the conflict across the entire call stack and raises 2F004.

-- Inner function reads SQL data
CREATE OR REPLACE FUNCTION fetch_discount(p_customer_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
READS SQL DATA
AS $$
DECLARE v_discount NUMERIC;
BEGIN
    SELECT discount_rate INTO v_discount
    FROM customer_tiers
    WHERE customer_id = p_customer_id;
    RETURN COALESCE(v_discount, 0);
END;
$$;

-- ❌ Outer function is NO SQL but calls a READS SQL DATA function
CREATE OR REPLACE FUNCTION calculate_total(p_amount NUMERIC, p_customer_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
NO SQL  -- ❌ Conflict: inner call reads SQL data
AS $$
BEGIN
    RETURN p_amount * (1 - fetch_discount(p_customer_id));
END;
$$;

-- ✅ Fix: Align the access level of the outer function
CREATE OR REPLACE FUNCTION calculate_total(p_amount NUMERIC, p_customer_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
READS SQL DATA  -- ✅ Matches the behavior of nested calls
AS $$
BEGIN
    RETURN p_amount * (1 - fetch_discount(p_customer_id));
END;
$$;

3. Trigger Functions with Overly Restrictive Access Declarations

Developers sometimes add NO SQL to trigger functions aiming to optimize performance, but trigger logic often needs to query lookup or reference tables, which immediately causes 2F004.

-- ❌ Trigger function incorrectly declared with NO SQL
CREATE OR REPLACE FUNCTION trg_check_inventory()
RETURNS TRIGGER
LANGUAGE plpgsql
NO SQL  -- ❌ Wrong: we need to SELECT from inventory table
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM inventory WHERE sku = NEW.sku AND quantity > 0
    ) THEN
        RAISE EXCEPTION 'SKU % is out of stock', NEW.sku;
    END IF;
    RETURN NEW;
END;
$$;

-- ✅ Fix: Remove the restrictive declaration (default is safe)
CREATE OR REPLACE FUNCTION trg_check_inventory()
RETURNS TRIGGER
LANGUAGE plpgsql
-- No explicit data access restriction = defaults to VOLATILE, which allows SQL reads
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM inventory WHERE sku = NEW.sku AND quantity > 0
    ) THEN
        RAISE EXCEPTION 'SKU % is out of stock', NEW.sku;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER check_inventory_trigger
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION trg_check_inventory();

---

Quick Fix Solutions

1. Identify the offending function by checking pg_proc for mismatched declarations:

-- Find IMMUTABLE/STABLE functions that contain SELECT statements
SELECT
    n.nspname AS schema,
    p.proname AS function_name,
    CASE p.provolatile
        WHEN 'i' THEN 'IMMUTABLE'
        WHEN 's' THEN 'STABLE'
        WHEN 'v' THEN 'VOLATILE'
    END AS volatility
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public'
  AND p.prosrc ILIKE '%SELECT%'
ORDER BY p.proname;

1. Use CREATE OR REPLACE FUNCTION with the correct data access level (READS SQL DATA) without dropping and recreating the function.

2. For existing functions, use ALTER FUNCTION where applicable to adjust volatility settings quickly.

---

Prevention Tips

• Establish a team standard: Always explicitly declare READS SQL DATA for any function that contains SELECT, and MODIFIES SQL DATA for functions that perform DML. Avoid defaulting to NO SQL as a premature optimization.
• Automate detection in CI/CD: Add a pre-deployment SQL script that queries pg_catalog.pg_proc to verify that function declarations match their actual SQL usage patterns, catching 2F004 issues before they reach production.

---

Related Errors

• 2F000 – Generic SQL routine exception (parent class of 2F004)
• 2F002 – modifying_sql_data_not_permitted: triggered when a READS SQL DATA function tries to run DML
• 2F003 – prohibited_sql_statement_attempted: raised when disallowed SQL statements appear inside a function context

---

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