How to diagnose and fix the 2F002 modifying_sql_data_not_permitted error code in Postgres.

by

The 2F002 error code in PostgreSQL stands for modifying_sql_data_not_permitted. This error typically occurs when an attempt is made to modify the database within a function or trigger that is declared as READ ONLY. The SQL standard defines certain contexts where data modification is not allowed, and PostgreSQL enforces this rule to maintain data integrity and the expected behavior of functions and triggers.

Here are some examples of situations that can raise this error, along with professional guidance on how to diagnose and resolve the issue:

Example 1: Data Modification Inside a READ ONLY Function

If you create a function with the READ ONLY transaction mode and try to perform data modification operations like INSERT, UPDATE, or DELETE within it, you will encounter the 2F002 error.

Diagnosis:

Check the function definition to see if it is declared as READ ONLY.

-- Example of a function definition that might cause the error
CREATE OR REPLACE FUNCTION read_only_function()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  -- Attempt to modify data
  DELETE FROM my_table WHERE id = 1;
END;
$$ READ ONLY;

Fix:

If data modification is intended, change the function’s transaction mode to READ WRITE.

-- Corrected function definition
CREATE OR REPLACE FUNCTION read_write_function()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  -- Now the data modification is permitted
  DELETE FROM my_table WHERE id = 1;
END;
$$ READ WRITE;

Example 2: Data Modification Inside a READ ONLY Trigger Function

Similar to functions, if a trigger function is defined as READ ONLY and tries to modify data in other tables, PostgreSQL will raise the 2F002 error.

Diagnosis:

Examine the trigger function to determine if it’s attempting to modify data and if it’s declared as READ ONLY.

-- Example of a trigger function that could cause the error
CREATE OR REPLACE FUNCTION read_only_trigger_function()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  -- Attempt to modify data in another table
  INSERT INTO audit_log (action) VALUES ('DELETE ATTEMPT');
  RETURN OLD;
END;
$$ READ ONLY;

Fix:

To fix this, you need to ensure the trigger function is not declared as READ ONLY.

-- Corrected trigger function
CREATE OR REPLACE FUNCTION read_write_trigger_function()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  -- Now inserting into the audit_log table is allowed
  INSERT INTO audit_log (action) VALUES ('DELETE ATTEMPT');
  RETURN OLD;
END;
$$;

Example 3: Data Modification Inside a READ ONLY Transaction Block

If you explicitly start a transaction with READ ONLY mode and then try to modify data, PostgreSQL will throw the 2F002 error.

Diagnosis:

Check your transaction blocks to ensure they are not marked as READ ONLY if you intend to modify data.

-- Starting a read-only transaction
BEGIN READ ONLY;

-- Attempting to modify data will result in an error
UPDATE my_table SET column = 'value' WHERE id = 1;

Fix:

Start the transaction as READ WRITE or without specifying the mode (as it defaults to READ WRITE).

-- Starting a read-write transaction
BEGIN READ WRITE;

-- Now data modification is allowed
UPDATE my_table SET column = 'value' WHERE id = 1;

COMMIT;

In summary, to diagnose and fix the 2F002 error:

  1. Review the context in which the error occurred, whether it’s within a function, trigger, or transaction block.
  2. Check the definitions or declarations for READ ONLY constraints.
  3. Modify the function, trigger, or transaction block to allow for data modification by removing the READ ONLY constraint or changing it to READ WRITE.
  4. Test the changes to ensure that the error is resolved and the intended behavior is achieved.

Leave a Comment