SQL Postgres 是否支持嵌套或自治事务？

Does Postgres support nested or autonomous transactions?

提问by SarthAk

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:

我有一种情况，我必须提交一部分代码作为它自己的事务。
我创建了一个表subtransaction_tbl：

CREATE TABLE subtransaction_tbl
(
  entryval integer
)

And a function in language plpython3u:

以及语言 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

表中的条目是正确的：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 1or 2should be added to table subtransaction_tblbut 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.

我期望1或2应该添加到表中，subtransaction_tbl但令我惊讶的是没有插入任何值。我想象一个新的子事务被函数打开，它不应该依赖于父事务。请让我知道我是否正确。

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

Postgres 中是否有自治事务？还是我必须修改我的 plpython3u 函数？

回答by vitaly-t

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

Postgres 确实支持嵌套事务，但它们不同于传统的 SQL，更像是带有嵌套部分点的事务。

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

在顶层你总是有你的典型BEGIN/COMMIT/ROLLBACK，在嵌套级别你必须使用以下命令：

• 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

• SAVEPOINT name- 创建一个新的保存点，名称对于交易来说是唯一的
• RELEASE SAVEPOINT name- 提交保存点，但只有在包含事务提交时它才会持续存在
• ROLLBACK TO SAVEPOINT name- 回滚保存点

You would also have to make sure that:

您还必须确保：

• The names used for each SAVEPOINTare unique;
• Failure in one SAVEPOINTis propagated upwards to the top level.

• 每个使用的名称SAVEPOINT都是唯一的；
• 一个失败SAVEPOINT向上传播到顶层。

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:

当我写pg-promise 时，我确保这两个规定得到保证：

• 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 ROLLBACKin case a child transaction fails - all built on the standard promise-chaining logic.

• 它根据事务级别自动生成保存点名称，如level_1、level_2等；
• 它执行 contains ROLLBACK TO SAVEPOINT name，以及在ROLLBACK子事务失败的情况下的顶级- 所有这些都建立在标准的承诺链逻辑上。

See also the limitationsof the PostgreSQL nested transactions explained...

另请参阅解释的 PostgreSQL 嵌套事务的限制...

回答by Erwin Brandstetter

There are noautonomous transactions in Postgres before Postgres 11, where SQL procedureswere added. Everything that's done in a functionis rolled back with the transaction.

Postgres 11 之前的 Postgres中没有自治事务，其中添加了SQL过程。在函数中完成的所有操作都随事务回滚。

Here is a discussion of the feature:

以下是对该功能的讨论：

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

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

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

在 Postgres 10 或更早版本中，解决方法可能是（ab-）使用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?

• dblink 无法在更新后触发器中更新同一数据库上的表
• 如何在 PostgreSQL 中进行大型非阻塞更新？

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

还有 a 的相关概念SAVEPOINT。（不是一回事！）：

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:

plpython 有子事务( with plpy.subtransaction():)，但这与自治事务不同。没有单独的COMMIT. 它所做的只是将几个语句捆绑在一起，使它们具有原子性。否则，如果在中间某处发生异常，并且您捕获该异常，则只会执行此异常之前的代码。如果你把它包装成一个子事务，要么全有要么全无。这就像使用SAVEPOINT，而不是自治事务。根据文档：

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.

子事务上下文管理器不会捕获错误，它只会确保在其范围内执行的所有数据库操作都将被自动提交或回滚。