How to diagnose and fix the 2F004 reading_sql_data_not_permitted error code in Postgres.

by

The 2F004 error code in PostgreSQL, reading_sql_data_not_permitted, occurs when a function that is declared to perform no SQL data reading (i.e., it is marked as NO SQL or MODIFIES SQL DATA) attempts to execute an SQL command that reads data from the database. This is a violation of the function’s declared behavior and PostgreSQL enforces this restriction to maintain the integrity of function volatility classifications.

Here are several examples of situations that might cause this error, along with explanations and solutions:

Example 1: Function Declared as NO SQL Trying to Read Data

When a function is declared with NO SQL, it means the function contains no SQL commands at all. Attempting to read data with SQL inside such a function will result in the 2F004 error.

-- Incorrect function declaration that causes error 2F004
CREATE OR REPLACE FUNCTION no_sql_function()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    -- Attempt to read data
    PERFORM * FROM my_table; -- This causes an error because the function is NO SQL
END;
$$ NO SQL;

-- Correct approach
-- Remove the NO SQL declaration if the function needs to read data
CREATE OR REPLACE FUNCTION no_sql_function()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    -- Now it is allowed to read data
    PERFORM * FROM my_table;
END;
$$;

Example 2: Function Declared as MODIFIES SQL DATA Trying to Read Data

A function declared with MODIFIES SQL DATA is expected to modify data but not necessarily read data. If you need to read data, the function should not be declared with MODIFIES SQL DATA.

-- Incorrect function declaration that causes error 2F004
CREATE OR REPLACE FUNCTION modifies_sql_data_function()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    -- Attempt to read data
    PERFORM * FROM my_table; -- This causes an error because the function is MODIFIES SQL DATA
END;
$$ MODIFIES SQL DATA;

-- Correct approach
-- Use the appropriate declaration (e.g., STABLE or VOLATILE) if the function needs to read data
CREATE OR REPLACE FUNCTION modifies_sql_data_function()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    -- Now it is allowed to read data
    PERFORM * FROM my_table;
END;
$$;

Example 3: Function Declared as CONTAINS SQL or READS SQL DATA

If a function is declared as CONTAINS SQL or READS SQL DATA, it is allowed to read data but not modify it. Ensure that the function’s behavior matches its declaration.

-- Correct function declaration that allows reading data
CREATE OR REPLACE FUNCTION reads_sql_data_function()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    -- Reading data is allowed
    PERFORM * FROM my_table;
END;
$$ READS SQL DATA;

To fix the 2F004 error, you should:

  1. Check the function definition to understand its declared behavior in terms of data access (NO SQL, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA).
  2. Modify the function’s SQL data access declaration to match its actual behavior. If the function needs to read data, do not declare it as NO SQL or MODIFIES SQL DATA.
  3. Review all SQL commands within the function to ensure they comply with the function’s declared data access classification.

Understanding the different function volatility and data access options in PostgreSQL is crucial for writing correct and optimized code. Functions should be declared with the least permissive option that still allows them to operate as intended, to help PostgreSQL optimize execution.

For more information on function volatility and data access declarations in PostgreSQL, you can refer to the official PostgreSQL documentation on function volatility categories. If you encounter difficulties in resolving the issue, you might consider reaching out to a database specialist or seeking advice from the PostgreSQL community through various forums or support channels.

Leave a Comment