Does Postgres support nested or autonomous transactions?

Question

I have situation in which I have to commit a portion of code as transaction of its own.
I have created a table subtransaction_tbl:

CREATE TABLE subtransaction_tbl (   entryval integer ) 

And a function in language plpython3u:

CREATE FUNCTION subtransaction_nested_test_t() RETURNS void AS $$ plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)") with plpy.subtransaction():     plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)") $$ LANGUAGE plpython3u; 

First situation:

BEGIN TRANSACTION; INSERT INTO subtransaction_tbl VALUES (4); select  subtransaction_nested_test_t(); COMMIT TRANSACTION; 

Entries in table are correct: 1,2,4

Second situation:

BEGIN TRANSACTION; INSERT INTO subtransaction_tbl VALUES (4); select  subtransaction_nested_test_t(); ROLLBACK TRANSACTION; 

Values in the table are not populated

I expected 1 or 2 should be added to table subtransaction_tbl but to my surprise no value was inserted. I imagined a new subtransaction was opened by the function and it should not depend upon the parent transaction. Please let me know if I am right or not.

Are there autonomous transactions in Postgres? Or do I have to modify my plpython3u function?

Answer 1

Postgres does support nested transactions, but they differ from the conventional SQL, more like transactions with nested partial points.

On the top level you always have your typical BEGIN/COMMIT/ROLLBACK, and on nested levels you have to use the following commands:

• SAVEPOINT name - creates a new savepoint, with name unique for the transaction
• RELEASE SAVEPOINT name - commits the savepoint, though it will only persist if the containing transaction commits
• ROLLBACK TO SAVEPOINT name - rolls back the savepoint

You would also have to make sure that:

• The names used for each SAVEPOINT are unique;
• Failure in one SAVEPOINT is propagated upwards to the top level.

The last bit is a bit tricky, unless you use a library that can do that for you automatically.

When I wrote pg-promise, I made sure that those two provisions are guaranteed:

• It generates save-point names automatically, as level_1, level_2, and so on, based on the transaction level;
• It executes containing ROLLBACK TO SAVEPOINT name, plus the top-level ROLLBACK in case a child transaction fails - all built on the standard promise-chaining logic.

See also the limitations of the PostgreSQL nested transactions explained...

Answer 2

There are no autonomous transactions in Postgres before Postgres 11, where SQL procedures were added. Everything that's done in a function is rolled back with the transaction.

Here is a discussion of the feature:

• http://wiki.postgresql.org/wiki/Autonomous_subtransactions

In Postgres 10 or older a workaround could be to (ab-)use dblink:

• dblink can't update a table on the same database in an after UPDATE trigger
• How do I do large non-blocking updates in PostgreSQL?

There is also the related concept of a SAVEPOINT. (Not the same thing!):

plpython

plpython has subtransactions (with plpy.subtransaction():), but that's not the same as autonomous transactions. There is no separate COMMIT. All it does, is bundle a couple of statements together to make them atomic. Without that, if an exception occurs somewhere in the middle, and you catch that exception, only the code up to this exception would be executed. If you wrap it into a subtransaction, it's all or nothing. This is like using a SAVEPOINT, not an autonomous transaction. Per documentation:

The subtransaction context manager does not trap errors, it only assures that all database operations executed inside its scope will be atomically committed or rolled back.