PostgreSQL не может начинать / завершать транзакции в PL/pgSQL

Я ищу разъяснения о том, как обеспечить атомарную транзакцию в функции 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;
Источник

2 ответа

Решение

Функция plpgsql автоматически запускается внутри транзакции. Все это удается или все не удается. Руководство:

Функции и процедуры триггера всегда выполняются в транзакции, установленной внешним запросом - они не могут запустить или зафиксировать эту транзакцию, поскольку не было бы контекста, в котором они могли бы выполняться. Однако блок, содержащий EXCEPTION Предложение эффективно формирует субтранзакцию, которую можно откатить, не влияя на внешнюю транзакцию. Подробнее об этом см. Раздел 42.6.6.

Так что, если вам нужно, вы можете поймать исключение, которое теоретически может возникнуть (но очень маловероятно).
Подробности об ошибках перехвата в руководстве.

Ваша функция рассмотрена и упрощена:

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;

Если вам действительно нужно начинать / завершать транзакции, как указано в заголовке, обратитесь к процедурам SQL в Postgres 11 или новее (CREATE PROCEDURE). Связанные с:

Источник 
START TRANSACTION;
select foo() ;
COMMIT;

"К сожалению, у Postgres нет хранимых процедур, поэтому вам всегда нужно управлять транзакцией в вызывающем коде" - a_horse_with_no_name

Транзакция в блоке исключений - как?

Источник
Другие вопросы по тегам