PostgreSQL 38003 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 38003: prohibited sql statement attempted

PostgreSQL error code 38003 occurs when a SQL statement is executed inside a function or stored procedure that violates the function's declared security policy or execution context constraints. This typically happens when a function declared as STABLE or IMMUTABLE attempts to modify data, or when a PARALLEL SAFE function tries to execute commands forbidden in parallel worker processes. Understanding the function attribute system is key to resolving this error quickly.

Top 3 Causes and Fixes

Cause 1: DML Inside a STABLE or IMMUTABLE Function

Declaring a function as STABLE signals to PostgreSQL that it will not modify the database. Attempting any INSERT, UPDATE, or DELETE inside such a function triggers error 38003.

-- BAD: STABLE function attempting an INSERT
CREATE OR REPLACE FUNCTION find_or_create_product(p_name TEXT)
RETURNS INT
LANGUAGE plpgsql
STABLE  -- This is the problem
AS $$
DECLARE v_id INT;
BEGIN
  SELECT id INTO v_id FROM products WHERE name = p_name;
  IF NOT FOUND THEN
    -- 38003 fires here!
    INSERT INTO products (name) VALUES (p_name) RETURNING id INTO v_id;
  END IF;
  RETURN v_id;
END;
$$;

-- GOOD: Change to VOLATILE to allow data modification
CREATE OR REPLACE FUNCTION find_or_create_product(p_name TEXT)
RETURNS INT
LANGUAGE plpgsql
VOLATILE  -- Correctly declares that data may be modified
AS $$
DECLARE v_id INT;
BEGIN
  SELECT id INTO v_id FROM products WHERE name = p_name;
  IF NOT FOUND THEN
    INSERT INTO products (name) VALUES (p_name) RETURNING id INTO v_id;
  END IF;
  RETURN v_id;
END;
$$;

Cause 2: Forbidden Statements in PARALLEL SAFE Functions

When a function is marked PARALLEL SAFE, PostgreSQL may execute it inside a parallel worker process. Parallel workers cannot run transaction control commands like SAVEPOINT, COMMIT, or ROLLBACK, which triggers 38003.

-- Check current parallel safety of a function
SELECT proname, proparallel  -- 's'=SAFE, 'r'=RESTRICTED, 'u'=UNSAFE
FROM pg_proc
WHERE proname = 'my_reporting_function';

-- BAD: PARALLEL SAFE function using transaction control
CREATE OR REPLACE FUNCTION process_batch(p_batch_id INT)
RETURNS VOID
LANGUAGE plpgsql
PARALLEL SAFE  -- Wrong: contains SAVEPOINT
AS $$
BEGIN
  SAVEPOINT batch_sp;  -- 38003 in parallel worker!
  UPDATE jobs SET status = 'done' WHERE batch_id = p_batch_id;
END;
$$;

-- GOOD: Downgrade parallel safety appropriately
CREATE OR REPLACE FUNCTION process_batch(p_batch_id INT)
RETURNS VOID
LANGUAGE plpgsql
PARALLEL UNSAFE  -- Correct: no parallel execution allowed
AS $$
BEGIN
  SAVEPOINT batch_sp;
  UPDATE jobs SET status = 'done' WHERE batch_id = p_batch_id;
EXCEPTION WHEN OTHERS THEN
  ROLLBACK TO SAVEPOINT batch_sp;
  RAISE;
END;
$$;

-- Or alter an existing function without recreating it
ALTER FUNCTION process_batch(INT) PARALLEL UNSAFE;

Cause 3: Utility Commands Inside Trigger Functions

Trigger functions execute within a transaction context, which means commands like VACUUM, CLUSTER, or CREATE INDEX CONCURRENTLY — which cannot run inside a transaction — will raise 38003 immediately.

-- BAD: Trigger attempting VACUUM directly
CREATE OR REPLACE FUNCTION post_delete_cleanup()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  EXECUTE 'VACUUM ANALYZE audit_log';  -- 38003! Cannot run in transaction
  RETURN NULL;
END;
$$;

-- GOOD: Use pg_notify to delegate to an external worker
CREATE OR REPLACE FUNCTION post_delete_cleanup()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Signal an external listener (e.g., pg_cron, custom daemon)
  PERFORM pg_notify('maintenance_needed', TG_TABLE_NAME);
  RETURN NULL;
END;
$$;

-- Schedule VACUUM separately using pg_cron
SELECT cron.schedule(
  'vacuum-audit-log',
  '0 3 * * *',
  'VACUUM ANALYZE audit_log'
);

Quick Diagnosis Query

-- Audit all public functions for potential attribute mismatches
SELECT
  proname,
  CASE provolatile
    WHEN 'i' THEN 'IMMUTABLE'
    WHEN 's' THEN 'STABLE'
    WHEN 'v' THEN 'VOLATILE'
  END AS volatility,
  CASE proparallel
    WHEN 's' THEN 'PARALLEL SAFE'
    WHEN 'r' THEN 'PARALLEL RESTRICTED'
    WHEN 'u' THEN 'PARALLEL UNSAFE'
  END AS parallel_safety
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace
ORDER BY proname;

Prevention Tips

Always explicitly declare function attributes. Never rely on defaults. Make it a code review requirement that every function explicitly states VOLATILE/STABLE/IMMUTABLE and PARALLEL SAFE/UNSAFE/RESTRICTED. Add a linting step to your CI pipeline that flags any function containing DML that is declared STABLE or IMMUTABLE.
Test functions with parallelism enabled and disabled. Before deploying, run your workload with both SET max_parallel_workers_per_gather = 4 and SET max_parallel_workers_per_gather = 0. If behavior differs, your function has a parallel safety misconfiguration that will eventually surface as a 38003 error in production.

Related Error Codes

Code	Name	Relationship
38000	external_routine_exception	Parent category of 38003
38001	containing_sql_not_permitted	SQL not allowed in this routine type
38002	modifying_sql_data_not_permitted	Closest sibling — data modification blocked
25006	read_only_sql_transaction	Transaction-level (not function-level) write block

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

DEV Community