Análisis de errores típicos al depurar y optimizar PL/pgSQL

Hoy, para cerrar este épico viaje por PL/pgSQL, vamos a analizar los errores más comunes que te pueden pillar mientras depuras y optimizas funciones y procedimientos. Saber sobre estos errores te va a ayudar no solo a evitarlos en el futuro, sino también a entender mejor los bugs si aparecen.

Errores típicos al depurar y optimizar

1. Uso incorrecto de variables

Uno de los errores más frecuentes al escribir y depurar funciones en PL/pgSQL es declarar o usar mal las variables. Por ejemplo, si te olvidas de especificar el tipo de variable o te lías con los valores que llegan por parámetros. Vamos a ver cómo puede ser esto en la práctica:

CREATE OR REPLACE FUNCTION calculate_discount(order_total NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    discount_rate NUMERIC;
BEGIN
    -- ¡Ups! Olvidé inicializar la variable discount_rate
    RETURN order_total * discount_rate;
END;
$$ LANGUAGE plpgsql;

Cuando llames a esta función vas a recibir un error relacionado con el uso de NULL en los cálculos, porque la variable discount_rate no está inicializada al principio.

Cómo evitarlo:

1. Siempre asigna valores por defecto a las variables cuando las declares:

   DECLARE
       discount_rate NUMERIC := 0.1; -- Valor por defecto

2. Comprueba el uso de variables con RAISE NOTICE para asegurarte de que tienen los valores esperados:

RAISE NOTICE 'Valor de discount_rate: %', discount_rate;

2. Falta de logging de errores

Otro problema muy común es no tener un mecanismo de logging. Si algo va mal y no registras el flujo de tu función, es como buscar un gato negro en una habitación oscura, sobre todo si ni siquiera sabes si hay un gato ahí.

Aquí tienes un ejemplo de función sin logging:

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    -- Alguna lógica complicada de procesamiento de pedido
    UPDATE orders SET status = 'processed' WHERE id = order_id;
END;
$$ LANGUAGE plpgsql;

¿Y si order_id es incorrecto? ¿Y si el registro en la tabla orders no existe?

Cómo evitarlo: Añade RAISE NOTICE o RAISE EXCEPTION para registrar los pasos críticos:

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    -- Logueamos los datos de entrada
    RAISE NOTICE 'Procesando pedido con ID %', order_id;

    -- Lógica complicada de procesamiento
    UPDATE orders SET status = 'processed' WHERE id = order_id;

    -- Logueamos el resultado
    RAISE NOTICE 'Estado del pedido actualizado para ID %', order_id;
END;
$$ LANGUAGE plpgsql;

Ahora vas a poder rastrear fácilmente dónde ocurre el error gracias a los mensajes que se muestran.

3. Ignorar el rendimiento de las consultas

Este es uno de los mayores enemigos de cualquier desarrollador de bases de datos. Por ejemplo, escribes una función que parece correcta, pero va lentísima. Y una de las principales razones de consultas lentas es la falta de índices o planes de ejecución ineficientes.

Ejemplo de consulta lenta:

CREATE OR REPLACE FUNCTION get_large_orders()
RETURNS TABLE(order_id INT, total NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT id, total FROM orders WHERE total > 1000;
END;
$$ LANGUAGE plpgsql;

Si el campo total en la tabla orders no está indexado, la consulta va a escanear toda la tabla, lo cual es súper ineficiente.

Cómo evitarlo:

1. Usa EXPLAIN ANALYZE para asegurarte de que tus consultas son eficientes:

EXPLAIN ANALYZE SELECT id, total FROM orders WHERE total > 1000;

2. Crea índices en las columnas que usas mucho:

CREATE INDEX idx_orders_total ON orders(total);

4. Usar un nivel de aislamiento de transacciones incorrecto

Al ejecutar procedimientos complejos a veces aparecen errores por no entender bien los niveles de aislamiento de transacciones. Por ejemplo, si dos transacciones intentan actualizar el mismo registro al mismo tiempo, puede haber un deadlock.

Ejemplo de posible deadlock:

BEGIN;
UPDATE orders SET status = 'processed' WHERE id = 1;

-- Esperando el bloqueo de otra transacción
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
COMMIT;

Si otra transacción intenta hacer estas operaciones en otro orden, tendrás un bloqueo mutuo.

Cómo evitarlo:

1. Piénsate bien el orden de las operaciones y síguelo siempre.
2. Usa el nivel de aislamiento SERIALIZABLE si hace falta.

5. Falta de manejo de errores

Manejar errores no solo es buena práctica, sino que también es clave para la estabilidad de tu código. Por ejemplo, en el siguiente código no hay manejo de posibles errores:

CREATE OR REPLACE FUNCTION add_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders (id, status) VALUES (order_id, 'new');
END;
$$ LANGUAGE plpgsql;

Si de repente order_id ya existe, vas a recibir el error duplicate key value violates unique constraint.

Cómo evitarlo: Usa bloques de manejo de excepciones:

CREATE OR REPLACE FUNCTION add_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders (id, status) VALUES (order_id, 'new');
EXCEPTION WHEN unique_violation THEN
    RAISE NOTICE '¡El pedido con ID % ya existe!', order_id;
END;
$$ LANGUAGE plpgsql;

Ejemplos de errores y cómo corregirlos

Error 1: Las consultas van lentas por falta de índices

Situación: Tienes una consulta que filtra una tabla por una columna, pero esa columna no tiene índice.

Solución: Crea un índice para esa columna.

Error 2: La lógica de la función es confusa y difícil de depurar

Situación: La función tiene demasiada lógica y no está dividida en subfunciones.

Solución: Divide la función compleja en subfunciones más pequeñas. Así será más legible y fácil de depurar.

Error 3: Uso incorrecto de RAISE EXCEPTION

Situación: RAISE EXCEPTION se usa para todos los errores, incluso los que no son graves.

Solución: Usa RAISE NOTICE para mensajes informativos y solo RAISE EXCEPTION para casos críticos.

RAISE NOTICE 'Todo bajo control — la etapa actual de la función ha terminado.';
RAISE EXCEPTION '¡Algo se rompió! Revisa los parámetros de entrada.';

Recomendaciones para evitar errores

1. Añade logging: en los pasos críticos de tu función usa RAISE NOTICE para seguir la ejecución.
2. Testea las funciones: usa datos de prueba regularmente para comprobar funciones y procedimientos.
3. Mantén el código legible: divide funciones complejas en subfunciones y procedimientos más pequeños.
4. Analiza el rendimiento: usa EXPLAIN ANALYZE para asegurarte de que tus consultas van bien.
5. Prepárate para sorpresas: siempre añade bloques de manejo de excepciones para tratar errores.

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Ocurrió un error inesperado: %', SQLERRM;

Esto te va a permitir entender y solucionar errores con confianza, y además evitar que aparezcan en el futuro.