Archivo de la categoría: postgreSQL

Articulos sobre postgreSQL, en inglés y en español

[PostgreSQL] Crear triggers dinámicos con postgreSQL

¿Qué es un trigger dinámico?, es aquel trigger que la metadata de en cual tabla/campo va a operar es pasado de manera dinámica (parámetros) a la función de trigger (trigger function).

¿Y para qué sirve?, bueno, imaginen un sistema dónde cada operación debe ser por ejemplo, agregada a una tabla auditoría, o por ejemplo, que una operación en una tabla, causa una operación en otra tabla, que aunque el código sea *casi* el mismo, depende de la tabla que dispara el trigger, qué tabla va a operar.

La utilidad de esto es simplemente poder re-utilizar una única funcion trigger para diversas acciones a ser tomadas de acuerdo a los parámetros recibidos.

El ejemplo

Este ejemplo se construyó durante un taller de postgreSQL que estaba dictando, el criterio era el siguiente:

  • El trigger toma el valor insertado en una tabla
  • Busca dicho valor en otra tabla, usando otro campo como criterio
  • Si el valor no existe, retorna NULL
  • Si el valor existe, retorna el objeto NEW del disparador, con lo cual la inserción sí ocurre

Los campos TG_*

En los disparadores de postgreSQL se cuenta con un conjunto de variables muy útiles, como son:

  • TG_OP: define la operación realizada en la tabla (INSERT, UPDATE o DELETE)
  • TG_TABLE_SCHEMA: define el schema al que pertenece la tabla (ej: “public”)
  • TG_TABLE_NAME: define el nombre de la tabla que disparó este trigger
  • TG_ARGV: es un arreglo (array []) que contiene los parámetros enviados a una función de trigger.

Con esto podemos hacer cualquier tipo de condicional en la función de trigger, reduciendo el código a sólo una función que se usará, de acuerdo a los parámetros que se le pasen.

La Función

CREATE OR REPLACE FUNCTION permitir_insert() RETURNS trigger AS

Comenzamos con la definición, la llamaremos “permitir_insert()” y obviamente, retorna un trigger.

luego, en la sección de declaración, definimos las únicas variables que vamos a necesitar para este ejemplo:

 vrecord integer;
campo text;
tabla text;
id int;
rc int;

BEGIN

En el cuerpo de la función, preguntamos qué tabla y campo voy a consultar para verificar si permito o no el insert, esto se hace consultando a TG_ARGV:

 tabla := TG_ARGV[0];
campo := TG_ARGV[1];

Como ven, ya que podamos recibir cualquier cantidad de parámetros en el trigger, las opciones son infinitas.

Ahora, necesito saber que hay en el campo trigger (NEW.campo), en este caso, debo reemplazar “campo” con el nombre del campo de la tabla que disparó el trigger (y que voy a consultar en otra tabla), como en mi caso el valor de el “campo” en todas las “tablas” que usarán esta función de trigger es entero, he declarado “vrecord” integer, si su tipo será distinto, sería conveniente declararlo “anyelement”.

EXECUTE 'SELECT ($1).' || quote_ident(campo) || '::text' INTO vrecord USING NEW;

Lo que hace ese código, es ejecutar un $1.”campo”::text (dónde campo es una variable) y mete su valor en la variable “vrecord”, se usa el objeto NEW para la sustitución del $1.

Ejemplo, si el campo se llama “piezas“, ese “EXECUTE” quedaría “NEW.piezas” y entonces metería el valor de NEW.piezas en la variable “vrecord”.

¿sencillo, no?.

La consulta

Ahora, debo verificar que en el campo “campo”::text de la tabla tabla::text (variable) existe un valor con lo que está contenido en “vrecord”, de lo contrario retorno NULL y el trigger no se ejecuta (la operación de inserción no ocurre).

Sería algo como un “constraint” pero sin usar reglas de constraint, ¿entendido?.

IF TG_OP = 'INSERT' THEN

EXECUTE format('SELECT '||quote_ident(campo)||' FROM '||quote_ident(tabla)||' WHERE '||quote_ident(campo)||' =$1') USING vrecord INTO id;

En este caso, he ejecutado un SELECT (SELECT “campo”::text FROM “tabla”::text WHERE campo::text = vrecord), claro que haciendo las conversiones y los reemplazos respectivos.

El valor de esa ejecución lo agrego a la variable id.

Si adicionalmente se desea averiguar si esa consulta anterior retornó filas, colocamos seguidamente al EXECUTE:

GET DIAGNOSTICS rc = ROW_COUNT;

Si “rc” es igual a cero, entonces no existe el valor “vrecord” en el campo “campo” de la tabla “tabla”, caso contrario, se retorna NEW.

 IF rc = 0 THEN
    RAISE EXCEPTION 'no existe el valor % en el campo % de la tabla %', vrecord, campo, tabla;
    RETURN NULL;
END IF;
RETURN NEW;

Y listo!, definimos el cierre y que esta es una función VOLATILE:

END;
LANGUAGE plpgsql VOLATILE

Y ya podemos usarla.

Usando la función dinámica

Para usar la función dinámica, simplemente creamos un trigger en cada tabla que necesite convocarla, pasando como parámetros de la función trigger la tabla referencia y el campo que debe evaluar, ejemplo:

CREATE TRIGGER trg_insert_detalle_reportes
BEFORE INSERT
ON reportes
FOR EACH ROW
EXECUTE PROCEDURE permitir_insert('reportes', 'id_reporte');

Se insertará un detalle de reporte, solamente si el valor de “id_reporte” aparece en la tabla “reportes”.

Conclusiones

Sé que parece muy “rebuscado” un ejemplo que bien podría salir con una clave foránea, pero sirve para el hecho de demostrar la posibilidad de obtener e iterar sobre el objeto NEW, consultar metadata al “information_schema” o realizar cualquier operación de manera dinámica, pasando parámetros y consultando las variables mágicas TG_* de postgreSQL.

¿Se les ocurre alguna idea para estos triggers dinámicos? …

Del por qué un desarrollador NO ES un Database Administrator?

O del por qué podría ser, pero debería primero cambiarse la camisa …

Este POST no busca explicar postgreSQL, ni siquiera es un artículo acerca de trucos o buenas prácticas, es simplemente una reflexión acerca de cómo pequeñas cosas que muchos pasan desapercibidas causan impacto profundo en el diseño de una aplicación.

Preámbulo

Tomé un servidor físico GNU/Linux que únicamente ejecutaba una base de datos y lo mudé a una máquina virtual restringida (¡conchale!, hay que ahorrar recursos!, pensé), pensando que todo quedaría bien.

Sin embargo, los usuarios del sistema (en producción) comenzaron a quejarse de lentitud, además, los administradores de sistemas comenzaron a notar excesivos picos de uso de CPU (¿en un equipo que sólo tiene postgreSQL?) e incluso en varias oportunidades se iba a SWAP.

Antes de devolver la base de datos al equipo físico, decidí hacer una revisión (les dije, “no solo monto sistemas, también sé de programación y mucho de base de datos, lo olvidan?”) y estas son las impresiones de la muy breve (pero exitosa) revisión.

El análisis

Lo primero que siempre hago cuando voy a analizar un sistema conectado a postgreSQL, es activar el slow log, esto es, comenzar a medir aquellos queries que consumen más del tiempo que uno supone debería asumir cierto tipo de consulta; paralelamente es daba una clase rápida de postgreSQL, de la importancia de entender el ciclo de parsing/análisis de una consulta SQL y más aún, de la importancia de entender SQL; por ejemplo, dos casos reales extraídos de este caso:

“fecha_inscrito” es un campo VARCHAR, donde almacenan algo como “12/04/2012”; si desean sacar el año, hacen un SPLIT de la cadena con un SUBSTR y sacan el tercer valor luego del “/” … ¿les parece eso correcto?, pues veamos el resultado:

postgres=# SELECT substr(’12/04/2012′, 6);
substr
——–
/2012
(1 fila)
Duración: 61,300 ms

Versus:

postgres=# SELECT EXTRACT(year from ‘2012-04-12’::date);
date_part
———–
2012
(1 fila)
Duración: 0,468 ms

Y comenzaron las alarmas!, 60 milisegundos menos!, pero chamo, a mi no me gusta trabajar con formato ANSI!, y luego les recuerdo que existe la variable de conexión DATESTYLE:

postgres=# SET datestyle to sql;
SET
Duración: 0,124 ms
postgres=# SELECT ‘2012-04-12’::date;
date
————
12/04/2012
(1 fila)
Duración: 0,154 ms

INSOLITO! ¿cómo es posible que una simple función SET cause que todas las fechas salgan con el formato que yo desee?, esto pasa cuando se desconocen los detalles de la base de datos con la que se trabaja.

Igual sucede cuando la gente comprende la diferencia entre usar ON y USING cuando se construyen JOINS (diferencia claro está, a nivel de planificador).

El tercer caso de no-entendimiento de postgreSQL ocurría con un “pseudo-ORM hecho a mano” por los mismos programadores, que no solamente escapaba mal las cadenas:

Esto:

(‘Empresa’, ‘   MATURIN’, ’15/04/2014′, …

No es igual a esto:

(‘Empresa’, ‘MATURIN’, ’15/04/2014′, …

Ya que la cadena ”    MATURIN” no es igual a “MATURIN”, a menos que se haga un TRIM adicional, afectando el performance de la consulta, sino que además, hacen cosas como esta:

’21’, ’32’, ’16’, ‘8’, …

¿Esos son cadenas o números?, luego de ejecutar un DESCRIBE a la tabla, encontramos con que son campos numéricos, y aunque postgreSQL hace la conversión, lanza un WARNING indicando que esos valores son “not integers”, lo cual obviamente no es para nada óptimo.

Esto es lo que pasa cuando los programadores, pensando como programadores, intentan diseñar bases de datos.

El extraño caso del CPU consumido

Me he encontrado con una consulta muy repetida, con esta forma:

SELECT * FROM sol_solvencia WHERE cod_aport = $1 and estatus = $2;

La consulta se repetía muchísimo (sobre todo en temporada de mucho uso de la aplicación web hecha en PHP) y los logs del postgreSQL reportaban que su duración, promedio, era de 550 milisegundos … grité (como gritó Emmet Brown cuando le dijeron que debía alimentar el D’lorean con 1.1Gigawatts) ¡550 milisegundos!, yo en 550 milisegundos corría una nómina y ellos sólo ejecutan un query!.

Luego de usar EXPLAIN ANALYZE nos encontramos con la sorpresa de que la tabla no posee índices de ningún tipo (alegan que es un sistema legado y viene así de informix) y en la actualidad posee unos 98 mil registros; por ende, postgreSQL no le queda de otra para hacer el WHERE que ejecutar un SEQUENTIAL SCAN; o lo que es lo mismo, postgreSQL iteró de manera secuencial por los 98 mil registros para sacar ámbos criterios, ¿en conclusión?, allí se van los 550 milisegundos.

Si el query se ejecuta a un ritmo de hasta 50 veces en un minuto, se imaginan el uso de CPU de postgreSQL para poder mantener ese ritmo.

Indizar, y sino, indizar también …

Luego de construir un par de índices sencillos:

CREATE INDEX idx_sol_solvencia_cod_aport ON sol_solvencia USING btree (cod_aport NULLS FIRST);

Y

CREATE INDEX idx_sol_solvencia_estatus ON sol_solvencia USING btree (cod_estatus);

El EXPLAIN cambia considerablemente:

Aggregate  (cost=32.19..32.20 rows=1 width=4)
  ->  Index Scan using idx_solvencia_fecha on sol_solvencia  (cost=0.00..31.96 rows=94 width=4)
        Index Cond: (fecha_now = ’11-04-2014′::date)
        Filter: ((estado = 1) OR (estado = 15))

Al realizar un escaneo sobre índices y luego una función de agregado sobre ámbos índices, la consulta se ejecuta en 1.7 milisegundos …

¡toda una mejora!, ¿no les parece?

Prepáralo, ponlo para llevar

Luego, les expliqué el concepto del planificador, del GeQo de postgreSQL, y de cómo se analizaban las consultas, luego de ello, les dije “¿y se imaginan que ese tiempo pudiera bajarse más?” … te miran con cara de gallina comiendo sal y tú les muestras PREPARE.

PREPARE genera sentencias preparadas, toma una consulta común, la pasa por el planificador, construye el plan en base a criterios pre-definidos y compila este plan, presto a simplemente recibir los parámetros, ¿y qué ganamos con esto?, ¡ahorrarnos milisegundos valiosos de planificación!.

PREPARE pln_obtenersolvencia (int, varchar) AS
SELECT * FROM sol_solvencia WHERE cod_aport = $1 and estatus = $2;

Como ven, la sentencia preparada es simplemente un QUERY tradicional, al cual hemos pasado sus condiciones (que deben ser criterios, no metadatos) como parámetros, así, postgreSQL puede analizar, planificar y compilar la sentencia y esperar únicamente a que pasemos los parámetros:

EXECUTE pln_obtenersolvencia(682185, 1);

Total runtime: 0.473 ms
(1 filas)

De 1.7 a 0.4 ha sido una mejora interesante, ¿no?.

Conclusiones

No busco “echarme de enemigos” a los desarrolladores, yo también soy uno (de diseñado APIs, frameworks, y módulos en python y PHP), pero, cuando se trata de bases de datos, los desarrolladores deben quitarse los audífonos, la sudadera geek y ponerse en la camisa y anteojos nerd de los DBA; porque al final del día, la aplicación no se está diseñando para yo sentirme cómodo para trabajar con fechas o agregar y agregar campos sin mantener un diccionario de datos simplemente porque me pidieron en un lugar meter el RIF como J310210423 en otro como J-31021042-3 y en otro el tipo de contribuyente por un campo y el código del mismo por otro; no se trata de nuestra comodidad (o de nuestro limitado conocimiento como desarrolladores) sino de la eficiencia y la funcionalidad que deben garantizar que sea el USUARIO FINAL (y sólo él, porque al final, es el que usará todos los días la aplicación) quien disfrute el uso de nuestra aplicación.

Por cierto, como colofón, habrán notado que la tabla se llama “solvencia”, si, es un sistema que genera solvencias al público en general, ahora las consultas duran menos de un milisegundo (y no casi un segundo en cargar) por lo que la mejora impacta a miles de personas que solicitan la solvencia en ese sistema …

… Y ya el CPU no se agota en la máquina virtual! …

 

A %d blogueros les gusta esto: