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?

Polygamy

Possible duplicate of Committing transactions while executing a postgreql Function – Sinistral

Answer 1

There are no autonomous transactions in Postgres before Postgres 11, where SQL procedures were added. See:

• When to use stored procedure / user-defined function?

Everything that's done in a function is committed or rolled back with the transaction.

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. The manual:

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.

Here is a discussion of the feature:

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

Sept

does creating dblink have any performence bottelneck ? – Polygamy

@user3247573: It is pretty fast in my experience. If you call dblink for every single row individually it may be a drag in operations with many rows. Try to run it once for many rows ... – Sept

Answer 2

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 sp_xy, where x is the current task/transaction depth, and y is the actual transaction level + 1;
• 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...

Agincourt

That's not a nested transaction, because when you roll back the outer transaction, everything will be rolled back. The common understanding of a nested transaction means that it can be committed even when the "outer" transaction is rolled back. – Screamer

@a_horse_with_no_name, yes, that's the unconventional part, calling rollback on the top level will revert changes for any nested savepoint, unfortunately. That's that's the difference between proper nested transactions and nested transactions via save points. – Agincourt

Could you clarify what you mean by "unconventional" (could you provide me some references?), it is the first time I am researching nested transactions. From a developer point of view, I expect a rollback on a transaction to rollback anything that happened inside it, be it simple queries or child transactions (whether they are committed or not). The behavior you described with the "save-points" seems to make more sense. – Mariandi

@Mariandi I have updated it slightly, to more encompass the difference, but it was there already, through the links I provided. – Agincourt

What confuses me is "the result of a successful sub-transaction [...]" because this is a concept that is not defined by the Postgres documentations. BEGIN; Q1; BEGIN; Q2; COMMIT; ROLLBACK; is equivalent to BEGIN; Q1; /* warning; */ Q2; COMMIT; /* warning; */. For me, it isn't "the reference behavior for sub-transactions" but the illustration of silent errors and lack of support for first-class nested transactions. Is this what you call "conventional SQL" or were you referring to other SQL implementations that claim support for nested transactions but still behave as you described? – Mariandi

And just to clarify, thank you for explaining that Postgres provides a more general feature (save points) that allows to implement the semantics of nested transactions as described in this issue and implemented by pg-sql, I just felt that your library could claim to have real support for nested transactions, and that this is different from the naive approach to chain BEGIN + ROLLBACK / COMMIT statements that leads to silent errors. – Mariandi

@Mariandi As per documentation , my library fully automates everything about PostgreSQL transactions, it doesn't do anything that PostgreSQL doesn't support :) It automatically chains savepoints on sub-levels, and can do automatic rollback on the transaction when a savepoint fails, when it is needed (it is not necessary). If you want to see how it works, best is to use pg-monitor, so you can see everything it executes. – Agincourt

...arguing on a meaningless term "nested transaction". Transactions by definition (all or nothing) can not be nested, they can only independent, with other words autonomus. What on earth means the inner commit, if that can be rollbacked? (nothing) – Cirilla

Answer 3

Here is a patch with support of Autonomous transactions; it's a work-in-progress. If you want, you may patch Postgres master source code and compile.

Here's a video at FOSSAsia with the description.

The patch introduces Autonomous Transactions for PL/pgSQL. It adds pragma AUTONOMOUS_TRANSACTION to functions. When one such function is executed all (at the current time not all, WIP) statements from it are executed in an autonomous session.

Example SQL-request:

CREATE TABLE tbl (a int);
CREATE OR REPLACE FUNCTION func() RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO tbl VALUES (1);
END;
$$;


START TRANSACTION;
SELECT func();
ROLLBACK;


SELECT * FROM tbl;


DROP FUNCTION func;
DROP TABLE tbl;

Output:

  a
---
  1
(1 row)

For each backend the patch lazily creates a pool of autonomous sessions. When backend calls autonomous function, backend takes one autonomous session from this pool and sends there function's statements for execution. When execution is finished backend returns session to pool. Lazily means that pool is created only when first autonomous session is needed. Backend and autonomous session communicate with the help of Postgres client-server protocol. Messages are sent through dynamic shared memory.

Execution of backend and autonomous session is synchronous: autonomous session waits for messages from backend and backend waits for messages from autonomous session.

Classified

Thank you so much for the edit - Outside my area of expertise, but it looks like you turned it into a good answer! – Dorene