How to fix 2F003: prohibited_sql_statement_attempted in PostgreSQL

PostgreSQLADVANCEDMEDIUMUpdated Dec 22, 2025· 165 views

PostgreSQL error 2F003 occurs when a SQL routine (function, procedure, or trigger) attempts to execute a statement that is prohibited in its current context, such as transaction control commands (COMMIT, ROLLBACK) or DDL statements within restricted function types.

Quick fix

Fixes 2F003

-- View the function source code
SELECT proname, prosrc
FROM pg_proc
WHERE proname = 'your_function_name';

-- Or use psql command
\df+ your_function_name
  1. Identify the prohibited statement in the function
  2. Convert function to procedure if using transaction control
  3. Remove DDL statements or mark function as VOLATILE
  4. Use exception blocks instead of explicit ROLLBACK
postgresql — error
-- View the function source code
SELECT proname, prosrc
FROM pg_proc
WHERE proname = 'your_function_name';
-- Or use psql command
\df+ your_function_name
2F003: prohibited_sql_statement_attempted

What this error means

The 2F003 error code belongs to class 2F "SQL Routine Exception" and indicates that a prohibited SQL statement was attempted within an external routine or function. This error occurs when you try to execute certain SQL statements that are not allowed within the specific context of a function or procedure. PostgreSQL restricts certain types of SQL statements within functions, particularly those that would interfere with transaction control or violate the function's declared characteristics. Common prohibited operations include transaction control statements (COMMIT, ROLLBACK, SAVEPOINT) in regular functions, DDL statements (CREATE TABLE, ALTER TABLE, DROP TABLE) in IMMUTABLE or STABLE functions, and utility commands that don't fit the function's declared behavior. This error serves as a safeguard to maintain PostgreSQL's ACID guarantees and ensure that functions behave consistently with their declared characteristics. The database engine enforces these restrictions to prevent operations that could compromise transaction integrity, violate isolation levels, or interfere with the query optimizer's assumptions about function behavior.

Typical symptoms

  • Error message "2F003: prohibited_sql_statement_attempted" when calling a function
  • Functions containing COMMIT or ROLLBACK statements fail immediately
  • Procedures with transaction control fail in non-procedure contexts
  • DDL statements within functions raise unexpected errors
  • CREATE INDEX or ALTER TABLE operations fail inside function bodies
  • Utility commands like VACUUM or ANALYZE fail when called from functions

Common causes

  1. Attempting COMMIT or ROLLBACK within a regular PL/pgSQL function (only allowed in procedures)
  2. Executing DDL statements (CREATE, ALTER, DROP) within IMMUTABLE or STABLE functions
  3. Using SAVEPOINT or ROLLBACK TO SAVEPOINT in inappropriate function contexts
  4. Attempting transaction control in functions called within larger transactions
  5. Executing utility commands (VACUUM, ANALYZE, REINDEX) from within functions
  6. Using SET LOCAL or similar session-altering commands in restricted functions
  7. Calling functions with prohibited statements from within SQL queries

How to fix "2F003: prohibited_sql_statement_attempted"

1Identify the prohibited statement in the function

First, locate the specific SQL statement causing the error. Examine your function definition:

sql
-- View the function source code
SELECT proname, prosrc
FROM pg_proc
WHERE proname = 'your_function_name';

-- Or use psql command
\df+ your_function_name

Look for transaction control statements (COMMIT, ROLLBACK, SAVEPOINT) or DDL statements (CREATE, ALTER, DROP) in the function body.

2Convert function to procedure if using transaction control

If your code needs COMMIT or ROLLBACK, convert it from a FUNCTION to a PROCEDURE. Only procedures support transaction control:

sql
-- Instead of a function like this:
-- CREATE FUNCTION process_orders() RETURNS void AS $$
--   BEGIN
--     INSERT INTO orders_processed ...
--     COMMIT;  -- This causes 2F003 error
--   END;
-- $$ LANGUAGE plpgsql;

-- Use a PROCEDURE:
CREATE OR REPLACE PROCEDURE process_orders()
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO orders_processed (order_id, processed_at)
  SELECT order_id, NOW()
  FROM pending_orders;

  COMMIT;  -- Allowed in procedures

  DELETE FROM pending_orders WHERE processed = true;
  COMMIT;
END;
$$;

-- Call with CALL instead of SELECT:
CALL process_orders();

Note: Procedures are called with CALL, not SELECT, and cannot be used in SQL expressions.

3Remove DDL statements or mark function as VOLATILE

If your function contains DDL statements, ensure it's marked as VOLATILE and reconsider whether the DDL belongs in a function:

sql
-- If DDL is necessary, use VOLATILE
CREATE OR REPLACE FUNCTION create_user_table(username text)
RETURNS void
LANGUAGE plpgsql
VOLATILE  -- Required for DDL operations
AS $$
BEGIN
  EXECUTE format('CREATE TABLE IF NOT EXISTS %I (
    id SERIAL PRIMARY KEY,
    data jsonb
  )', username || '_data');
END;
$$;

Better approach: Move DDL to setup scripts or migration files rather than embedding in functions.

4Use exception blocks instead of explicit ROLLBACK

For error handling in functions, use PostgreSQL's exception handling mechanism rather than explicit transaction control:

sql
CREATE OR REPLACE FUNCTION safe_insert_order(order_data jsonb)
RETURNS integer
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  new_order_id integer;
BEGIN
  INSERT INTO orders (data, created_at)
  VALUES (order_data, NOW())
  RETURNING id INTO new_order_id;

  RETURN new_order_id;
EXCEPTION
  WHEN unique_violation THEN
    -- PostgreSQL automatically rolls back the failed statement
    -- No explicit ROLLBACK needed
    RAISE NOTICE 'Duplicate order detected';
    RETURN NULL;
  WHEN OTHERS THEN
    -- Log error and re-raise
    RAISE WARNING 'Order insert failed: %', SQLERRM;
    RAISE;
END;
$$;

PostgreSQL automatically rolls back failed statements within functions - explicit ROLLBACK is unnecessary and prohibited.

5Refactor utility commands outside of functions

Maintenance commands like VACUUM, ANALYZE, or REINDEX cannot run inside functions. Move them to separate scripts:

sql
-- Don't do this:
-- CREATE FUNCTION maintenance() RETURNS void AS $$
--   BEGIN
--     VACUUM orders;  -- Causes 2F003 error
--   END;
-- $$ LANGUAGE plpgsql;

-- Instead, create a separate maintenance script:
-- maintenance.sql
VACUUM ANALYZE orders;
REINDEX TABLE orders;

-- Or use a DO block for one-time operations:
DO $$
BEGIN
  RAISE NOTICE 'Starting maintenance...';
  -- Regular SQL statements only, no VACUUM/ANALYZE
END $$;

Schedule maintenance commands via cron, pg_cron extension, or external scripts rather than embedding in functions.

6Test the corrected function

After making changes, verify the function works correctly:

sql
-- For procedures, use CALL:
CALL process_orders();

-- For functions, use SELECT:
SELECT safe_insert_order('{"customer": "John", "total": 99.99}'::jsonb);

-- Verify function characteristics:
SELECT
  proname,
  prokind,  -- 'f' = function, 'p' = procedure
  provolatile,  -- 'v' = VOLATILE, 's' = STABLE, 'i' = IMMUTABLE
  prosrc
FROM pg_proc
WHERE proname = 'your_function_name';

Ensure no 2F003 errors occur and the routine behaves as expected.

Advanced notes

Function vs Procedure Transaction Control:

PostgreSQL introduced procedures in version 11, which support transaction control (COMMIT/ROLLBACK) unlike functions. Key differences:

- Functions: Cannot use COMMIT/ROLLBACK, automatically run within the caller's transaction, can be used in SQL expressions and return values
- Procedures: Support COMMIT/ROLLBACK, control their own transactions, called with CALL statement, no return value (use OUT parameters instead)

Autonomous Transactions Workaround:

PostgreSQL doesn't support true autonomous transactions like Oracle. If you need transaction isolation within a function, use dblink or pg_background extensions to execute commands in a separate connection:

sql
-- Using dblink for pseudo-autonomous transactions
CREATE EXTENSION IF NOT EXISTS dblink;

CREATE OR REPLACE FUNCTION log_audit(message text)
RETURNS void AS $$
BEGIN
  PERFORM dblink_exec('dbname=mydb', format(
    'INSERT INTO audit_log (message, logged_at) VALUES (%L, NOW())',
    message
  ));
END;
$$ LANGUAGE plpgsql;

DDL in Functions - Security Considerations:

Executing dynamic DDL (CREATE TABLE, ALTER TABLE) within functions poses security risks. If the function is SECURITY DEFINER, an attacker could potentially exploit SQL injection to create malicious database objects. Always:

- Use quote_ident() and quote_literal() to sanitize inputs
- Validate table/column names against whitelists
- Prefer static DDL in migration scripts over dynamic DDL in functions
- Consider whether the DDL truly needs to be in a function

Error Code Context:

The 2F class represents SQL Routine Exceptions:
- 2F000: General SQL routine exception
- 2F002: Modifying SQL data not permitted (data modification in read-only functions)
- 2F003: Prohibited SQL statement attempted (transaction control, DDL in inappropriate contexts)
- 2F004: Reading SQL data not permitted (rare, SQL/MED related)
- 2F005: Function executed no return statement

Understanding the distinction between these errors helps diagnose whether the issue is about data modification (2F002) or prohibited statement types (2F003).

Frequently asked questions

What does "2F003" mean?
The 2F003 error code belongs to class 2F "SQL Routine Exception" and indicates that a prohibited SQL statement was attempted within an external routine or function. This error occurs when you try to execute certain SQL statements that are not allowed within the specific context of a function or procedure. PostgreSQL restricts certain types of SQL statements within functions, particularly those that would interfere with transaction control or violate the function's declared characteristics.
How do I fix "2F003"?
PostgreSQL error 2F003 occurs when a SQL routine (function, procedure, or trigger) attempts to execute a statement that is prohibited in its current context, such as transaction control commands (COMMIT, ROLLBACK) or DDL statements within restricted function types.
What causes "2F003"?
Attempting COMMIT or ROLLBACK within a regular PL/pgSQL function (only allowed in procedures) Executing DDL statements (CREATE, ALTER, DROP) within IMMUTABLE or STABLE functions Using SAVEPOINT or ROLLBACK TO SAVEPOINT in inappropriate function contexts
Is the "2F003" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading