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

问题描述

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

在如下所示的 plpgsql 函数中，我想确保删除和插入都成功.当我尝试将它们包装在单个事务中时出现错误:

<块引用>

错误:无法在 PL/pgSQL 中开始/结束事务

如果另一个用户为情况('RAIN'、'NIGHT'、'45MPH')添加了默认行为，在执行下面的函数期间会发生什么此函数删除了自定义行但之前它有机会插入自定义行吗?是否有包含插入和删除的隐式事务，以便在另一个用户更改了此函数引用的任一行时都回滚?我可以为这个函数设置隔离级别吗?

创建函数 foo(v_weather varchar(10), v_timeofday varchar(10), v_speed varchar(10),v_behavior varchar(10))返回 CUSTOMBEHAVIOR 集作为 $body$开始-- 如果这些行中的任何一行未注释，则运行时错误-- 开始事务 ISOLATION LEVEL READ COMMITTED;-- 或者，设置事务 ISOLATION LEVEL READ COMMITTED；从 CUSTOMBEHAVIOR 中删除其中天气 = 'RAIN' 和 timeofday = 'NIGHT' 和速度 = '45MPH';-- 如果没有默认行为插入自定义行为如果不存在(从 DEFAULTBEHAVIOR 中选择 id，其中 a = 'RAIN' 和 b = 'NIGHT' 和 c = '45MPH')然后插入自定义行为(天气、时间、速度、行为)价值观(v_weather, v_timeofday, v_speed, v_behavior);万一;返回查询select * from CUSTOMBEHAVIOR where ... ;-  犯罪;结尾$body$ 语言 plpgsql;

解决方案

plpgsql 函数 在事务内自动运行.要么全部成功，要么全部失败.手册:

<块引用>

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

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

您的功能经过审核和简化:

CREATE FUNCTION foo(v_weather text, v_timeofday 文本, v_speed 文本, v_behavior 文本)返回 SETOF 自定义行为语言 plpgsql AS$func$开始从自定义行为中删除WHERE 天气 = 'RAIN'AND timeofday = 'NIGHT'AND 速度 = '45MPH';插入自定义行为(天气、时间、速度、行为)选择 v_weather、v_timeofday、v_speed、v_behavior不存在的地方(从默认行为中选择WHERE a = '雨'AND b = '夜'AND c = '45英里/小时');返回查询SELECT * FROM custombehavior WHERE ...;结尾$func$;

---

如果您确实需要开始/结束事务，如标题所示，请查看 Postgres 11 或更高版本中的 SQL 过程(创建程序).见:

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

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.

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

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;

解决方案

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

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

---

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

• In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

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