When do Postgres transactions fail?

Question

I was wondering at what point a transaction actually fails in Postgres. By this I mean stop working, and return an error. Is it after the first failed query? Does it try all queries and then return failure? Hopefully it fails fast on first failed query, but I don’t know for sure.

For instance, if I have a transaction with 5 insert statements, but insert 2 fails, does it stop right then and return an error?

Thanks

It fails immediately after the first erroneous query. Be careful if you use ORMs or connection pools; they may cache the queries. — Guillaume F.
– Guillaume F., Commented Feb 23, 2022 at 3:10
Thanks! Guillaume. Also, thanks for the advice about the connection pools. — caffein
– caffein, Commented Feb 23, 2022 at 3:33

Laurenz Albe · Accepted Answer · 2022-02-23 07:37:41Z

2

If you have a transaction spanning multiple statements, and one of these statements causes an error, the whole transaction is aborted and can only be rolled back. Here is a psql session that exhibits that behavior:

test=> BEGIN;
BEGIN
test=*> SELECT 42;
 ?column? 
══════════
       42
(1 row)

test=*> SELECT 1 / 0;
ERROR:  division by zero
test=!> SELECT 42;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
test=!> COMMIT;
ROLLBACK

answered Feb 23, 2022 at 7:37

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

When do Postgres transactions fail?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related