Exemplos práticos de uso de transações aninhadas

Hoje a missão é criar uma função que:

1. Verifica o saldo do cliente. Antes de debitar qualquer valor, tem que checar se tem grana suficiente.
2. Debita o saldo. Se o saldo for suficiente, faz o débito.
3. Loga operações bem-sucedidas e falhas. Tudo que rolar vai pra tabela de logs pra gente analisar depois.

Não é só uma função chata de subtrair valor. Aqui a gente vai usar transações aninhadas pra desfazer mudanças se der ruim (tipo saldo insuficiente ou erro ao gravar o log). Vamos sacar a utilidade dos pontos de salvamento (SAVEPOINT) e aprender a deixar os procedimentos à prova de erro.

Criando as tabelas iniciais

Antes de criar a função, bora preparar o banco. Vamos precisar de três tabelas:

1. clients — pra guardar os dados dos clientes e seus saldos.
2. payments — pra registrar as transações que deram certo.
3. logs — pra guardar info de todas as tentativas de pagamento (tanto as que deram certo quanto as que falharam).

-- Tabela de clientes
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL DEFAULT 0
);

-- Tabela de pagamentos bem-sucedidos
CREATE TABLE payments (
    payment_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    amount NUMERIC(10, 2) NOT NULL,
    payment_date TIMESTAMP DEFAULT NOW()
);

-- Tabela de logs
CREATE TABLE logs (
    log_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    message TEXT NOT NULL,
    log_date TIMESTAMP DEFAULT NOW()
);

Vamos preencher a tabela clients com dados de teste

INSERT INTO clients (full_name, balance)
VALUES 
    ('Otto Song', 100.00),
    ('Maria Chi', 50.00),
    ('Anna Vel', 0.00);

Agora temos três clientes: o Otto tem 100 na conta, a Maria tem 50 e a Anna tem 0.

Implementando a lógica de negócio: PROCEDURE vs FUNCTION

Resumindo:

• Pra operações de negócio "tudo ou nada", uma função já resolve.
• Pra controlar transações em etapas, commits parciais, rollbacks, log de erro — usa procedure (CREATE PROCEDURE).

Por que não usar função? Porque no PostgreSQL 17, dentro de uma função você NÃO pode usar COMMIT, SAVEPOINT nem ROLLBACK. Tudo rola de forma atômica dentro da transação externa.

Só procedure (CREATE PROCEDURE ... LANGUAGE plpgsql) deixa usar SAVEPOINT, COMMIT, ROLLBACK — mas tem umas limitações importantes:

• Dentro da procedure, pode usar SAVEPOINT, COMMIT, RELEASE SAVEPOINT.
• ROLLBACK TO SAVEPOINT é proibido em PL/pgSQL (vai dar erro), então a galera usa blocos BEGIN ... EXCEPTION ... END, que fazem um "savepoint virtual".

A técnica principal pra desfazer parte do código:

BEGIN
    -- seu código aqui
EXCEPTION
    WHEN OTHERS THEN
        -- Esse bloco, se der erro, desfaz TUDO que rolou dentro dele!
        -- Pode deixar info no log:
        INSERT INTO logs (...) VALUES (...);
END;

Criando uma procedure de pagamento com rollback parcial e log

CREATE OR REPLACE PROCEDURE process_payment(
    in_client_id INT,
    in_payment_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- Pega o saldo do cliente
    SELECT balance INTO current_balance
    FROM clients
    WHERE client_id = in_client_id;

    IF NOT FOUND THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Cliente não encontrado, operação recusada');
        RAISE EXCEPTION 'Cliente com ID % não encontrado', in_client_id;
    END IF;

    -- Checa se tem saldo suficiente
    IF current_balance < in_payment_amount THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Saldo insuficiente pra debitar ' || in_payment_amount || ' reais.');
        -- Sai da procedure
        RETURN;
    END IF;

    -- Bloco pra mudanças atômicas; se der erro — rollback (savepoint virtual)
    BEGIN
        -- Debita o saldo
        UPDATE clients
        SET balance = balance - in_payment_amount
        WHERE client_id = in_client_id;

        -- Adiciona registro de pagamento bem-sucedido
        INSERT INTO payments (client_id, amount)
        VALUES (in_client_id, in_payment_amount);

        -- Loga sucesso
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Débito bem-sucedido de ' || in_payment_amount || ' reais.');

    EXCEPTION
        WHEN OTHERS THEN
            -- Tudo que rolou nesse bloco é desfeito
            INSERT INTO logs (client_id, message)
            VALUES (in_client_id, 'Erro no pagamento: ' || SQLERRM);
            -- (não precisa de ROLLBACK TO SAVEPOINT explícito — é proibido e nem precisa)
    END;
END;
$$;

Resumindo o que rola:

• Se não tem saldo suficiente/não existe cliente — loga e sai.
• Todo o código crítico tá dentro do bloco BEGIN ... EXCEPTION ... END.
• Se der qualquer erro nesse bloco — tudo é desfeito automaticamente; a gente loga o erro.
• Não tem uso direto de SAVEPOINT nem ROLLBACK TO SAVEPOINT — é assim mesmo, em PL/pgSQL só funciona via blocos EXCEPTION.

Chamando a procedure

Importante: pra chamar a procedure, usa o comando CALL ..., e a conexão com o banco tem que estar em modo autocommit ou fora de uma transação grandona!

CALL process_payment(1, 30.00);   -- Pagamento bem-sucedido
CALL process_payment(2, 100.00);  -- Saldo insuficiente
CALL process_payment(999, 50.00); -- Cliente não existe

Conferindo os resultados

• Mudança no saldo do cliente — só se o pagamento rolou.
• Tabela payments — só tem registro se o débito foi feito.
• logs — histórico de todas as tentativas (e erros).

SELECT * FROM clients;
SELECT * FROM payments;
SELECT * FROM logs;

Aplicação na vida real

Procedures pra tratar transações são parte central de sistemas de fintech, e-commerce e até plataformas de games. Imagina uma loja online que tem que controlar saldo de vale-presente e debitar quando alguém compra, ou um banco com milhares de operações por segundo.

Esse conhecimento vai te ajudar na prática, proteger os dados dos seus clientes e evitar erros catastróficos no processamento de pagamentos.