PostgreSQL: 'current transaction is aborted, commands ignored until end of transaction block' (25P02)

ERROR: current transaction is aborted, commands ignored until end of transaction block — an earlier statement in the same transaction failed, and PostgreSQL now refuses every command until the transaction ends. Run ROLLBACK, fix the statement that failed first (its error is the one above this message), and run the transaction again.

If one statement is allowed to fail, set a SAVEPOINT before it and run ROLLBACK TO SAVEPOINT when it fails; the rest of the transaction survives. In Npgsql, catching the exception does not repair the transaction: roll it back and begin a new one, or use the transaction's savepoint methods.

The error

BEGIN;
BEGIN
INSERT INTO customers VALUES (2, 'David Chen', 'david@example.test');
INSERT 0 1
INSERT INTO customers VALUES (3, 'Aisha Bello', 'maria@example.test');
ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(maria@example.test) already exists.
INSERT INTO customers VALUES (4, 'Lars Eriksen', 'lars@example.test');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COMMIT;
ROLLBACK

psql ran with -a, so each statement is echoed above its result. From a .NET app the same state arrives as an exception on the next command:

Unhandled exception. Npgsql.PostgresException (0x80004005): 25P02: current transaction is aborted, commands ignored until end of transaction block

Why it happens

A PostgreSQL transaction is all or nothing. Once any statement inside it fails, the server marks the whole transaction as failed and answers every further command with SQLSTATE 25P02 until it receives ROLLBACK. Even COMMIT saves nothing: psql printed ROLLBACK as its result, and afterwards the table held only Maria Garcia, so David Chen's row, which had succeeded, was gone as well.

In application code this is the classic "I caught the exception and carried on": a loop inserts rows, catches the duplicate-key error for one of them, and the next INSERT throws 25P02. When the code caught every PostgresException instead, it was worse: CommitAsync() returned without an exception, the program exited with code 0, and no row was saved. The habit often comes from SQL Server, where the same statements on LocalDB ended only the failing INSERT and committed the other two rows.

The fix

BEGIN;
BEGIN
INSERT INTO customers VALUES (2, 'David Chen', 'david@example.test');
INSERT 0 1
SAVEPOINT before_aisha;
SAVEPOINT
INSERT INTO customers VALUES (3, 'Aisha Bello', 'maria@example.test');
ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(maria@example.test) already exists.
ROLLBACK TO SAVEPOINT before_aisha;
ROLLBACK
INSERT INTO customers VALUES (4, 'Lars Eriksen', 'lars@example.test');
INSERT 0 1
COMMIT;
COMMIT

The same idea in Npgsql 10, with a savepoint around each row:

foreach (var c in customers)
{
    await tx.SaveAsync("row");
    try
    {
        await using var cmd = new NpgsqlCommand(
            "INSERT INTO customers (id, name, email) VALUES ($1, $2, $3)", conn, tx);
        cmd.Parameters.AddWithValue(c.Id);
        cmd.Parameters.AddWithValue(c.Name);
        cmd.Parameters.AddWithValue(c.Email);
        await cmd.ExecuteNonQueryAsync();
        await tx.ReleaseAsync("row");
        Console.WriteLine($"inserted {c.Name}");
    }
    catch (PostgresException ex)
    {
        await tx.RollbackAsync("row");
        Console.WriteLine($"skipped {c.Name}: {ex.SqlState} {ex.MessageText}");
    }
}
await tx.CommitAsync();

inserted David Chen
skipped Aisha Bello: 23505 duplicate key value violates unique constraint "customers_email_key"
inserted Lars Eriksen
  1 Maria Garcia
  2 David Chen
  4 Lars Eriksen

If the rows do not belong together, the simpler design is one transaction per unit of work: begin it inside the loop, commit on success, roll back in the catch. That gave the same three rows. In psql, \set ON_ERROR_ROLLBACK on makes psql issue a savepoint before each statement inside a transaction; with it, the original statements committed David Chen and Lars Eriksen.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build) on Docker 29.9.2 under Windows 11, with a customers table whose email column is UNIQUE; the statements ran through psql 18.4 inside the container. The .NET side was a console app on SDK 10.0.403 with Npgsql 10.0.3. The SQL Server comparison ran the same statements against a temporary table on LocalDB 16.0.1210.3 with sqlcmd. The names fix7-pg and shop are this test's own, and the container was removed afterwards.

Frequently asked

How do I fix current transaction is aborted, commands ignored until end of transaction block?

Run ROLLBACK. Then find the first error inside that transaction, which is the real problem, fix it and run the transaction again. If one statement is allowed to fail, wrap it in a SAVEPOINT.

Why does COMMIT print ROLLBACK in PostgreSQL?

Because the transaction had already failed. PostgreSQL ends a failed transaction when it receives COMMIT but discards all of its changes and reports ROLLBACK. In the test, Npgsql's CommitAsync did not throw in this state, so do not rely on the commit to tell you an earlier statement failed.

How do I continue a PostgreSQL transaction after an error in Npgsql?

Call SaveAsync("name") on the transaction before the statement that may fail and RollbackAsync("name") in the catch block; the transaction can then carry on and commit. The alternative is to give each unit of work its own transaction.

More decoded errors in the Fixes category. When the first error in the transaction is a duplicate primary key right after seeding data, the cause is usually the identity sequence: 23505 after seeding.