如何在 PostgreSQL 函数中使用 COMMIT 和 ROLLBACK
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48277519/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to use COMMIT and ROLLBACK in a PostgreSQL function
提问by Rahul Gour
I am using three insert statements, and if there is an error in the third statement, I want to rollback the first and the second one. If there is no way to do this, please tell me a different approach to handle this in PostgresqQL.
我使用了三个insert语句，如果第三条语句有错误，我想回滚第一条和第二条。如果没有办法做到这一点，请告诉我在 PostgresqQL 中处理这个问题的不同方法。
If I use COMMITor ROLLBACK, I get an error.
如果我使用COMMIT或ROLLBACK，则会出现错误。
CREATE OR REPLACE FUNCTION TEST1 ()
RETURNS VOID
LANGUAGE 'plpgsql'
AS $$
BEGIN
INSERT INTO table1 VALUES (1);
INSERT INTO table1 VALUES (2);
INSERT INTO table1 VALUES ('A');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;$$;
The above code is not working; COMMITand ROLLBACKare not supported by PostgreSQL functions.
上面的代码不起作用；COMMIT并且ROLLBACK不受 PostgreSQL 函数支持。
采纳答案by godot
Compared to other SQL languages, you should think that Postgres always takes care of the commit/rollback in case of error implicitly when you are inside a transaction.
与其他 SQL 语言相比，您应该认为 Postgres 始终会在发生错误时隐式地处理提交/回滚，当您在事务中时。
Here is what the docis saying:
这是文档的内容：
Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.
事务是所有数据库系统的基本概念。事务的要点在于它将多个步骤捆绑到一个单一的、全有或全无的操作中。这些步骤之间的中间状态对于其他并发事务是不可见的，如果发生某些阻止事务完成的故障，那么这些步骤根本不会影响数据库。
CREATE OR REPLACE FUNCTION TEST1 ()
RETURNS VOID
LANGUAGE 'plpgsql'
AS $$
BEGIN
INSERT INTO table1 VALUES (1);
INSERT INTO table1 VALUES (2);
INSERT INTO table1 VALUES ('A');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;$$;
回答by Laurenz Albe
You cannot use transaction statements like SAVEPOINT, COMMITor ROLLBACKin a function. The documentationsays:
您不能使用像SAVEPOINT,COMMIT或ROLLBACK在函数中的事务语句。 文档说：
In procedures invoked by the
CALLcommand as well as in anonymous code blocks (DOcommand), it is possible to end transactions using the commandsCOMMITandROLLBACK.
在
CALL命令调用的过程中以及匿名代码块 (DOcommand) 中，可以使用命令COMMIT和结束事务ROLLBACK。
Ex negativo, since functions are not procedures that are invoked with CALL, you cannot do that in functions.
Ex negativo，因为函数不是用 调用的过程CALL，你不能在函数中这样做。
The BEGINthat starts a block in PL/pgSQL is different from the SQL statement BEGINthat starts a transaction.
在BEGIN启动PL / pgSQL的一个块是从SQL语句不同BEGIN的是启动一个事务。
Just remove the COMMITfrom your function, and you have the solution: since the whole function is always run inside a single transaction, any error in the third statement will lead to a ROLLBACKthat also undoes the first two statements.
只需COMMIT从您的函数中删除，您就有了解决方案：由于整个函数始终在单个事务中运行，因此第三个语句中的任何错误都将导致 aROLLBACK也撤消前两个语句。
