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

Ответ 1

Функция 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 1 FROM defaultbehavior
   WHERE  a = 'RAIN'
   AND    b = 'NIGHT'
   AND    c = '45MPH'
   );

RETURN QUERY
SELECT * FROM custombehavior WHERE ... ;

END
$func$  LANGUAGE plpgsql