PostgreSQL aborts the transactions on error

You may be surprised by this in PostgreSQL:

postgres=!# commit;
ROLLBACK
postgres=#

Yes, I issued a COMMIT but got a ROLLBACK!
I'll demo how it happened and how to avoid it. In short, the transaction was already rolled back and the only possible command to run is a ROLLBACK, which is implicit when terminating the transaction.

I created a table, started a transaction and inserted one row:

postgres=# create table demo ( id int primary key );
CREATE TABLE
postgres=# begin transaction;
BEGIN
postgres=*# insert into demo values (1);
INSERT 0 1
postgres=*#

The * in the prompt (which comes from the %x in the default %/%R%x%# PROMPT1) shows that I'm still in a transaction.

I try to add the same row, with the same key, which violates the primary key constraint:

postgres=*# insert into demo values (1);
ERROR:  duplicate key value violates unique constraint "demo_pkey"
DETAIL:  Key (id)=(1) already exists.
postgres=!#

The ! in the prompt shows that the transaction failed.

I can check its status from another transaction (trying it within a failed transaction would have raised ERROR: current transaction is aborted, commands ignored until end of transaction block):

postgres=!# \! psql -c "select pid, application_name, state from pg_stat_activity where wait_event='ClientRead'"

  pid  | application_name |             state             
-------+------------------+-------------------------------
 66420 | psql             | idle in transaction (aborted)
(1 row)

postgres=!#

As the transaction has been aborted, it has released all locks. To verify this, I insert the same row from another session:

postgres=!# \! psql -c "insert into demo values (1)"

INSERT 0 1

postgres=!

The only thing I can do is ending the transaction block, with ABORT, ROLLBACK or even COMMIT:

postgres=!# commit;
ROLLBACK
postgres=# 

If you're used to Oracle Database, you might find it surprising that in an interactive transaction, you must restart from the beginning even after completing previous statements successfully until only one failed. You may just want to continue with another statement once knowing the reason of the error. Oracle Database automatically creates a savepoint before each user call, rolling back to this savepoint in case of an error, so that the user can continue with another statement once aware of the error.

In PostgreSQL, creating implicit savepoints is the client's or driver's responsibility.

For instance, PgJDBC can enable autosave=on to achieve this. However, it's important to note that using savepoints in PostgreSQL may be more resource-intensive compared to other databases.

Another example is a PL/pgSQL statement with an exception block that creates an implicit savepoint to roll back the main block before running the exception block. This differs from Oracle Database, which rolls back only the statement that failed when continuing to the exception block of PL/SQL.

If the exceptions are managed by your application code, you must use savepoints to achieve the same.

With an interactive user interface, like PSQL, it might be preferrable to create an implicit savepoint before each statement, and this is possible with ON_ERROR_ROLLBACK. Here is an example:

postgres=# drop table demo;
DROP TABLE

postgres=# create table demo ( id int primary key );
CREATE TABLE

postgres=# \set ON_ERROR_ROLLBACK on

postgres=# begin transaction;
BEGIN

postgres=*# insert into demo values (1);
INSERT 0 1

postgres=*# insert into demo values (1);
ERROR:  duplicate key value violates unique constraint "demo_pkey"
DETAIL:  Key (id)=(1) already exists.

postgres=*# \! psql -c "select pid, application_name, state from pg_stat_activity where wait_event='ClientRead'"  

  pid  | application_name |        state
-------+------------------+---------------------
 66461 | psql             | idle in transaction
(1 row)

postgres=*# insert into demo values (2);
INSERT 0 1

postgres=*# commit;
COMMIT

In this interactive transaction, with ON_ERROR_ROLLBACK set to on, I was able to continue with another value when I got the information that the one I tried to insert was a duplicate one.

When managing exceptions in your application, such as implementing retry logic for serializable errors, consider creating a savepoint before executing a statement. This allows you to continue with the same transaction if an exception is caught. However, be cautious as it is not always the right solution. In cases of deadlocks, one transaction must abort to release its locks. The scope of rollback on errors depends on what has been executed before, so it makes sense that the application controls it rather than relying on defaults.

In interactive usage with PSQL, setting ON_ERROR_ROLLBACK is advisable to prevent rolling back all previous work due to a simple typo causing an error. While it is unnecessary if you do not start a transaction explicitly, and rely on autocommit, running interactive commands without the ability to verify outcomes before committing changes is not recommended.