如何在PostgreSQL函数中使用COMMIT和ROLLBACK

Question

我正在使用三个插入语句，如果第三个语句出现错误，我希望回滚第一个和第二个语句。如果没有办法做到这一点，请告诉我在PostgreSQL中处理此类问题的不同方法。

如果我使用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;$$;

上述代码无法工作；COMMIT和ROLLBACK在PostgreSQL函数中不受支持。

- Rahul Gour

---

这是Postgres默认的行为方式（即如果您的任何语句失败，则不会提交任何语句）。 - Nick Barnes

你可以通过使用双向链接来实现，但我建议重新考虑设计。 - Vao Tsun

Answer 1

在函数中不能使用诸如SAVEPOINT、COMMIT或ROLLBACK这样的事务语句。根据文档的说明：

在由CALL命令调用的过程中以及在匿名代码块(DO命令)中，可以使用COMMIT和ROLLBACK命令来结束事务。

由于函数不是通过CALL调用的过程，所以你不能在函数中这样做。

在PL/pgSQL中开始一个块级别的语句BEGIN与开始一个事务的SQL语句BEGIN是不同的。

只需从函数中移除COMMIT，您就可以解决问题：由于整个函数总是在单个事务内运行，第三个语句中的任何错误都将导致执行ROLLBACK，同时撤消前两个语句。

- Laurenz Albe

---

发布之前没有测试，谢谢提供这个信息，我已经进行了测试，它运行良好。 - Rahul Gour

但在我的情况下，我需要在pstgresql_fdw操作中插入数据后使用commit。所以，您能否建议如何在插入后提交数据。 - UmaShankar

@UmaShankar 你可以使用 dblink 来实现。 - Laurenz Albe

@LaurenzAlbe，如果我需要在一张表中更新某些行，并通过if语句决定是否应该在另一张表中删除相应的行或回滚（我的意思是根据一些过程逻辑），那么没有办法在函数内部将if与回滚组合在一起吗？ - EngineerSpock

这很有趣，这个语句中是否有任何参考资料？> 你不能在函数中使用SAVEPOINT、COMMIT或ROLLBACK等事务语句。也许是从文档中获取的？@LaurenzAlbe - Gujarat Santana

@GujaratSantana 我已经从文档中添加了一条引用。 - Laurenz Albe

Answer 2

与其他SQL语言相比，当您处于事务中时，Postgres始终会隐式地处理提交/回滚以应对错误。以下是doc的说法:

“事务是所有数据库系统的基本概念。事务的核心点在于将多个步骤捆绑成一个单一的、全有或全无的操作。步骤之间的中间状态对其他并发事务不可见，如果出现某些故障导致事务无法完成，则这些步骤都不会影响数据库。”

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;$$;

- godot

---

在函数中，您不能使用SAVEPOINT、COMMIT或ROLLBACK等事务语句。 - Sergey Menshov

Answer 3

为了事务控制，我们使用PROCEDURE（来自postgresql11），而不是FUNCTION。
FUNCTION不支持函数内的事务。这是PostgreSQL中FUNCTION和PROCEDURE之间的主要区别。

你的代码应该是：

    CREATE OR REPLACE PROCEDURE 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;$$;

- Sidharth Priyadarshi