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;

换句话说，如果在函数执行过程中发生错误，是否会回滚任何更改?如果这不是默认行为，我怎样才能使函数事务性?

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 更新:对可以进行事务控制的顶级PROCEDURE 的支持有限.您仍然无法在常规 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 和 ROLLBACK TO 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 ... EXCEPT 错误处理.如果函数内出现错误并且未处理，则调用该函数的事务将中止.中止的事务无法提交，如果他们尝试提交 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;

(如果 SELECT 引发错误，COMMIT 将失败).

(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屋！