Ejemplos prácticos de trabajo con transacciones anidadas

Hoy nuestro objetivo es crear una función que:

1. Comprueba el balance del cliente. Antes de descontar una cantidad, hay que comprobar si hay suficiente saldo.
2. Descuenta fondos del balance. Si hay suficiente dinero en el balance, se realiza el descuento.
3. Registra operaciones exitosas y fallidas. Todas las acciones se guardan en una tabla de logs para su posterior análisis.

No es solo una función aburrida para restar. Aquí vamos a usar transacciones anidadas para hacer rollback de los cambios si algo sale mal (por ejemplo, fondos insuficientes o un error al registrar el log). Vamos a descubrir la utilidad de los puntos de guardado (SAVEPOINT) y aprenderemos a hacer procedimientos resistentes a errores.

Creando las tablas iniciales

Antes de ponernos con la función, vamos a preparar la base de datos. Necesitamos tres tablas:

1. clients — para guardar los datos de los clientes y su balance.
2. payments — para registrar las transacciones exitosas.
3. logs — para guardar información sobre todos los intentos de pago (exitosos y fallidos).

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

-- Tabla de pagos exitosos
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()
);

-- Tabla 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 a rellenar la tabla clients con datos de prueba

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

Ahora tenemos tres clientes: Otto tiene 100 en su cuenta, María — 50, y Anna — 0.

Implementación de la lógica de negocio: PROCEDURE vs FUNCTION

En resumen:

• Para operaciones de negocio "todo o nada" basta con una función.
• Para controlar transacciones por etapas, commits parciales, rollbacks, registro de errores — usa un procedimiento (CREATE PROCEDURE).

¿Por qué no una función? El tema es que en PostgreSQL 17 dentro de una función NO puedes usar ni COMMIT, ni SAVEPOINT, ni ROLLBACK. Todos los cambios se hacen de forma atómica dentro de la transacción externa.

Solo un procedimiento (CREATE PROCEDURE ... LANGUAGE plpgsql) te permite usar SAVEPOINT, COMMIT, ROLLBACK — pero con limitaciones importantes:

• Dentro de un procedimiento están permitidos SAVEPOINT, COMMIT, RELEASE SAVEPOINT.
• ROLLBACK TO SAVEPOINT está prohibido en PL/pgSQL (da error), en su lugar se usan bloques BEGIN ... EXCEPTION ... END, que hacen un "savepoint virtual".

La técnica principal para hacer rollback parcial:

BEGIN
    -- tu código
EXCEPTION
    WHEN OTHERS THEN
        -- Este bloque, si hay error, hace rollback de TODOS los cambios dentro del bloque!
        -- Puedes dejar info en el log:
        INSERT INTO logs (...) VALUES (...);
END;

Creando un procedimiento de pago con rollback parcial y logging

CREATE OR REPLACE PROCEDURE process_payment(
    in_client_id INT,
    in_payment_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- Obtenemos el balance del 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 no encontrado, operación rechazada');
        RAISE EXCEPTION 'Cliente con ID % no encontrado', in_client_id;
    END IF;

    -- Comprobamos si hay fondos suficientes
    IF current_balance < in_payment_amount THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Fondos insuficientes para descontar ' || in_payment_amount || ' eur.');
        -- Terminamos el procedimiento
        RETURN;
    END IF;

    -- Bloque para cambios atómicos; si hay error — rollback (savepoint virtual)
    BEGIN
        -- Descontamos del balance
        UPDATE clients
        SET balance = balance - in_payment_amount
        WHERE client_id = in_client_id;

        -- Añadimos registro de pago exitoso
        INSERT INTO payments (client_id, amount)
        VALUES (in_client_id, in_payment_amount);

        -- Logueamos el éxito
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Descuento exitoso de ' || in_payment_amount || ' eur.');

    EXCEPTION
        WHEN OTHERS THEN
            -- Todos los cambios dentro de este bloque se cancelan
            INSERT INTO logs (client_id, message)
            VALUES (in_client_id, 'Error en el pago: ' || SQLERRM);
            -- (no hace falta ROLLBACK TO SAVEPOINT explícito — está prohibido y no es necesario)
    END;
END;
$$;

En resumen, qué pasa:

• Si no hay fondos o el cliente no existe — lo registramos en el log y salimos.
• Todo el código crítico está dentro de un bloque BEGIN ... EXCEPTION ... END.
• Si hay cualquier error dentro de ese bloque — todos los cambios se revierten automáticamente; escribimos el error en los logs.
• No se usa directamente SAVEPOINT ni ROLLBACK TO SAVEPOINT — así debe ser, en PL/pgSQL solo funciona con bloques EXCEPTION.

Llamando al procedimiento

Importante: tienes que llamar al procedimiento con el comando CALL ..., y la conexión a la base debe estar en modo autocommit o fuera de una transacción grande explícita!

CALL process_payment(1, 30.00);   -- Pago exitoso
CALL process_payment(2, 100.00);  -- Fondos insuficientes
CALL process_payment(999, 50.00); -- Cliente no existe

Comprobando los resultados

• El balance del cliente cambia solo si el pago fue exitoso.
• La tabla payments — solo hay registro si el descuento fue exitoso.
• logs — historial de todos los intentos (y errores).

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

Aplicación real

Los procedimientos para manejar transacciones son una de las partes centrales en sistemas de fintech, e-commerce e incluso plataformas de juegos. Imagina una tienda online que tiene que tener en cuenta el saldo de los vales regalo y descontarlos al hacer compras, o un sistema bancario con miles de operaciones por segundo.

Estos conocimientos te van a servir en la práctica, te ayudarán a proteger los datos de tus clientes y evitar errores catastróficos en el procesamiento de pagos.

2

Tarea

SQL SELF, nivel 54, lección 2

Bloqueada

Procesamiento de pago para un cliente

Procesamiento de pago para un cliente

Abrir