Depuração completa e otimização de procedimento multi-etapas

Procedimentos multi-etapas são tipo o "canivete suíço" dos bancos de dados. Geralmente eles têm validação dos dados de entrada, fazem alterações (tipo atualizar registros, inserir logs) e às vezes até fazem análise. Mas aí que tá: quanto mais complexo o procedimento, maior a chance de dar ruim. Um erro lógico, uma query lenta, um detalhe esquecido — e já era, tudo pode sair do controle.

Depuração completa envolve os seguintes pontos:

1. Análise dos dados de entrada: os parâmetros estão certos? Os dados que chegaram são válidos?
2. Checagem da execução das etapas principais: todos os passos do procedimento estão rodando direitinho?
3. Logging dos resultados intermediários: pra saber o que rolou antes de algo "quebrar".
4. Otimização dos gargalos de performance: bora melhorar os pontos fracos que estão "travando" as queries.

Definindo o problema: exemplo de procedimento multi-etapas

Pra nosso exemplo prático, imagina que a gente tá mexendo no banco de dados de uma loja online. Precisamos criar um procedimento pra processar um pedido. Ele vai fazer o seguinte:

1. Checar se o produto tá disponível no estoque.
2. Reservar o produto.
3. Atualizar o status do pedido.
4. Registrar eventos (tipo reserva feita ou erro) na tabela de logs.

Script da estrutura do banco de dados:

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

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

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

Passo 1: Criando o procedimento multi-etapas

Bora criar um procedimento básico chamado process_order. Ele vai receber o id do pedido e executar todas as etapas do processamento.

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. Pegando o id do produto e status do 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 % não existe ou está sem product_id', p_order_id;
    END IF;

    -- 2. Checando se tem produto no estoque
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Produto % está fora de estoque', v_product_id;
    END IF;

    -- 3. Atualizando quantidade no estoque
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Atualizando status do pedido
    UPDATE orders
    SET order_status = 'Processado'
    WHERE order_id = p_order_id;

    -- 5. Registrando evento de sucesso no log
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Pedido processado com sucesso.');
END;
$$;

Passo 2: Logging de erros com RAISE NOTICE e RAISE EXCEPTION

Aqui começa a mágica. Vamos adicionar logging dos passos intermediários pra pegar erros e entender o que tá rolando em cada etapa.

Código atualizado com 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 'Processando pedido %...', p_order_id;

    -- 1. Pegando o id do produto
    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 % não existe ou está sem product_id', p_order_id;
    END IF;
    RAISE NOTICE 'Product ID do pedido %: %', p_order_id, v_product_id;

    -- 2. Checando se tem produto no estoque
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Produto % está fora de estoque', v_product_id;
    END IF;
    RAISE NOTICE 'Quantidade em estoque do produto %: %', v_product_id, v_stock_quantity;

    -- 3. Atualizando quantidade no estoque
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Atualizando status do pedido
    UPDATE orders
    SET order_status = 'Processado'
    WHERE order_id = p_order_id;

    -- 5. Logging de sucesso
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Pedido processado com sucesso.');
    RAISE NOTICE 'Pedido % processado com sucesso.', p_order_id;

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

Passo 3: Otimizando com índices

Se o banco tiver muitos produtos ou pedidos, buscar as linhas certas pode virar um gargalo. Vamos criar índices pra acelerar as buscas durante o processamento:

-- Índice pra acelerar busca na tabela orders
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- Índice pra acelerar busca na tabela products
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);

Passo 4: Analisando performance com EXPLAIN ANALYZE

Agora vamos ver o quão rápido nossa função tá rodando. Pra isso, chama ela com análise de performance:

EXPLAIN ANALYZE
SELECT process_order(1);

O resultado mostra quanto tempo cada etapa levou. Assim dá pra sacar qual parte tá mais lenta — e aí a gente pode otimizar ainda mais o procedimento.

Passo 5: Melhorando com transações

Pra deixar tudo mais confiável, dá pra colocar o procedimento inteiro dentro de uma transação. Assim, se der ruim em algum passo, tudo volta ao que era antes.

BEGIN;

-- Chamando a função
SELECT process_order(1);

-- Commit da transação
COMMIT;

No próprio código da função, dá pra usar SAVEPOINT e ROLLBACK TO SAVEPOINT pra tratar erros parciais.

Desafio prático: processamento em massa de pedidos

Bora fechar a aula com um exemplo de como processar vários pedidos de uma vez. Vamos criar uma função que processa todos os pedidos com status Pendente:

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 = 'Pendente'
    LOOP
        BEGIN
            PERFORM process_order(v_order_id);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Falha ao processar pedido %: %', v_order_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

Quando você chamar essa função, todos os pedidos com status Pendente vão ser processados, e qualquer erro vai só ser registrado no log.

Então é isso! Mostramos como depurar e otimizar procedimentos complexos, deixando eles mais confiáveis, rápidos e fáceis de entender. Esse conhecimento vai ser útil nos projetos reais, onde a qualidade dos procedimentos faz toda a diferença pro sucesso da aplicação.