Hans-Juergen Schoenig: ERROR: current transaction is aborted in PostgreSQL

Whenever you actively use transaction blocks in PostgreSQL “ERROR: current transaction is aborted, commands ignored until end of transaction block” is by far most often seen error message of them all. However, this does not imply that it is also the most widely understood error message of them all. This blog will hopefully provide some enlightenment to those out there who want to understand what is really going on, who want to know what this message means and how it actually works. Ensuring “everything or nothing” in a transaction To understand this most famous error message of them all, we first need to understand what a transaction in PostgreSQL really means. It is all about “everything or nothing”. Consider the following listing: test=# BEGIN; BEGIN test=*# SELECT 1; ?column? ---------- 1 (1 row) test=*# SELECT 2; ?column? ---------- 2 (1 row) test=*# COMMIT; COMMIT In PostgreSQL, every statement is a transaction. If you run just a single SQL statement it is (at least) one transaction. If you want to pack more than just a single statement into a transaction, you have to use BEGIN / COMMIT. In my example, two statements have been turned into one atomic block of operations. If all of them are successful, the transaction can end successfully (= COMMIT). However, if a single statement fails, we will see our famous error message: test=# BEGIN; BEGIN test=*# SELECT 3; ?column? ---------- 3 (1 row) test=*# SELECT 4 / 0; ERROR: division by zero test=!# SELECT 5; ERROR: current transaction is aborted, commands ignored until end of transaction block test=!# SELECT 5; ERROR: current transaction is aborted, commands ignored until end of transaction block test=!# SELECT 6; ERROR: current transaction is aborted, commands ignored until end of transaction block test=!# COMMIT; ROLLBACK The first SQL statement works just fine. However, a division by zero is not allowed in PostgreSQL which leads to a normal error. What happens now is that once an error has happ[...]