存储过程 - PostgreSQL 无法在 PL/pgSQL 中开始/结束事务

Question

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

在下面显示的 plpgsql 函数中，我想确保删除和插入都成功。 当我尝试将它们包装在单个事务中时，我收到错误：

ERROR:  cannot begin/end transactions in PL/pgSQL

如果其他用户在此函数删除自定义行之后但在有机会插入自定义行之前为环境（"RAIN"、"NIGHT"、"45MPH"）添加了默认行为，则在执行以下函数期间会发生什么情况？ 是否有隐式事务包装插入和删除，以便在其他用户更改了此函数引用的任一行时回滚两者？我可以设置此功能的隔离级别吗？

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;

Answer 1

plpgsql 函数在事务中自动运行。要么成功，要么全部失败。手册：

函数和触发器过程始终在由外部查询建立的事务 — 它们无法启动或提交该事务，因为他们没有上下文执行。但是，包含EXCEPTION子句的块有效地形成一个子事务，可以在没有的情况下回滚影响外部事务。有关此内容的更多信息，请参见第 42.6.6 节。

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

您的职能经过审查和简化：

CREATE FUNCTION foo(v_weather text
                  , v_timeofday text
                  , v_speed text
                  , v_behavior text)
  RETURNS SETOF custombehavior
  LANGUAGE plpgsql 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$;

<小时 />

如果您确实需要开始/结束事务，如标题所示，请查看Postgres 11或更高版本（CREATE PROCEDURE）中的SQL过程。看：

• 在PostgreSQL中，"存储过程"和其他类型的函数有什么区别？

Answer 2

更新：在PostgreSQL版本11之后。 您可以在存储过程中控制事务。

===

==在版本 10 之前：

START TRANSACTION;
select foo() ;
COMMIT;

"不幸的是，Postgres没有存储过程，所以你总是需要在调用代码中管理事务" – a_horse_with_no_name

异常块中的交易 - 如何？