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.
Table of Contents
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”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 happened inside the transaction, PostgreSQL will terminate and not commit anymore. As you can see, at the end we did indeed try to commit, but PostgreSQL had to issue a ROLLBACK instead. The important lesson here is that an application has to check whether the transaction ended successfully or not. The fact that COMMIT has been issued does not mean that it has actually succeeded.
There are a couple of things you need to keep in mind when thinking about the error message: By default, PostgreSQL will send all error messages to the log file. Now imagine running a long batch job containing millions of statements in a single transaction. If the job does not terminate in case of error, you will find millions of errors (= entries) in your text log. This can be fairly dangerous or at least flood your system with a lot of pointless content.
If a transaction containing an error has to finish successfully (COMMIT) no matter what, the only way in PostgreSQL is to make use of SAVEPOINT which is actually a subtransaction. It is the only way to rescue a transaction which has encountered an error. Learning about SAVEPOINT and subtransactions is definitely important to write better and more efficient code.
If you want to know more about PostgreSQL read this important information about CREATE SCHEMA.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at