SarthAk SarthAk - 3 months ago 36
SQL Question

Does Postgres support nested or autonomous transactions?

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

I have created a table


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:

INSERT INTO subtransaction_tbl VALUES (4);
select subtransaction_nested_test_t();

Entries in table are correct: 1,2,4

Second situation:

INSERT INTO subtransaction_tbl VALUES (4);
select subtransaction_nested_test_t();

Values in the table are not populated

I expected
should be added to table
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?


There are no autonomous transactions in Postgres up until and including Postgres 9.4. Everything that's done in the function is rolled back with the transaction.

There is an open TODO item in the project list:

Here is a discussion of the feature:

For now, a workaround could be to (ab-)use dblink:

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


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.