PostgreSQL函数是事务性的吗？ [英] Are PostgreSQL functions transactional?

问题描述

PostgreSQL函数是否可以自动执行以下事务？

Is a PostgreSQL function such as the following automatically transactional?

CREATE OR REPLACE FUNCTION refresh_materialized_view(name)
  RETURNS integer AS
$BODY$
 DECLARE
     _table_name ALIAS FOR $1;
     _entry materialized_views%ROWTYPE;
     _result INT;
 BEGIN          

     EXECUTE 'TRUNCATE TABLE ' || _table_name;

     UPDATE materialized_views
     SET    last_refresh = CURRENT_TIMESTAMP
     WHERE  table_name = _table_name;

     RETURN 1;
END
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

换句话说，如果函数执行期间发生错误，是否会回滚任何更改？如果这不是默认行为，该如何使函数 transactional ？

In other words, if an error occurs during the execution of the function, will any changes be rolled back? If this isn't the default behavior, how can I make the function transactional?

推荐答案

PostgreSQL 12更新：对顶级过程可以执行交易控制。您仍然无法使用常规的SQL调用函数来管理事务，因此除使用新的顶级过程外，以下内容仍然适用。

PostgreSQL 12 update: there is limited support for top-level PROCEDUREs that can do transaction control. You still cannot manage transactions in regular SQL-callable functions, so the below remains true except when using the new top-level procedures.

函数是调用它们的事务的一部分。如果事务回滚，它们的影响也会回滚。如果事务提交，他们的工作也将提交。该函数中的任何 BEGIN ... EXCEPT 块都像（并在后台使用）保存点一样运行，如 SAVEPOINT 和回滚到保存点 SQL语句。

Functions are part of the transaction they're called from. Their effects are rolled back if the transaction rolls back. Their work commits if the transaction commits. Any BEGIN ... EXCEPT blocks within the function operate like (and under the hood use) savepoints like the SAVEPOINT and ROLLBACK TO SAVEPOINT SQL statements.

该函数要么全部成功，要么全部失败，除非 BEGIN ...除了错误处理。如果在函数内引发错误并且未处理错误，则调用该函数的事务将中止。中止的事务无法提交，如果它们尝试提交 COMMIT ，则与其他任何事务一样，被视为 ROLLBACK 错误。观察：

The function either succeeds in its entirety or fails in its entirety, barring BEGIN ... EXCEPT error handling. If an error is raised within the function and not handled, the transaction calling the function is aborted. Aborted transactions cannot commit, and if they try to commit the COMMIT is treated as ROLLBACK, same as for any other transaction in error. Observe:

regress=# BEGIN;
BEGIN
regress=# SELECT 1/0;
ERROR:  division by zero
regress=# COMMIT;
ROLLBACK

查看由于零除而处于错误状态的事务处理方式，在 COMMIT 上回滚？

See how the transaction, which is in the error state due to the zero division, rolls back on COMMIT?

如果您调用的函数没有显式的事务，则规则完全相同以及其他任何Pg语句：

If you call a function without an explicit surounding transaction the rules are exactly the same as for any other Pg statement:

BEGIN;
SELECT refresh_materialized_view(name);
COMMIT;

（其中 COMMIT 如果 SELECT 引发了错误）。

(where COMMIT will fail if the SELECT raised an error).

PostgreSQL目前还不支持函数或过程中的自主事务。可以独立于调用事务进行提交/回滚。可以使用新会话通过 dblink 进行模拟。

PostgreSQL does not (yet) support autonomous transactions in functions, where the procedure/function could commit/rollback independently of the calling transaction. This can be simulated using a new session via dblink.

但，PostgreSQL中存在非事务性或不完全事务性的事物。如果它在正常的 BEGIN中具有非交易行为；做东西; COMMIT; 块，它在函数中也具有非事务性行为。例如， nextval 和 setval ， TRUNCATE 等。

BUT, things that aren't transactional or are imperfectly transactional exist in PostgreSQL. If it has non-transactional behaviour in a normal BEGIN; do stuff; COMMIT; block, it has non-transactional behaviour in a function too. For example, nextval and setval, TRUNCATE, etc.

这篇关于PostgreSQL函数是事务性的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！