postgresql 8.2 中的嵌套事务？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/204557/
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
Nested transactions in postgresql 8.2?
提问by Michael Kohne
I'm working on scripts that apply database schema updates. I've setup all my SQL update scripts using start transaction/commit. I pass these scripts to psql on the command line.
我正在研究应用数据库架构更新的脚本。我已经使用开始事务/提交设置了我所有的 SQL 更新脚本。我在命令行上将这些脚本传递给 psql。
I now need to apply multiple scripts at the same time, and in one transaction. So far the only solution I've come up with is to remove the start transaction/commit from the original set of scripts, then jam them together inside a new start transaction/commit block. I'm writing perl scripts to do this on the fly.
我现在需要在一个事务中同时应用多个脚本。到目前为止，我想出的唯一解决方案是从原始脚本集中删除启动事务/提交，然后将它们放在一个新的启动事务/提交块中。我正在编写 perl 脚本来即时执行此操作。
Effectively I want nested transactions, which I can't figure out how to do in postgresql.
实际上，我想要嵌套事务，但我不知道如何在 postgresql 中执行此操作。
Is there any way to do or simulate nested transactions for this purpose? I have things setup to automatically bail out on any error, so I don't need to continue in the top level transaction if any of the lower ones fail.
有没有办法为此目的做或模拟嵌套事务？我已经设置了可以自动解决任何错误的东西，所以如果任何较低的事务失败，我不需要继续在顶级事务中。
采纳答案by MysticSlayer
Well you have the possibility to use nested transactions inside postgresql using SavePoints.
好吧，您可以使用 SavePoints 在 postgresql 中使用嵌套事务。
Take this code example:
以这个代码示例为例：
CREATE TABLE t1 (a integer PRIMARY KEY);
CREATE FUNCTION test_exception() RETURNS boolean LANGUAGE plpgsql AS
$$BEGIN
INSERT INTO t1 (a) VALUES (1);
INSERT INTO t1 (a) VALUES (2);
INSERT INTO t1 (a) VALUES (1);
INSERT INTO t1 (a) VALUES (3);
RETURN TRUE;
EXCEPTION
WHEN integrity_constraint_violation THEN
RAISE NOTICE 'Rollback to savepoint';
RETURN FALSE;
END;$$;
BEGIN;
SELECT test_exception();
NOTICE: Rollback to savepoint
test_exception
----------------
f
(1 row)
COMMIT;
SELECT count(*) FROM t1;
count
-------
0
(1 row)
Maybe this will help you out a little bit.
也许这会对你有所帮助。
回答by Michael Kohne
I've ended up 'solving' my problem out of band - I use a perl script to re-work the input scripts to eliminate their start transaction/commit calls, then push them all into one file, which gets it's own start transaction/commit.
我最终在带外“解决”了我的问题 - 我使用 perl 脚本重新编写输入脚本以消除它们的启动事务/提交调用，然后将它们全部推送到一个文件中，从而获得它自己的启动事务/犯罪。
