current transaction is aborted, commands ignored until end of transaction block

What it means

This error is never the real problem. Some earlier statement in the same transaction failed, and in PostgreSQL any error inside a transaction block marks the whole transaction as failed. From then on, the server ignores every statement except the ones that end the transaction (ROLLBACK, or ROLLBACK TO a savepoint) and answers each with this message.

Nothing the transaction did will be saved. Even COMMIT doesn’t save it: it ends the transaction and replies ROLLBACK. Code that doesn’t check that reply can believe its changes were committed.

The SQLSTATE is 25P02 (in_failed_sql_transaction).

Common causes

1. An error was caught and ignored. Application code catches an exception, logs it, and carries on using the same transaction.
2. A script with one bad statement. Everything after the first error in a BEGIN … COMMIT block fails with this message.
3. An expected error, handled the wrong way. “Insert, and if it already exists, update” works in some databases by catching the duplicate-key error. In PostgreSQL, the failed insert has already aborted the transaction.
4. A connection returned to a pool mid-transaction. If code doesn’t roll back after an error, the next request that gets that connection inherits the failed transaction.
5. A timeout or concurrency error, such as a statement timeout, deadlock or serialisation failure, which also aborts the transaction.

How to fix it

Find the first error

Scroll up, or look in the application or server log, for the first ERROR in this transaction. That’s the one to fix. Everything after it is noise.

Roll back and start again

ROLLBACK;

Then run the transaction again from BEGIN, with the first error fixed. In application code, roll back in the error handler of every transaction, before the connection goes back to the pool.

Use a savepoint when an error is expected

A savepoint marks a point you can return to inside a transaction. Rolling back to it undoes only what came after, and the transaction carries on:

BEGIN;
INSERT INTO accounts VALUES (3, 50);
SAVEPOINT before_risky;
INSERT INTO accounts VALUES (1, 50);        -- fails: duplicate key
ROLLBACK TO SAVEPOINT before_risky;
-- the transaction is usable again; row 3 is still there
COMMIT;

Avoid the error in the first place

For “insert or update”, let PostgreSQL handle the conflict, so nothing fails:

INSERT INTO accounts VALUES (1, 50)
ON CONFLICT (id) DO UPDATE SET balance = EXCLUDED.balance;

ON CONFLICT (id) DO NOTHING skips the row instead.

In psql

\set ON_ERROR_ROLLBACK interactive makes psql set a savepoint before each statement you type, so one mistake doesn’t lose the whole transaction. For scripts, stop at the first error instead of carrying on:

psql -v ON_ERROR_STOP=1 --single-transaction -f migrate.sql

Find sessions stuck in a failed transaction

SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction (aborted)';

Each one holds its connection open until its client rolls back. query shows the last statement it sent.

Reproduce it

On PostgreSQL 18.6, with rows 1 and 2 already in seo_pgconn.accounts:

BEGIN;
BEGIN
INSERT INTO seo_pgconn.accounts VALUES (3, 50);
INSERT 0 1
INSERT INTO seo_pgconn.accounts VALUES (1, 50);
ERROR:  duplicate key value violates unique constraint "accounts_pkey"
DETAIL:  Key (id)=(1) already exists.
SELECT * FROM seo_pgconn.accounts;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COMMIT;
ROLLBACK

Row 3 isn’t there afterwards: COMMIT rolled it back. With \set VERBOSITY verbose the code shows: ERROR: 25P02: current transaction is aborted, …. PostgreSQL 14, 15, 16 and 17 give the same message.

With \set ON_ERROR_ROLLBACK on, the same steps keep row 3 and the transaction stays usable.

A script run with --single-transaction but without ON_ERROR_STOP keeps going after the first error, reports this message for every later statement, and still exits with status 0, though nothing was saved:

INSERT 0 1
psql:…/script.sql:2: ERROR:  duplicate key value violates unique constraint "accounts_pkey"
DETAIL:  Key (id)=(1) already exists.
psql:…/script.sql:3: ERROR:  current transaction is aborted, commands ignored until end of transaction block

With -v ON_ERROR_STOP=1 it stops after line 2 and exits with status 3.

A session left in this state, seen from another connection:

  pid   |             state             |    query    
--------+-------------------------------+-------------
 277109 | idle in transaction (aborted) | SELECT 1/0;
(1 row)

In Inlet

Inlet’s query editor supports transactions with manual commit and shows each error at the position the server reports, so you can find the statement that failed first and roll back. With your own Anthropic API key, Ask Claude can explain that statement’s error and suggest a fix.