PostgreSQL Reading SQL Data Not Permitted (SQLSTATE 2F004)

PostgreSQL raises ERROR: reading SQL data not permitted with SQLSTATE 2F004 and condition name reading_sql_data_not_permitted when a function or procedure attempts to execute a SQL statement that reads from the database, but the function was declared with a NO SQL data access level that prohibits such operations.

What This Error Means

SQLSTATE 2F004 belongs to error class 2F, which covers SQL routine exception errors — problems that arise from violations of declared contracts on functions and procedures. The specific condition reading_sql_data_not_permitted is the counterpart to 2F003 (prohibited_sql_statement_attempted) and signals that a read operation (such as a SELECT) was attempted inside a routine that declared it would not access SQL data at all.

When you create a function in PostgreSQL, you can declare its data access level using one of four options: NO SQL, CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA. These declarations serve as a contract that PostgreSQL (or the underlying language handler) can use for optimization, planning, and security enforcement. NO SQL is the most restrictive: it asserts the function neither reads nor writes any SQL data. When PostgreSQL or a calling context enforces this contract and a SELECT or similar read is attempted inside such a function, it raises 2F004.

In practice, this error is most commonly encountered with external procedural languages (such as PL/Java or external C functions registered with strict data access declarations) or in environments that validate function access levels at call time. In pure PL/pgSQL, PostgreSQL does not automatically enforce NO SQL at runtime, so this error is less common there but can still be surfaced by strict language handlers or wrapper layers.

Common Causes

1. Function declared NO SQL contains a SELECT statement. A function was created with LANGUAGE ... NO SQL (or an equivalent declaration) in its definition but its body includes a query that reads from a table or view.

2. External language function with incorrect data access level. A PL/Java, PL/Perl, or custom C extension function was registered with NO SQL in its CREATE FUNCTION statement, but the implementation performs a SQL read (e.g., via a JDBC connection or SPI call).

3. Wrapper or middleware enforcement. A connection pooler, proxy, or middleware layer that parses and enforces function metadata raises this error when it detects a mismatch between the declared access level and the observed SQL operations.

4. Copy-paste or template error in function definition. A developer copied a boilerplate CREATE FUNCTION template that included NO SQL without realizing the function body actually reads data.

How to Fix reading_sql_data_not_permitted

1. Change the data access level to READS SQL DATA. If the function legitimately needs to read from the database, update the function definition to reflect that:

-- Before (incorrect declaration)
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS integer
LANGUAGE plpgsql
NO SQL
AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM users);
END;
$$;

-- After (correct declaration)
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS integer
LANGUAGE plpgsql
READS SQL DATA
AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM users);
END;
$$;

2. Remove SQL reads from the function body. If the function is intended to be NO SQL (for performance or security reasons), refactor it to avoid any database reads. Pure computational functions — those working only with their input arguments — are valid NO SQL candidates:

-- Valid NO SQL function: pure computation
CREATE OR REPLACE FUNCTION add_values(a integer, b integer)
RETURNS integer
LANGUAGE plpgsql
NO SQL
AS $$
BEGIN
  RETURN a + b;
END;
$$;

3. Audit external language function declarations. For PL/Java, PL/Perl, or C functions, review both the CREATE FUNCTION SQL declaration and the underlying implementation. Make sure the NO SQL / READS SQL DATA / MODIFIES SQL DATA clause in the SQL definition matches what the code actually does:

-- Check existing function definitions
SELECT proname, prosrc, provolatile
FROM pg_proc
WHERE proname = 'your_function_name';

4. Check middleware and ORM configuration. If the error comes from a proxy or ORM rather than PostgreSQL itself, verify how function metadata is being read and enforced. You may need to annotate or re-register the function with the correct access level in that layer.

Additional Information

• SQLSTATE class 2F (SQL routine exception) contains several related conditions: 2F000 (sql_routine_exception, generic), 2F002 (modifying_sql_data_not_permitted), 2F003 (prohibited_sql_statement_attempted), and 2F005 (function_executed_no_return_value). These all relate to violations of a function's declared behavioral contract.
• The NO SQL, CONTAINS SQL, READS SQL DATA, and MODIFIES SQL DATA keywords come from the SQL standard and are most meaningfully enforced in external procedural language handlers. PostgreSQL's built-in PL/pgSQL is generally permissive about these declarations.
• In PostgreSQL, the provolatile column in pg_proc (i = immutable, s = stable, v = volatile) is related but distinct from the data access level. An IMMUTABLE function should not read from tables, but marking it NO SQL is a separate, explicit contract.
• Some PostgreSQL connection poolers and audit extensions enforce data access levels and may surface this error even when native PostgreSQL would not.

Frequently Asked Questions

Why doesn't PostgreSQL always raise 2F004 when a NO SQL function reads data? PostgreSQL's PL/pgSQL handler does not enforce the NO SQL declaration at runtime by default. The declaration exists for documentation, optimizer hints, and external tool consumption. Strict enforcement is the responsibility of the language handler or a calling context. External languages like PL/Java do enforce these constraints, which is why 2F004 is most commonly seen with non-native procedural languages.

What is the difference between 2F003 and 2F004? 2F003 (prohibited_sql_statement_attempted) is raised when any SQL statement is attempted in a context where no SQL is allowed (for example, a DDL statement inside a restricted function). 2F004 (reading_sql_data_not_permitted) is specifically about read operations (SELECT, cursor opens) being attempted in a function declared NO SQL or in a context that only permits write operations.

Does changing NO SQL to READS SQL DATA affect query planning or performance? It can. The optimizer uses data access level declarations for planning and caching decisions. IMMUTABLE functions with NO SQL can be evaluated at planning time and their results cached aggressively. Changing to READS SQL DATA (especially with STABLE or VOLATILE) tells the optimizer the function may return different results and limits how aggressively it can cache or inline the result.

How do I find all functions in my database declared with NO SQL? You can query pg_proc for functions with the data access annotation. Note that in PostgreSQL, the NO SQL / READS SQL DATA distinctions are stored in the prokind and language-specific metadata rather than a single column, but you can inspect function sources:

SELECT n.nspname AS schema, p.proname AS function_name, l.lanname AS language
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l ON p.prolang = l.oid
WHERE p.prosrc ILIKE '%NO SQL%'
   OR p.proconfig::text ILIKE '%no_sql%'
ORDER BY schema, function_name;