PostgreSQL无法在PL/pgSQL中开始/结束事务 [英] PostgreSQL cannot begin/end transactions in PL/pgSQL

问题描述

我正在寻求有关如何确保在plpgsql函数中进行原子事务的澄清，以及针对数据库的此特定更改设置隔离级别的位置.

I am seeking clarification of how to ensure an atomic transaction in a plpgsql function, and where the isolation level is set for this particular change to the database.

在下面显示的plpgsql函数中，我要确保删除和插入都成功.尝试将它们包装在单个事务中时出现错误:
ERROR: cannot begin/end transactions in PL/pgSQL.

In the plpgsql function shown below, I want to make sure that BOTH the deletion AND the insertion succeed. I am getting an error when I try to wrap them in a single transaction:
ERROR: cannot begin/end transactions in PL/pgSQL.

在下面的函数执行过程中，如果另一个用户添加了默认情况下的行为("RAIN"，"NIGHT"，"45MPH")，在 之后，该函数删除了自定义行，但是之前有没有机会插入自定义行?是否存在一个包含插入和删除的隐式事务，以便在另一个用户更改了此函数引用的任一行时都将它们回滚?我可以为此功能设置隔离级别吗?

What happens during execution of the function below if another user has added a default behavior for circumstances ('RAIN', 'NIGHT', '45MPH') after this function has deleted the custom row but before it has had a chance to insert the custom row? Is there an implicit transaction wrapping the insert and delete so that both are rolled back if another user has changed either of the rows referenced by this function? Can I set the isolation level for this function?

create function foo(v_weather varchar(10), v_timeofday varchar(10), v_speed varchar(10),
   v_behavior varchar(10))
   returns setof CUSTOMBEHAVIOR
   as $body$
   begin

      -- run-time error if either of these lines is un-commented

      -- start transaction ISOLATION LEVEL READ COMMITTED;
      -- or, alternatively, set transaction ISOLATION LEVEL READ COMMITTED;


      delete from CUSTOMBEHAVIOR 
      where weather = 'RAIN' and timeofday = 'NIGHT' and speed= '45MPH' ;

      -- if there is no default behavior insert a custom behavior

      if not exists
        (select id from DEFAULTBEHAVIOR where a = 'RAIN' and b = 'NIGHT' and c= '45MPH') then
         insert into CUSTOMBEHAVIOR
           (weather, timeofday, speed, behavior)
         values
           (v_weather, v_timeofday, v_speed, v_behavior);
      end if;

      return QUERY
      select * from CUSTOMBEHAVIOR where ...   ;

      -- commit;

   end
   $body$  LANGUAGE plpgsql;

推荐答案

plpgsql函数自动在事务内部运行.一切都成功或失败. 手册:

A plpgsql function automatically runs inside a transaction. It all succeeds or it all fails. The manual:

功能和触发过程始终在 由外部查询建立的事务-它们无法启动或 提交该交易，因为他们没有上下文可以 执行.但是，包含EXCEPTION子句的块 有效地形成可以回滚而无需回退的子交易 影响外部交易.有关更多信息，请参见第42.6.6节

Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. For more about that see Section 42.6.6.

因此，如果需要，您可以捕获理论上可能会发生的异常(但可能性很小).
手册中有关捕获错误的详细信息.

So, if you need to, you can catch an exception that theoretically might occur (but is very unlikely).
Details on trapping errors in the manual.

您的职能已审核并简化:

Your function reviewed and simplified:

CREATE FUNCTION foo(v_weather text
                  , v_timeofday text
                  , v_speed text
                  , v_behavior text)
  RETURNS SETOF custombehavior AS
$func$
BEGIN

DELETE FROM custombehavior
WHERE  weather = 'RAIN'
AND    timeofday = 'NIGHT'
AND    speed = '45MPH';

INSERT INTO custombehavior (weather, timeofday, speed, behavior)
SELECT v_weather, v_timeofday, v_speed, v_behavior
WHERE  NOT EXISTS (
   SELECT FROM defaultbehavior
   WHERE  a = 'RAIN'
   AND    b = 'NIGHT'
   AND    c = '45MPH'
   );

RETURN QUERY
SELECT * FROM custombehavior WHERE ... ;

END
$func$  LANGUAGE plpgsql;

---

如果您确实需要如标题所示 开始/结束交易 ，请查看Postgres 11或更高版本中的SQL过程(

---

If you actually need to begin/end transactions like indicated in the title look to SQL procedures in Postgres 11 or later (CREATE PROCEDURE). Related:

这篇关于PostgreSQL无法在PL/pgSQL中开始/结束事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！