PostgreSQL 38003 Error: Causes and Solutions Complete Guide

PostgreSQL Error 38003: prohibited sql statement attempted

PostgreSQL error code 38003 (prohibited_sql_statement_attempted) occurs when a SQL statement is executed inside a function or procedural language block that the current execution context does not permit. This typically happens when a function declared as IMMUTABLE or STABLE attempts to execute data-modifying statements, or when transaction control commands are misused inside a regular function.

---

Top 3 Causes

1. DML Inside an IMMUTABLE or STABLE Function

Declaring a function as IMMUTABLE or STABLE tells PostgreSQL that the function won't modify the database. Attempting any write operation inside such a function immediately triggers error 38003.

-- BAD: IMMUTABLE function attempting a write
CREATE OR REPLACE FUNCTION bad_update(p_id INT)
RETURNS VOID
LANGUAGE plpgsql
IMMUTABLE  -- incorrect declaration
AS $$
BEGIN
    -- ERROR 38003 will be raised here
    UPDATE orders SET status = 'done' WHERE id = p_id;
END;
$$;

-- GOOD: Use VOLATILE for data-modifying functions
CREATE OR REPLACE FUNCTION good_update(p_id INT)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
    UPDATE orders SET status = 'done' WHERE id = p_id;
END;
$$;

-- Check existing function volatility
SELECT proname,
       CASE provolatile
           WHEN 'i' THEN 'IMMUTABLE'
           WHEN 's' THEN 'STABLE'
           WHEN 'v' THEN 'VOLATILE'
       END AS volatility
FROM pg_proc
WHERE proname = 'bad_update';

2. Write Operation on a Read-Only Connection or Replica

When an application is connected to a Hot Standby replica or a read-only transaction, any write-capable function call will fail with this error. This is especially common in read/write splitting setups where a write function is accidentally routed to a read-only pool.

-- Check if the current server is a read-only replica
SELECT pg_is_in_recovery();
-- Returns TRUE on a standby (read-only) server

-- Check if the current transaction is read-only
SHOW transaction_read_only;

-- Explicitly set a transaction as read-write before calling write functions
BEGIN;
SET TRANSACTION READ WRITE;
SELECT good_update(42);
COMMIT;

3. COMMIT/ROLLBACK Inside a FUNCTION (Not a PROCEDURE)

Using transaction control statements like COMMIT or ROLLBACK directly inside a PL/pgSQL FUNCTION is not supported in most contexts. PostgreSQL 11+ introduced PROCEDURE specifically to handle this use case.

-- BAD: Using COMMIT inside a FUNCTION
CREATE OR REPLACE FUNCTION bad_commit_func()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO logs(msg) VALUES ('step 1');
    COMMIT;  -- raises 38003 in a FUNCTION context
END;
$$;

-- GOOD: Use PROCEDURE for transaction control (PostgreSQL 11+)
CREATE OR REPLACE PROCEDURE good_commit_proc()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO logs(msg) VALUES ('step 1');
    COMMIT;  -- allowed inside a PROCEDURE
    INSERT INTO logs(msg) VALUES ('step 2');
    COMMIT;
END;
$$;

-- Call the procedure correctly
CALL good_commit_proc();

---

Quick Fix Solutions

1. Change function volatility: Replace IMMUTABLE or STABLE with VOLATILE if the function modifies data.
2. Route writes to the primary: Verify with pg_is_in_recovery() and ensure write operations always target the primary server.
3. Replace FUNCTION with PROCEDURE: For any logic requiring COMMIT/ROLLBACK, migrate to a PROCEDURE (PostgreSQL 11+).

---

Prevention Tips

• Enforce volatility conventions: Always explicitly declare VOLATILE, STABLE, or IMMUTABLE when creating functions. Integrate a linting step in your CI/CD pipeline to catch mismatched volatility declarations before deployment.

-- Periodically audit non-VOLATILE functions for unsafe DML
SELECT n.nspname, p.proname,
       CASE p.provolatile WHEN 'i' THEN 'IMMUTABLE' ELSE 'STABLE' END AS volatility
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND p.provolatile IN ('i', 's');

• Validate connection targets: In HA or read/write split environments, always check pg_is_in_recovery() at the application startup or connection checkout phase to ensure write operations are never accidentally sent to a replica.

---

Related Errors

• 38001 (reading_sql_data_not_permitted): Raised when even read SQL is forbidden in the current function context.
• 38002 (modifying_sql_data_not_permitted): Similar to 38003 but triggered in a slightly different context where data modification is explicitly disallowed.
• 25006 (read_only_sql_transaction): Raised when a write is attempted directly in a read-only transaction; often co-occurs with 38003 in replica environments.

---

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