Hands-on Examples: Working with Nested Transactions

Today our goal is to build a function that:

1. Checks the client's balance. Before deducting any amount, we need to make sure there are enough funds.
2. Deducts funds from the balance. If the balance is enough, we do the deduction.
3. Logs both successful and failed operations. Every action gets written to a logs table for later analysis.

This isn't just some boring subtract function. Here, we're gonna use nested transactions so we can roll back changes if something goes wrong (like not enough funds or an error when writing to the log). We'll see why SAVEPOINTs are cool and learn how to make our procedures bulletproof against errors.

Setting Up the Initial Tables

Before we jump into writing the function, let's prep the database. We'll need three tables:

1. clients — to store client data and their balances.
2. payments — to record successful transactions.
3. logs — to keep info about all payment attempts (both successful and failed).

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

-- Successful payments table
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()
);

-- Logs table
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()
);

Let's fill the clients table with some test data

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

Now we've got three clients: Otto has 100 in his account, Maria has 50, and Anna has 0.

Business Logic: PROCEDURE vs FUNCTION

In short:

• For "all or nothing" business ops, a function is enough.
• For step-by-step transaction control, partial commits, rollbacks, error logging — use a procedure (CREATE PROCEDURE).

Why not a function? Well, in PostgreSQL 17, you CAN'T use COMMIT, SAVEPOINT, or ROLLBACK inside a function. All changes are atomic within the outer transaction.

Only a procedure (CREATE PROCEDURE ... LANGUAGE plpgsql) lets you use SAVEPOINT, COMMIT, ROLLBACK — but with some important gotchas:

• Inside a procedure, SAVEPOINT, COMMIT, RELEASE SAVEPOINT are allowed.
• ROLLBACK TO SAVEPOINT is forbidden in PL/pgSQL (you'll get an error), so instead you use BEGIN ... EXCEPTION ... END blocks, which act like a "virtual savepoint".

The main trick for rolling back part of your code:

BEGIN
    -- your code
EXCEPTION
    WHEN OTHERS THEN
        -- This block will roll back ALL changes inside it if there's an error!
        -- You can leave info in the log:
        INSERT INTO logs (...) VALUES (...);
END;

Building a Payment Procedure with Partial Rollback and Logging

CREATE OR REPLACE PROCEDURE process_payment(
    in_client_id INT,
    in_payment_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- Get the client's balance
    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, 'Client not found, operation rejected');
        RAISE EXCEPTION 'Client with ID % not found', in_client_id;
    END IF;

    -- Check if there's enough money
    IF current_balance < in_payment_amount THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Not enough funds to deduct ' || in_payment_amount || ' bucks.');
        -- End the procedure
        RETURN;
    END IF;

    -- Block for atomic changes; on error — rollback (virtual savepoint)
    BEGIN
        -- Deduct from balance
        UPDATE clients
        SET balance = balance - in_payment_amount
        WHERE client_id = in_client_id;

        -- Add a record about the successful payment
        INSERT INTO payments (client_id, amount)
        VALUES (in_client_id, in_payment_amount);

        -- Log the success
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Successfully deducted ' || in_payment_amount || ' bucks.');

    EXCEPTION
        WHEN OTHERS THEN
            -- All changes inside this block are rolled back
            INSERT INTO logs (client_id, message)
            VALUES (in_client_id, 'Error during payment: ' || SQLERRM);
            -- (no need for explicit ROLLBACK TO SAVEPOINT — it's forbidden and not needed)
    END;
END;
$$;

Quick summary of what's going on:

• If there's not enough money or the client doesn't exist — we log it and exit.
• All the critical code is inside a BEGIN ... EXCEPTION ... END block.
• If anything fails in that block — all changes in it are rolled back automatically; we log the error.
• No direct use of SAVEPOINT or ROLLBACK TO SAVEPOINT — that's how it's supposed to be, in PL/pgSQL you only use EXCEPTION blocks for this.

Calling the Procedure

Heads up: you gotta call the procedure with CALL ..., and your DB connection should be in autocommit mode or not inside a big manual transaction!

CALL process_payment(1, 30.00);   -- Successful payment
CALL process_payment(2, 100.00);  -- Not enough funds
CALL process_payment(999, 50.00); -- No such client

Checking the Results

• Client's balance changes — only if the payment went through.
• The payments table — only gets a record for successful deductions.
• logs — keeps the history of all attempts (and errors).

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

Real-World Use

Procedures for handling transactions are one of the core parts of systems in fintech, e-commerce, and even gaming platforms. Picture an online store that needs to keep track of gift card balances and deduct them when people buy stuff, or a banking system with thousands of ops per second.

This stuff will totally come in handy in real life, help you keep your clients' data safe, and avoid catastrophic screw-ups when processing payments.

2

Task

SQL SELF, level 54, lesson 2

Locked

Processing a payment for a client

Processing a payment for a client

Open