The cheapest, fastest, and most reliable components are those that aren’t there.
Gordon Bell.
En algunos casos, necesitamos que ciertas operaciones de una transacción de base de datos sean realizadas aunque la transacción se deshaga con un
rollback. Por ejemplo, imaginemos que tenemos una función o procedimiento almacenado en la que hemos agrupado 10 operaciones. Cuando dicha función se llame, se ejecutará en un contexto transaccional, es decir, se realizará una transacción con dichas 10 operaciones de forma atómica (o todas, o ninguna). Supongamos que queremos garantizar que un subconjunto de esas operaciones (por ejemplo un par de ellas que hacen auditoría de la propia llamada) se realicen siempre, con independencia de las demás: en este caso necesitamos una transacción autónoma.
Las transacciones autónomas (
Autonomous Transactions) permiten suspender el contexto de la transacción en curso (llamante), realizar una transacción independiente y reanudar el contexto de la transacción llamante sin afectar su estado. Los casos de uso típicos de uso son funcionalidades cuyos cambios queremos que persistan con independencia de la transacción que las llama: trazado (logging), operaciones de auditoría, contadores, funciones de propósito general...etc.
Un caso que se me ha dado recientemente es el de realizar una transacción batch muy muy larga (de importación de gran cantidad de datos), donde la unidad de importación supone operaciones de modificación (inserción o actualización) en varias tablas. Imaginemos que importamos usuarios con todos sus datos: dirección, provincia, datos bancarios, teléfonos, etc... Cada usuario puede suponer 8 o 10 operaciones en diversas tablas. Si tenemos que importar 30.000 usuarios, podemos encontrarnos con una transacción compuesta por 300.000 operaciones de inserción/actualización. Este tipo de transacciones tan grandes exige muchos recursos de un servidor de base de datos: crea ocupación de memoria y genera muchos puntos de bloqueos que podrían socavar el desempeño de un sistema en producción. Una forma de solucionar un problema así es realizar una confirmación de datos por cada unidad de importación. Es decir, que el bucle general de importación (que ya se ejecuta en un contexto transaccional) llame a una función que realiza y confirma la tarea de importación de una unidad (un usuario en el ejemplo) en una transacción autónoma, con lo cual cada usuario se procesaría de forma atómica, sin que la transacción general acumule trabajo.
Con Oracle, las transacciones autónomas se declaran sin mayor dificultad usando la directiva:
PRAGMA AUTONOMOUS_TRANSACTION;
Sin embargo,
en PostgreSQL las transacciones autonomas no están soportadas. No hay forma de hacerlo de forma declarativa. Sólo hay una forma de hacerlo (con dos enfoques, como veremos enseguida): realizando la parte que queremos que sea
autónoma en otro contexto transaccional, es decir, en otra conexión a la base de datos:
- Usando un lenguaje de programación externo (untrusted). Es decir, reducir la función PL/PgSQL a la parte que queremos garantizar su ejecución (la parte que queremos que sea autónoma) y realizar el resto en un lenguaje de programación externo a la base de datos. La función se llamará en una nueva conexión
- Usando dblink. Esta opción, es parecida a la anterior, pero usando siempre PL/PgSQL. Es decir, realizamos la transacción autónoma en una nueva conexión usando dblink.
dblink
dblink es un módulo del
contrib de PostgreSQL que permite realizar conexiones y realizar consultas a bases de datos locales o remotas dentro de una sesión. Con un ejemplo se verá más claro:
SELECT nombre, apellidos
FROM usuario INNER JOIN
dblink('dbname=otrabasededatos port=5432 host=localhost_u_otro' ||
'user=usuario password=password',
'SELECT direccion, poblacion, provincia from usuario')
AS u(direccion varchar, poblacion varchar, provincia varchar)
ON usuario.id = u.id;
En el anterior ejemplo, realizamos un JOIN entre dos tablas de dos bases de datos distintas (en el mismo host u otro, eso da igual). Es decir, en la sesión en curso, y a través de las funciones de dblink, se crea una conexión sobre la que enviamos nuestra query. Si esto lo hacemos contra el mismo servidor y misma base de datos...
voilá: ya tenemos una conexión nueva y, por tanto, un contexto transaccional diferente en el que podemos ejecutar nuestra transacción de forma independiente (es otra conexión).
Veamos un ejemplo de cómo usarlo. Supongamos el ejemplo del inicio, donde tenemos un cursor que recorremos en el que, para cada registro, debemos realizar un par de operaciones transaccionales de forma independiente (autónoma). Aprovecharemos el ejemplo también para ilustrar cómo se puede realizar una inserción y obtener la clave del elemento recién insertado para la siguiente sentencia.
for usuario in (select * from iusuarios) loop
raise notice 'Procesando usuario->, %',usuario.id || '-' || usuario.nombre;
PERFORM dblink_connect_u('dbname=dbname connect_timeout=5');
PERFORM dblink_exec('begin;');
l_dbl_sql :=
'insert into u2 (name,edad) values (' ||
quote_nullable(l_name) || ', ' ||
coalesce(l_edad::varchar,'null') || ', ' ||
'0) returning id;';
raise notice '[ALTA] SQL INSERT u2: %', l_dbl_sql;
select id into l_user_id from dblink(l_dbl_sql) as d(id int4);
l_dbl_sql :=
'update otra_tabla set date = now() where id_user = ' || l_user_id || ';';
raise notice '[ALTA] SQL UPDATE otra_tabla: %', l_dbl_sql;
PERFORM dblink_exec(l_dbl_sql);
PERFORM dblink_exec('commit;');
PERFORM dblink_disconnect();
end loop;
Examinaremos el ejemplo, porque hay cosas interesantes que quiero destacar:
- Linea 4: es importante especificar el timeout, para evitar efectos indeseables. Dado que se trata de la misma base de datos en el mismo servidor, no hay que especificar nada más.
- Linea 5: inicio de la transacción, ya que hay más de una sentencia
- Líneas 9 y 10: uso de las funciones quote_nullable() y coalesce() para garantizar el correcto escape de valores y la generación de 'null' cuando existan valores nulos.
- Línea 14: ejemplo de ejecución de sentencia INSERT con dblink para obtención del registro recién insertado.
- Línea 19: ejemplo de ejecución de sentencia UPDATE (o cualquier otra sentencia que no devuelva resultados)
- Línea 21: confirmación de la transacción
- Línea 22: desconexión
- Líneas 11 y 17: es muy importante asegurarse de que terminamos las sentencias que enviamos a dblink con el separador de terminación punto y coma (;). Un olvido puede hacer que nuestra función se detenga esperando la terminación.
Finalmente, recomiendo usar las funciones quote_nullable() para las fechas y cadenas y coalesce() para los valores numéricos para garantizar una correcta sintaxis y escapado de las sentencias. Si estás usando PostgreSQL 9.1 o superior puedes evitar ese engorro y usar la enormemente más cómoda función
format().
Referencias y más información:
