Transactions within a Transaction
What you would need is a so called "autonomous transaction" (a feature provided by oracle). At this point this is not possible in PostgreSQL yet. However, you can use SAVEPOINTs:
BEGIN;
INSERT ...
SAVEPOINT a;
some error;
ROLLBACK TO SAVEPOINT a;
COMMIT;
It is not entirely an autonomous transaction - but, it allows you get "every transaction" right. You can use it to achieve the thing you expect from autonomous transactions.
Otherwise there are no other reasonable solution at this point.
You could try it yourself:
WARNING: there is already a transaction in progress
It starts no new (sub)transaction as nested transactions are not implemented in PostgreSQL. (You may do some magic in a pl/pgsql
function, for example, that mimics that behaviour, though.)
With PostgreSQL 11, one could think the new real stored procedures and their ability to handle transactions would make nested transactions possible. However, according to the documentation, this is not the case:
In procedures invoked by the
CALL
command as well as in anonymous code blocks (DO
command), it is possible to end transactions using the commandsCOMMIT
andROLLBACK
. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command.
PostgreSQL does not support sub-transactions, but the SAVEPOINT
feature can effectively answer your need. Quoting from the documentation for Advanced access layer to PG via promises by Vitaly Tomilov on GitHub:
PostgreSQL doesn't have proper support for nested transactions, it only supports partial rollbacks via savepoints inside transactions. The difference between the two techniques is huge, as explained further.
Proper support for nested transactions means that the result of a successful sub-transaction isn't rolled back when its parent transaction is rolled back. But with PostgreSQL save-points, if you roll-back the top-level transaction, the result of all inner save-points is also rolled back.
Savepoints can be used for partial rollbacks to an earlier point inside an active transaction. For example, to establish a savepoint and later undo the effects of all commands executed after it was established:
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
COMMIT;
The above transaction will insert the values 1 and 3, but not 2. See the SAVEPOINT
documentation for more information.