DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 38004 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 38004: Reading SQL Data Not Permitted

PostgreSQL error code 38004 (reading_sql_data_not_permitted) occurs when a function or stored procedure attempts to read SQL data, but its declared data access level explicitly prohibits such operations. This error belongs to the Class 38 — External Routine Exception category and is strictly enforced by PostgreSQL's SQL-standard compliance mechanisms. In short, the function's declaration and its actual behavior are in conflict.

Top 3 Causes and Fixes

Cause 1: Incorrect Data Access Level Declaration

The most common cause is declaring a function with CONTAINS SQL or NO SQL while the function body includes SELECT statements that read from tables.

Problematic code:

-- WRONG: CONTAINS SQL does not allow reading table data
CREATE OR REPLACE FUNCTION get_product_price(product_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
CONTAINS SQL
AS $$
DECLARE
    v_price NUMERIC;
BEGIN
    SELECT price INTO v_price
    FROM products
    WHERE id = product_id;
    RETURN v_price;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

Fix — Change to READS SQL DATA:

-- CORRECT: Explicitly allows reading SQL data
CREATE OR REPLACE FUNCTION get_product_price(product_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
READS SQL DATA
AS $$
DECLARE
    v_price NUMERIC;
BEGIN
    SELECT price INTO v_price
    FROM products
    WHERE id = product_id;
    RETURN v_price;
END;
$$;

-- Verify the fix
SELECT get_product_price(42);
Enter fullscreen mode Exit fullscreen mode

Cause 2: PL/Python or PL/Perl Functions with Wrong Access Level

Functions written in procedural languages like plpython3u or plperlu that execute SQL internally can trigger this error if their access level is incorrectly set to NO SQL.

-- WRONG: NO SQL conflicts with internal SQL execution
CREATE OR REPLACE FUNCTION py_fetch_users()
RETURNS INT
LANGUAGE plpython3u
NO SQL
AS $$
result = plpy.execute("SELECT COUNT(*) FROM users")
return result[0]['count']
$$;

-- CORRECT: Declare the proper access level
CREATE OR REPLACE FUNCTION py_fetch_users()
RETURNS INT
LANGUAGE plpython3u
READS SQL DATA
AS $$
result = plpy.execute("SELECT COUNT(*) FROM users")
return result[0]['count']
$$;
Enter fullscreen mode Exit fullscreen mode

Cause 3: Migrated Functions from Other Databases

Functions migrated from Oracle or other RDBMS systems often carry incompatible metadata. The access level declarations may not translate correctly during migration.

-- Diagnose all functions with potential mismatches
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    l.lanname AS language,
    pg_get_functiondef(p.oid) AS full_definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l ON p.prolang = l.oid
WHERE n.nspname = 'public'
  AND pg_get_functiondef(p.oid) ILIKE '%SELECT%'
  AND pg_get_functiondef(p.oid) NOT ILIKE '%READS SQL DATA%'
ORDER BY p.proname;

-- Fix a migrated function
CREATE OR REPLACE FUNCTION legacy_get_order_total(order_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
READS SQL DATA  -- Add this explicitly after migration
AS $$
DECLARE
    v_total NUMERIC;
BEGIN
    SELECT SUM(unit_price * quantity)
    INTO v_total
    FROM order_items
    WHERE order_id = order_id;
    RETURN COALESCE(v_total, 0);
END;
$$;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Access Level Allows Use When
NO SQL Nothing Pure computation, no DB access
CONTAINS SQL Non-data SQL (e.g., SET) Control statements only
READS SQL DATA SELECT Function reads but doesn't modify data
MODIFIES SQL DATA All DML Function inserts/updates/deletes

Prevention Tips

  1. Always explicitly declare data access levels when creating functions. Never rely on defaults — add access level review to your code review checklist and CI/CD pipeline.

  2. Run a pre-deployment validation query to catch mismatches before they reach production:

-- Pre-deployment check: flag functions reading data without proper declaration
SELECT 
    proname AS function_name,
    pg_get_functiondef(oid) AS definition
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace
  AND pg_get_functiondef(oid) ILIKE '%SELECT%'
  AND pg_get_functiondef(oid) NOT ILIKE '%READS SQL DATA%'
  AND pg_get_functiondef(oid) NOT ILIKE '%MODIFIES SQL DATA%';
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • 38001 — containing_sql_not_permitted: SQL execution is not allowed at all in the current context.
  • 38002 — modifying_sql_data_not_permitted: Similar to 38004 but triggered when a function tries to modify data (INSERT/UPDATE/DELETE) without the appropriate access level.
  • 38000 — external_routine_exception: The parent class for all 38xxx errors.

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