Why does PostgreSQL silently ROLLBACK when I issue COMMIT after a caught exception? (Also observed on PolarDB for PostgreSQL)

Matthias' answer is exhaustive for the first question, I have nothing to add.

If the transaction was aborted by an error, a COMMIT will silently roll back. Essentially, it ends the transaction in the only possible way. There was an interesting thread on the pgsql-hackers list a while ago about that topic, and it would probably be "more correct" if COMMIT threw an error and still rolled back, but no action was taken...

To your third question, the "atomicity" guarantee of a database transaction means that either all statements in a transaction fail or all succeed, so that data consistency is maintained. So the normal way to proceed is that you roll back any transaction that encounters a database error. You can try something alternative in another transaction.

In those cases where rolling back the entire transaction would be painful (all the work of your batch job is undone), you can use savepoints. But use them in moderation. If you use too many savepoints (implemented as subtransactions), your database performance will be severely affected. PostgreSQL v18 has introduced the parameter subtransaction_buffers to mitigate the problem to some extent, but it is still a problem.

It is the PostgreSQL implementation that a failed transaction cannot be commited anymore.

The only solution is to roll back the transation, that is why your commit call does also fail.

Answers:

(1) Yes, these are two independent state machines. You could additionally roll back after catching the exception to continue running PostgreSQL statements. However in this case you would rollback your step (1).

(2) Commit does not silently roll back, it just does not commit the transaction - a failed transaction cannot be commited anymore (with all previous statements).

(3) If the statements are independent, that is statement (1) should be commited even though statement (2) failed, same with statement (3), I would suggest using three seperate transactions. It is also possible to use savepoints as you suggested however these would create additional overhead on the connection. Of course you could also check previously if you would expect an integrity error by checking if the record already exists.