Errors inside a transaction

Transactions, isolation and locking Mid-level Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Predict the output

A psql script keeps going after an error, like app code that swallows the exception. What happens at the final SELECT?

SQL · PostgreSQL 17

CREATE TABLE users (email text PRIMARY KEY);
BEGIN;
INSERT INTO users VALUES ('a@x.io');
\set ON_ERROR_STOP 0
INSERT INTO users VALUES ('a@x.io');  -- duplicate: 23505
\set ON_ERROR_STOP 1
SELECT count(*) FROM users;

OptionsIt fails: current transaction is aborted (25P02)It prints 1It prints 2It prints 0

Hint 1 of 1

What state is the transaction in after the 23505 error?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A It fails: current transaction is aborted (25P02)

Correct. The duplicate key error aborted the transaction; nothing else runs until ROLLBACK.

After any error inside a transaction, PostgreSQL rejects every later statement with 25P02 until ROLLBACK. A savepoint lets you roll back just the failed step and carry on.

Why not the others?

B It prints 1. That's what you'd get with a savepoint and ROLLBACK TO before the SELECT.
C It prints 2. The primary key rejects the duplicate.
D It prints 0. No statement runs successfully after the error, including this one.

30-second answer

After any error the whole transaction is marked aborted: every later statement fails with SQLSTATE 25P02 until you ROLL BACK, and COMMIT turns into a rollback. To recover from an expected error without losing earlier work, set a SAVEPOINT before the risky statement and ROLLBACK TO SAVEPOINT if it fails. Drivers use this for nested transaction blocks.

Practise saying it · 30 s. No recording, just a timer

Explain aborted transactions and savepoints.

Say your answer out loud, then check which of these points you covered:

One error aborts the transaction
25P02 until rollback
SAVEPOINT / ROLLBACK TO
ON CONFLICT avoids the error

Deep dive

psycopg 3's nested with conn.transaction(): blocks become savepoints.

Savepoints have a cost; thousands in one transaction slow things down.

Often INSERT ... ON CONFLICT DO NOTHING avoids the error, and the savepoint, entirely.

MySQL behaves differently: a failed statement doesn't abort the whole transaction by default.

Recover with a savepoint (a nested transaction block) Ran on Python 3.13.5 · mypy --strict

Python 3.13

import psycopg

with psycopg.connect(autocommit=True) as conn:
    conn.execute("CREATE TEMP TABLE users (email text PRIMARY KEY)")
    with conn.transaction():
        conn.execute("INSERT INTO users VALUES ('a@x.io')")
        try:
            with conn.transaction():  # SAVEPOINT
                conn.execute("INSERT INTO users VALUES ('a@x.io')")
        except psycopg.errors.UniqueViolation:
            pass  # ROLLBACK TO SAVEPOINT; the outer work survives
        conn.execute("INSERT INTO users VALUES ('b@x.io')")
    row = conn.execute("SELECT count(*) FROM users").fetchone()
    print(row)

Common mistakes

Catching the exception in code and carrying on with more queries.
Wrapping every statement in a savepoint 'just in case'.
Assuming COMMIT after an error saves the earlier statements.

Senior take & follow-ups

State the 25P02 behaviour and show both remedies: savepoints, or SQL that doesn't raise.

Follow-up questions

Think of your answer first, then open the hint.

What does COMMIT do on an aborted transaction?
Show hint
It rolls back and reports ROLLBACK.
How do you insert-if-absent without errors?
Show hint
INSERT ... ON CONFLICT DO NOTHING.

Suggest a fix if something here is wrong or out of date.