Depuración y optimización integral de un procedimiento de varios pasos

Los procedimientos de varios pasos son como las "navajas suizas" de las bases de datos. Normalmente incluyen validación de datos de entrada, cambios (por ejemplo, actualizar registros, insertar logs) y a veces hasta analítica. Pero aquí está el rollo: cuanto más complejo es el procedimiento, más fácil es meter la pata. Un fallo lógico, una consulta lenta, un detalle que se te escapa — y todo se va al garete.

La depuración integral incluye estos puntos:

1. Análisis de los datos de entrada: ¿los parámetros están bien? ¿Los datos que llegan son correctos?
2. Comprobación de las etapas clave: ¿todos los pasos del procedimiento se ejecutan bien?
3. Logging de resultados intermedios: para saber qué ha pasado antes de que algo "petara".
4. Optimización de cuellos de botella de rendimiento: mejoramos los puntos débiles que "ralentizan" las consultas.

Planteamiento: ejemplo de procedimiento de varios pasos

Para nuestro ejemplo práctico, imagina que curramos con la base de datos de una tienda online. Tenemos que crear un procedimiento para procesar un pedido. Va a hacer estos pasos:

1. Comprobar si hay stock del producto.
2. Reservar el producto.
3. Actualizar el estado del pedido.
4. Guardar eventos (por ejemplo, reserva exitosa o error) en la tabla de logs.

Script de la estructura de la base de datos:

-- Tabla de productos
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    stock_quantity INTEGER NOT NULL
);

-- Tabla de pedidos
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    order_status TEXT NOT NULL
);

-- Tabla de logs
CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT NOW()
);

Paso 1: Crear el procedimiento de varios pasos

Vamos a crear el procedimiento básico process_order. Va a recibir el identificador del pedido y ejecutar todos los pasos del proceso.

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    -- 1. Pillamos el id del producto y el estado del pedido
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Pedido % no existe o falta product_id', p_order_id;
    END IF;

    -- 2. Comprobamos si hay stock
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Producto % sin stock', v_product_id;
    END IF;

    -- 3. Actualizamos el stock
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Actualizamos el estado del pedido
    UPDATE orders
    SET order_status = 'Procesado'
    WHERE order_id = p_order_id;

    -- 5. Guardamos evento exitoso en el log
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Pedido procesado correctamente.');
END;
$$;

Paso 2: Logging de errores con RAISE NOTICE y RAISE EXCEPTION

Aquí empieza la magia. Vamos a meter logging de los pasos intermedios para pillar errores y entender qué pasa en cada etapa.

Código actualizado con logging:

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    RAISE NOTICE 'Procesando pedido %...', p_order_id;

    -- 1. Pillamos el id del producto
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Pedido % no existe o falta product_id', p_order_id;
    END IF;
    RAISE NOTICE 'Product ID para pedido %: %', p_order_id, v_product_id;

    -- 2. Comprobamos si hay stock
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Producto % sin stock', v_product_id;
    END IF;
    RAISE NOTICE 'Stock para producto %: %', v_product_id, v_stock_quantity;

    -- 3. Actualizamos el stock
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Actualizamos el estado del pedido
    UPDATE orders
    SET order_status = 'Procesado'
    WHERE order_id = p_order_id;

    -- 5. Logging de éxito
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Pedido procesado correctamente.');
    RAISE NOTICE 'Pedido % procesado correctamente.', p_order_id;

EXCEPTION WHEN OTHERS THEN
    -- Logging de error
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Error: ' || SQLERRM);
    RAISE;
END;
$$;

Paso 3: Optimización usando índices

Si tienes un montón de productos o pedidos en la base, buscar las filas correctas puede ser un cuello de botella. Añadimos índices para acelerar las búsquedas durante el proceso:

-- Índice para acelerar búsquedas en la tabla orders
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- Índice para acelerar búsquedas en la tabla products
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);

Paso 4: Análisis de rendimiento con EXPLAIN ANALYZE

Ahora vamos a ver qué tan rápido va nuestra función. Para eso la llamamos con análisis de rendimiento:

EXPLAIN ANALYZE
SELECT process_order(1);

El resultado te muestra cuánto tarda cada paso. Así puedes ver cuál es el más lento — y eso te ayuda a optimizar el procedimiento aún más.

Paso 5: Mejorando con transacciones

Para que todo sea más fiable, puedes meter todo el procedimiento en una transacción. Así, si algo falla, se deshacen todos los cambios.

BEGIN;

-- Llamada a la función
SELECT process_order(1);

-- Commit de la transacción
COMMIT;

En el propio código de la función puedes usar SAVEPOINT y ROLLBACK TO SAVEPOINT para manejar errores parciales.

Ejercicio práctico: procesar pedidos masivos

Vamos a terminar la lección con un ejemplo de cómo procesar varios pedidos de golpe. Creamos una función que procesa todos los pedidos con estado Pendiente:

CREATE OR REPLACE FUNCTION process_all_orders()
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    FOR v_order_id IN
        SELECT order_id
        FROM orders
        WHERE order_status = 'Pendiente'
    LOOP
        BEGIN
            PERFORM process_order(v_order_id);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'No se pudo procesar el pedido %: %', v_order_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

Al llamar a esta función, todos los pedidos con estado Pendiente se procesarán, y cualquier error solo se dejará en el log.

Así hemos visto cómo depurar y optimizar procedimientos complejos, mejorando su fiabilidad, rendimiento y legibilidad. Estos trucos te van a venir genial en proyectos reales, donde la calidad de los procedimientos puede marcar el éxito de tu app.

2

Tarea

SQL SELF, nivel 56, lección 3

Bloqueada

Registro de pasos intermedios

Registro de pasos intermedios

Abrir