PostgreSQL 40002 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 40002: transaction_integrity_constraint_violation

PostgreSQL error code 40002 (transaction_integrity_constraint_violation) is raised when a transaction violates integrity constraints in a way that cannot be resolved without aborting and retrying the transaction. It belongs to error class 40 (Transaction Rollback), which means the application must implement retry logic to handle it gracefully. This error is closely related to 40001 (serialization_failure) and typically appears under SERIALIZABLE or REPEATABLE READ isolation levels.

Top 3 Causes

1. Write-Write Conflicts Under SERIALIZABLE Isolation

When two concurrent transactions attempt to modify the same rows, PostgreSQL detects the conflict and aborts one of them to maintain serializability.

-- Session 1
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Session 2 (concurrent)
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;
-- One of these will receive ERROR 40002 on COMMIT
COMMIT;

2. Deferrable Constraint Violations at Commit Time

PostgreSQL allows constraints to be checked at the end of a transaction using DEFERRABLE INITIALLY DEFERRED. If a violation exists at commit time, a 40002 error is raised instead of an immediate constraint error.

-- Create a deferrable foreign key constraint
ALTER TABLE orders
    ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    DEFERRABLE INITIALLY DEFERRED;

-- This will fail at COMMIT with 40002, not at INSERT time
BEGIN;
INSERT INTO orders (order_id, customer_id, amount)
VALUES (1001, 99999, 250.00); -- customer 99999 does not exist

COMMIT; -- ERROR: 40002 raised here

-- Fix: validate constraints early
BEGIN;
SET CONSTRAINTS fk_customer IMMEDIATE; -- Force immediate check
INSERT INTO orders (order_id, customer_id, amount)
VALUES (1001, 99999, 250.00); -- ERROR raised immediately, easier to debug
COMMIT;

3. SSI Read-Write Anti-dependency Cycles

PostgreSQL's Serializable Snapshot Isolation (SSI) detects read-write cycles that would produce non-serializable results and aborts one transaction.

-- Session 1 reads data, then writes based on it
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE region = 'EAST'; -- reads
INSERT INTO reports (region, total) VALUES ('EAST', 15000);

-- Session 2 concurrently inserts into the range Session 1 read
BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT INTO accounts (account_id, region, balance) VALUES (999, 'EAST', 5000);
COMMIT; -- may succeed

-- Session 1's COMMIT may now fail with 40002
COMMIT; -- ERROR 40002: serialization anomaly detected

-- Fix: use SELECT FOR UPDATE to explicitly lock the read set
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE region = 'EAST' FOR SHARE;
INSERT INTO reports (region, total) VALUES ('EAST', 15000);
COMMIT;

Quick Fix Solutions

The most important fix is implementing retry logic at the application level. All errors in class 40 are designed to be retried.

-- PL/pgSQL retry wrapper example
CREATE OR REPLACE FUNCTION safe_transfer(
    p_from INT, p_to INT, p_amount NUMERIC
) RETURNS VOID AS $$
DECLARE
    v_attempts INT := 0;
BEGIN
    LOOP
        BEGIN
            UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from;
            UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to;
            RETURN; -- success
        EXCEPTION
            WHEN transaction_integrity_constraint_violation
              OR serialization_failure THEN
                v_attempts := v_attempts + 1;
                IF v_attempts >= 5 THEN
                    RAISE; -- give up after 5 attempts
                END IF;
                PERFORM pg_sleep(0.1 * v_attempts); -- simple backoff
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Prevention Tips

Always implement retry logic with exponential backoff for any transaction that may run under SERIALIZABLE or REPEATABLE READ isolation. Catch SQLSTATE 40002 and 40001 together and retry up to 5–10 times with increasing wait intervals.
Keep transactions as short as possible and avoid doing heavy computation, external API calls, or file I/O inside a transaction block. The shorter the transaction lifetime, the lower the probability of conflicts. Also, consider whether SERIALIZABLE is truly necessary — READ COMMITTED avoids most 40002 scenarios and is sufficient for many workloads.

-- Set timeouts to prevent long-running transactions from blocking others
SET lock_timeout = '5s';
SET statement_timeout = '30s';

-- Monitor for conflict patterns
SELECT count(*), wait_event
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY wait_event
ORDER BY count DESC;

Related Errors

Code	Name	Notes
40001	serialization_failure	Most closely related; handle together with 40002
23000	integrity_constraint_violation	Immediate constraint violation; retry won't help
23505	unique_violation	Can surface as 40002 with deferred unique constraints
55P03	lock_not_available	Raised with `NOWAIT`; useful for detecting contention

📖 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