Postgres是否支持嵌套或自治事务? [英] Does Postgres support nested or autonomous transactions?
问题描述
在这种情况下，我必须提交一部分代码作为自己的事务.
我创建了一个表subtransaction_tbl:
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
)
还有一个语言为plpython3u的函数:
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;
第一种情况:
BEGIN TRANSACTION;
INSERT INTO subtransaction_tbl VALUES (4);
select subtransaction_nested_test_t();
COMMIT TRANSACTION;
表中的条目正确:1,2,4
Entries in table are correct: 1,2,4
第二种情况:
BEGIN TRANSACTION;
INSERT INTO subtransaction_tbl VALUES (4);
select subtransaction_nested_test_t();
ROLLBACK TRANSACTION;
表中的值未填充
我希望将1或2添加到表subtransaction_tbl中，但令我惊讶的是未插入任何值.我以为函数会打开一个新的子事务，它不应该依赖于父事务.请让我知道我是否正确.
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.
Postgres中有自主交易吗?还是我必须修改plpython3u函数?
Are there autonomous transactions in Postgres? Or do I have to modify my plpython3u function?
推荐答案
在Postgres 11之前的Postgres中，没有没有自治事务，其中SQL
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:
在Postgres 10或更早版本中，一种解决方法是(ab-)使用 dblink :
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?
还有 SAVEPOINT . (不是同一件事！):
There is also the related concept of a SAVEPOINT. (Not the same thing!):
plpython具有子事务(with plpy.subtransaction():)，但这与自主事务不同.没有单独的COMMIT.它所做的就是将几个语句捆绑在一起，使它们成为原子的.否则，如果在中间某处发生异常，并且您捕获了该异常，则仅执行该异常之前的代码.如果将其包装到子事务中，则全有或全无.这就像使用SAVEPOINT而不是自主事务一样. 每个文档:
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.
这篇关于Postgres是否支持嵌套或自治事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
