DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 25P02 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 25P02: In Failed SQL Transaction

PostgreSQL error 25P02 (in_failed_sql_transaction) occurs when you attempt to execute SQL commands inside a transaction that has already been marked as aborted due to a prior error. Once any statement within a BEGIN block fails, PostgreSQL immediately flags the entire transaction as aborted and rejects all subsequent commands — except ROLLBACK. The only way to recover is to issue a ROLLBACK and start a fresh transaction.

Top 3 Causes

1. Ignoring Errors Inside a Transaction Block

The most common cause is simply continuing to run queries after one has failed within the same transaction. Many application drivers won't automatically roll back for you.

-- This triggers 25P02 on the SELECT
BEGIN;
  INSERT INTO products (id, name) VALUES (1, 'Widget');
  INSERT INTO products (id, name) VALUES (1, 'Gadget'); -- ERROR: duplicate key
  SELECT * FROM products; -- ERROR 25P02: in failed sql transaction
COMMIT;

-- Correct approach: rollback on error, then retry
BEGIN;
  INSERT INTO products (id, name) VALUES (1, 'Widget');
  INSERT INTO products (id, name) VALUES (1, 'Gadget'); -- ERROR
ROLLBACK;

BEGIN;
  INSERT INTO products (id, name) VALUES (2, 'Gadget'); -- retry with new key
COMMIT;
Enter fullscreen mode Exit fullscreen mode

2. Missing SAVEPOINT for Partial Error Recovery

When you need to handle expected errors without aborting the whole transaction, use SAVEPOINT. Without it, a single failure kills the entire transaction context.

BEGIN;
  INSERT INTO accounts (id, balance) VALUES (10, 1000.00);

  SAVEPOINT before_transfer;

  UPDATE accounts SET balance = balance - 500 WHERE id = 99; -- row may not exist
  -- If this causes an error, rollback only to savepoint

  ROLLBACK TO SAVEPOINT before_transfer;

  -- Transaction is still alive — continue safely
  UPDATE accounts SET balance = balance + 500 WHERE id = 10;

COMMIT;
Enter fullscreen mode Exit fullscreen mode

3. Unhandled Exceptions in PL/pgSQL Functions

If a stored function raises an unhandled exception, it aborts the caller's transaction, causing every subsequent statement in that transaction to return 25P02.

-- Dangerous: no exception handling
CREATE OR REPLACE FUNCTION risky_insert(p_id INT)
RETURNS VOID AS $$
BEGIN
  INSERT INTO orders (id) VALUES (p_id); -- if this fails, caller's txn is aborted
END;
$$ LANGUAGE plpgsql;

-- Safe: wrap with EXCEPTION block
CREATE OR REPLACE FUNCTION safe_insert(p_id INT)
RETURNS TEXT AS $$
BEGIN
  INSERT INTO orders (id) VALUES (p_id);
  RETURN 'OK';
EXCEPTION
  WHEN unique_violation THEN
    RETURN 'DUPLICATE';
  WHEN OTHERS THEN
    RAISE NOTICE 'Unexpected error: %', SQLERRM;
    RETURN 'ERROR';
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Always ROLLBACK on error — never reuse a connection that's in an aborted transaction state.
  2. Use SAVEPOINT for granular error recovery without losing the entire transaction.
  3. Add EXCEPTION blocks in all PL/pgSQL functions that perform DML.
  4. Monitor aborted sessions using pg_stat_activity: 
SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction (aborted)';

-- Terminate stuck aborted sessions if needed
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction (aborted)'
  AND state_change < NOW() - INTERVAL '5 minutes';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Set idle_in_transaction_session_timeout to auto-terminate sessions stuck in a failed transaction state:

-- Apply globally in postgresql.conf or per role
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
Enter fullscreen mode Exit fullscreen mode

Implement proper error handling at the application layer — always wrap DB calls in try/catch blocks, rollback on any exception, and validate connection health before returning it to the pool. Libraries like HikariCP or PgBouncer can help reset connection state automatically between requests.

Related Errors

Code Name Relation
40P01 deadlock_detected Causes transaction abort → leads to 25P02
23505 unique_violation Common trigger that aborts a transaction
40001 serialization_failure Requires retry logic; leaves txn in aborted state
25001 active_sql_transaction Cannot start a new txn inside an existing one

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

Top comments (0)