PostgreSQL：在 plpgsql 函数中回滚事务？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2422205/
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
PostgreSQL: Rolling back a transaction within a plpgsql function?
提问by jamieb
Coming from the MS SQL world, I tend to make heavy use of stored procedures. I'm currently writing an application uses a lot of PostgreSQL plpgsql functions. What I'd like to do is rollback all INSERTS/UPDATES contained within a particular function if I get an exception at any point within it.
来自 MS SQL 世界，我倾向于大量使用存储过程。我目前正在编写一个使用了很多 PostgreSQL plpgsql 函数的应用程序。我想要做的是回滚包含在特定函数中的所有 INSERTS/UPDATES，如果我在其中的任何一点遇到异常。
I was originally under the impression that each function is wrapped in it's own transaction and that an exception would automatically rollback everything. However, that doesn't seem to be the case. I'm wondering if I ought to be using savepoints in combination with exception handling instead? But I don't really understand the difference between a transaction and a savepoint to know if this is the best approach. Any advice please?
我最初的印象是每个函数都包含在它自己的事务中，并且异常会自动回滚所有内容。然而，情况似乎并非如此。我想知道是否应该将保存点与异常处理结合使用？但是我真的不明白事务和保存点之间的区别，不知道这是否是最好的方法。请问有什么建议吗？
CREATE OR REPLACE FUNCTION do_something(
_an_input_var int
) RETURNS bool AS $$
DECLARE
_a_variable int;
BEGIN
INSERT INTO tableA (col1, col2, col3)
VALUES (0, 1, 2);
INSERT INTO tableB (col1, col2, col3)
VALUES (0, 1, 'whoops! not an integer');
-- The exception will cause the function to bomb, but the values
-- inserted into "tableA" are not rolled back.
RETURN True;
END; $$ LANGUAGE plpgsql;
回答by Joshua D. Drake
A function doesrepresent a transaction. You do not have to wrap a function in BEGIN/COMMIT.
一个函数确实代表了一个交易。您不必在 BEGIN/COMMIT 中包装函数。
回答by Peter Krauss
You can't use commit or rollback command into the function, but you can use your function into a committed transaction,
您不能在函数中使用 commit 或 rollback 命令，但可以在已提交的事务中使用您的函数，
BEGIN TRANSACTION; SELECT do_something(); COMMIT;
开始交易；选择做某事（）；犯罪;
This SQL script only commits if there are no exceptions in do_something, then, it will rolling back the transaction of the function.
这个 SQL 脚本只有在 do_something 没有异常时才会提交，然后，它会回滚函数的事务。
回答by tom
The docssay this:
该文档这样说：
A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.
保存点是事务内的特殊标记，它允许回滚建立后执行的所有命令，将事务状态恢复到保存点时的状态。
They give examples too.
他们也举了例子。
Edit:
编辑：
You need to wrap a transactionin BEGIN and COMMIT commands.
您需要在 BEGIN 和 COMMIT 命令中包装事务。
a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands
通过使用 BEGIN 和 COMMIT 命令包围事务的 SQL 命令来设置事务
回答by gahooa
Savepoints can be used to emulate nested transactions. Because a postgresql transaction is a sequence of statements that will either be applied or discarded, savepoints can mark points within that sequence that allow rolling back to.
保存点可用于模拟嵌套事务。因为 postgresql 事务是一个将被应用或丢弃的语句序列，所以保存点可以标记该序列中允许回滚的点。
Since true nested transactions are not supported, this is your best bet (and a good one at that).
由于不支持真正的嵌套事务，这是您最好的选择（并且是一个很好的选择）。
