在PostgreSQL 8.2中嵌套事务？ [英] Nested transactions in postgresql 8.2?

问题描述

我正在研究应用数据库架构更新的脚本。我已经使用开始事务/提交设置了所有的SQL更新脚本。我在命令行上将这些脚本传递给psql。

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.

我现在需要在一个事务中同时应用多个脚本。到目前为止，我想出的唯一解决方案是从原始脚本集中删除启动事务/提交，然后将它们卡在新的启动事务/提交块中。我正在编写perl脚本来即时执行此操作。

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.

有效地，我想要嵌套事务，但我无法弄清楚在postgresql中该如何做。

Effectively I want nested transactions, which I can't figure out how to do in 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.

推荐答案

那么您可以使用SavePoints在Postgresql中使用嵌套事务。

Well you have the possibility to use nested transactions inside postgresql using SavePoints.

以下面的代码示例为例：

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.

这篇关于在PostgreSQL 8.2中嵌套事务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！