transactions - PostgreSQL no puede iniciar/finalizar transacciones en PL/pgSQL

runtime-error plpgsql

Question

Estoy buscando una aclaración sobre cómo garantizar una transacción atómica en una función plpgsql y dónde se establece el nivel de aislamiento para este cambio en particular en la base de datos.

En la función plpgsql que se muestra a continuación, quiero asegurarme de que TANTO la eliminación Y la inserción se realicen correctamente. Recibo un error cuando trato de envolverlos en una sola transacción:
ERROR: cannot begin/end transactions in PL/pgSQL .

¿Qué sucede durante la ejecución de la siguiente función si otro usuario ha agregado un comportamiento predeterminado para las circunstancias (''LLUVIA'', ''NOCHE'', ''45MPH'') después de que esta función haya eliminado la fila personalizada pero antes haya tenido la oportunidad de insertar la personalizada ¿fila? ¿Hay una transacción implícita que envuelve la inserción y la eliminación de modo que ambos se deshagan si otro usuario ha cambiado alguna de las filas a las que hace referencia esta función? ¿Puedo configurar el nivel de aislamiento para esta función?

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

Una función plpgsql se ejecuta automáticamente dentro de una transacción. Todo tiene éxito o todo falla. El manual:

Las funciones y los procedimientos de activación siempre se ejecutan dentro de una transacción establecida por una consulta externa; no pueden iniciar o confirmar esa transacción, ya que no habría contexto para que se ejecuten. Sin embargo, un bloque que contiene una cláusula EXCEPTION forma efectivamente una subtransacción que puede ser retrotraído sin afectar la transacción externa. Para más información sobre eso, vea la Sección 42.6.6.

Por lo tanto, si lo necesita, puede detectar una excepción que, en teoría, podría ocurrir (pero es muy poco probable).
Detalles sobre errores de captura en el manual.

Su función revisada y simplificada:

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;

Si realmente necesita comenzar / finalizar transacciones como se indica en el título, consulte los procedimientos de SQL en Postgres 11 o posterior ( CREATE PROCEDURE ). Relacionado:

• En PostgreSQL, ¿cuál es la diferencia entre un “Procedimiento almacenado” y otros tipos de funciones?

Answer 2

START TRANSACTION;
select foo() ;
COMMIT;

"Desafortunadamente, Postgres no tiene procedimientos almacenados, por lo que siempre debe administrar la transacción en el código de llamada" - a_horse_with_no_name

Transacción en un bloque de excepción - ¿cómo?