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”.
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
).
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 Facebook or LinkedIn.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply