stored - establecer el nivel de aislamiento para los procedimientos almacenados postgresql

sintaxis para procedimientos almacenados en postgresql

Question

Esperemos que sea una pregunta simple, pero para la cual no he encontrado fácilmente una respuesta decente. Se me informa de manera confiable que los procedimientos almacenados (funciones de base de datos definidas por el usuario) en PostgreSQL (específicamente, la versión 9.0.4) son inherentemente transaccionales, en la medida en que se llaman a través de una instrucción SELECT que en sí misma es una transacción. Entonces, ¿cómo se elige el nivel de aislamiento del procedimiento almacenado? Creo que en otros DBMS, el bloque transaccional deseado se incluiría en un bloque START TRANSACTION para el cual el nivel de aislamiento deseado es un parámetro opcional.

Como ejemplo específico, digamos que quiero hacer esto:

CREATE FUNCTION add_new_row(rowtext TEXT)
RETURNS VOID AS 
$$
BEGIN
        INSERT INTO data_table VALUES (rowtext);
        UPDATE row_counts_table SET count=count+1;
END;
$$  
LANGUAGE plpgsql
SECURITY DEFINER;

E imagine que quiero asegurarme de que esta función siempre se realice como una transacción serializable (sí, sí, PostgreSQL SERIALIZABLE no es serializable propiamente dicho, pero ese no es el punto). No quiero exigir que se llame como

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT add_new_row(''foo'');
COMMIT;

Entonces, ¿cómo presiono el nivel de aislamiento requerido hacia abajo en la función? Creo que no puedo simplemente poner el nivel de aislamiento en la declaración BEGIN , como dice el manual

Es importante no confundir el uso de BEGIN / END para agrupar sentencias en PL / pgSQL con los comandos SQL de nombre similar para el control de transacciones. PL / pgSQL''s BEGIN / END son solo para agrupar; No inician ni finalizan una transacción. 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.

El enfoque más obvio para mí sería utilizar SET TRANSACTION en algún lugar de la definición de la función, por ejemplo:

CREATE FUNCTION add_new_row(rowtext TEXT)
RETURNS VOID AS 
$$
BEGIN
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        INSERT INTO data_table VALUES (rowtext);
        UPDATE row_counts_table SET count=count+1;
END;
$$  
LANGUAGE plpgsql
SECURITY DEFINER;

Si bien esto sería aceptado, no está claro de qué manera puedo confiar en que esto funcione. La documentation para SET TRANSACTION dice

Si SET TRANSACTION se ejecuta sin un START TRANSACTION o COMENZAR antes, parecerá que no tiene efecto, ya que la transacción finalizará de inmediato.

Lo que me deja perplejo, ya que si llamo a un SELECT add_new_row(''foo''); solitario SELECT add_new_row(''foo''); declaración que esperaría (siempre que no haya deshabilitado la confirmación automática) el SELECT se ejecute como una transacción de una sola línea con el nivel de aislamiento predeterminado de la sesión.

El documentation también dice:

El nivel de aislamiento de la transacción no se puede cambiar después de que se haya ejecutado la primera consulta o declaración de modificación de datos (SELECCIONAR, INSERTAR, BORRAR, ACTUALIZAR, FETCH o COPIAR) de una transacción.

Entonces, ¿qué sucede si se llama a la función desde una transacción con un nivel de aislamiento inferior, por ejemplo ,:

START TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE row_counts_table SET count=0;
SELECT add_new_row(''foo'');
COMMIT;

Para una pregunta extra: ¿el lenguaje de la función hace alguna diferencia? ¿Se establecería un nivel de aislamiento diferente en PL / pgSQL que en SQL plano?

Soy un fan de los estándares y las mejores prácticas documentadas, por lo que cualquier referencia decente sería apreciada.

Answer 1

El aislamiento de transacciones significa los cambios realizados en otras transacciones concurrentes a las que puede acceder.

Si quieres serializar la ejecución tienes que usar bloqueos.

Se puede usar después del disparo de fila y el recuento de actualizaciones. "UPDATE row_counts_table" bloqueará la tabla y todas las transacciones se serializarán. Es lento.

En tu ejemplo tienes dos afirmaciones. La inserción se ejecuta pero la actualización tiene que esperar otras transacciones y el recuento no es válido en este período.

Answer 2

El lenguaje de la función no hace diferencia alguna.

Esto falla:

test=# create function test() returns int as $$
  set transaction isolation level serializable;
  select 1;
$$ language sql;
CREATE FUNCTION
test=# select test();
ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL function "test" statement 1

Tenga en cuenta que en su ejemplo particular, puede hacer esto usando un disparador en su primera tabla. Solo asegúrese de que las actualizaciones de conteo de filas se realicen en un orden consistente para evitar bloqueos, y lo hará bien en el modo de lectura repetible.

Soy un fan de los estándares

Los PL / lenguajes son específicos de la plataforma.

Answer 3

En PG sus procedimientos no son transacciones separadas. Ese es el procedimiento almacenado participa en una transacción existente.

BEGIN TRAN

SELECT 1;
SELECT my_proc(99);

ROLLBACK TRAN;

Dicho esto, debe establecer el nivel de transacción en el que se inicia la transacción que está fuera del procedimiento almacenado.

Una opción sería configurar el servidor para que se ejecute en el aislamiento que mayormente desea usar y hacer un SET para los casos perimetrales en los que difiere de la configuración del servidor.

Answer 4

No puedes hacer eso.

Lo que podría hacer es hacer que su función verifique cuál es el nivel de aislamiento de la transacción actual y abortar si no es el que desea. Puede hacer esto ejecutando SELECT current_setting(''transaction_isolation'') y luego verificando el resultado.