Transactions within a Transaction

Question

What behaviour would PostgreSQL display if for example the script below were called

BEGIN;
SELECT * FROM foo;
INSERT INTO foo(name) VALUES ('bar');
BEGIN; <- The point of interest
END;

Would PostgreSQL discard the second BEGIN or would a commit be implicitly decided on and then run the BEGIN END block at the end as a separate transaction?

Answer 1

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.

Answer 2

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 commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command.

Answer 3

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.

Answer 4

For Postgresql 9.5 or newer you can use dynamic background workers provided by pg_background extension. It creates autonomous transaction. Please, refer the github page of the extension. The sollution is better then db_link. There is a complete guide on Autonomous transaction support in PostgreSQL