Analyzing Common Mistakes When Working with Nested Transactions

Programming in Postgres is quite an adventure: sometimes it turns into a quest called “Find Your Bug.” In this section, we’ll talk about the typical mistakes and pitfalls you might hit when working with nested transactions. Let’s go!

Incorrect Use of Transactional Commands Inside Functions and Procedures

Mistake: trying to use COMMIT, ROLLBACK, or SAVEPOINT inside a FUNCTION.

Why: In PostgreSQL, functions (CREATE FUNCTION ... LANGUAGE plpgsql) always run within a single outer transaction, and any transactional commands inside a function are forbidden. Trying to use them will throw a syntax error.

Example of the mistake:

CREATE OR REPLACE FUNCTION f_bad() RETURNS void AS $$
BEGIN
    SAVEPOINT sp1;  -- Error: transactional commands are not allowed
END;
$$ LANGUAGE plpgsql;

How to do it right:

For atomic operations that should be “all or nothing,” use functions without explicit transactional commands. If you need step-by-step commits — use procedures.

Mistake: trying to use ROLLBACK TO SAVEPOINT in a PL/pgSQL procedure.

Why: In PostgreSQL 17, only COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT are allowed inside procedures (CREATE PROCEDURE ... LANGUAGE plpgsql). But ROLLBACK TO SAVEPOINT in PL/pgSQL is not allowed! Any such attempt will end with a syntax error.

Example of the mistake:

CREATE PROCEDURE p_bad()
LANGUAGE plpgsql
AS $$
BEGIN
    SAVEPOINT sp1;
    -- ...
    ROLLBACK TO SAVEPOINT sp1; -- Error! Not allowed
END;
$$;

How to do it right:

For “partial rollback,” use BEGIN ... EXCEPTION ... END blocks — they automatically create a savepoint; if there’s an error inside the block, all changes roll back to its start.

CREATE PROCEDURE p_good()
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- operations that might throw an error
        ...
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Rollback inside BEGIN ... EXCEPTION ... END block';
    END;
END;
$$;

Nested Procedure Calls: Limitations and Typical Mistakes

Mistake: calling a procedure with explicit COMMIT/ROLLBACK inside an already open client transaction.

Why: Procedures with transactional control only work correctly in autocommit mode (one procedure — one transaction), otherwise if you try to use COMMIT or ROLLBACK inside the procedure, you’ll get an error: the transaction is already open at the client level.

Example:

# In Python with psycopg2, autocommit=False by default
cur.execute("BEGIN;")
cur.execute("CALL my_proc();")   -- Error when trying COMMIT inside my_proc

How to do it right:

• Switch the connection to autocommit mode before calling procedures.
• Don’t call procedures via functions or SELECT.

Mistake: calling procedures with transactional control (COMMIT, ROLLBACK) doesn’t work if they’re NOT called with the CALL command (for example, via SELECT).

Why: Only calling via CALL (or in an anonymous DO block) lets you manage transactions. Calling from a function — not allowed.

Locking and Deadlock Issues

Locks are like uninvited guests: first they get in the way, then they cause chaos. Deadlock happens when transactions wait for each other forever. Here’s a typical example:

1. Transaction A locks a row in the orders table and tries to update a row in the products table.
2. Transaction B locks a row in the products table and tries to update a row in the orders table.

In the end, neither transaction can move forward. It’s like two cars trying to squeeze into a tight turn at the same time, and the result is a traffic jam.

Example:

-- Transaction A
BEGIN;
UPDATE orders SET status = 'Processing' WHERE id = 1;

-- Transaction B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;

-- Now transaction A tries to update the same row in `products`,
-- and transaction B tries to change a row in `orders`.
-- Deadlock!

How to avoid it?

1. Always update data in the same order. For example, orders first, then products.
2. Avoid transactions that take too long.
3. Use LOCK wisely, specifying the minimal lock level you need.

Incorrect Use of Dynamic SQL (EXECUTE)

Dynamic SQL, if you’re not careful, can be a real headache. The most common mistake is SQL injection. For example:

EXECUTE 'SELECT * FROM orders WHERE id = ' || user_input;

If user_input contains something like 1; DROP TABLE orders;, you can say goodbye to your orders table.

How to avoid it? Use prepared statements:

EXECUTE 'SELECT * FROM orders WHERE id = $1' USING user_input;

This approach will protect your app from SQL injections.

Rolling Back a Transaction After Bad Error Handling

If errors aren’t handled properly, a transaction can get stuck in an invalid state. For example:

BEGIN;

INSERT INTO orders (order_id, status) VALUES (1, 'Pending');

BEGIN;
-- Some operation that throws an error
INSERT INTO non_existing_table VALUES (1);
-- Error, but the transaction wasn’t finished

COMMIT; -- Error: current transaction is aborted

Because of the error, all the code gets stuck.

How to avoid it? Use EXCEPTION blocks for proper rollback:

BEGIN
    INSERT INTO orders (order_id, status) VALUES (1, 'Pending');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'An error happened, transaction will be rolled back.';
END;

How to Avoid Mistakes: Tips and Recommendations

• When writing a complex procedure, always start with pseudocode. Write out all the steps and possible error points.
• Use SAVEPOINT for isolated transaction rollbacks. But don’t forget to release them after use.
• Try to avoid long transactions — the longer the transaction, the higher the chance of locks.
• For nested procedure calls, make sure the outer and inner transaction contexts are properly synced.
• Always check the performance of your procedures with EXPLAIN ANALYZE.
• Log errors to tables or text files — it’ll make debugging way easier.

Examples of Mistakes and How to Fix Them

Example 1: Error When Calling a Nested Procedure

Buggy code:

BEGIN;

CALL process_order(5);

-- Inside process_order there was a ROLLBACK
-- The whole transaction becomes invalid
COMMIT; -- Error

Fixed code:

BEGIN;

SAVEPOINT sp_outer;

CALL process_order(5);

-- Rollback only if there’s an error
ROLLBACK TO SAVEPOINT sp_outer;

COMMIT;

Example 2: Deadlock Problem

Buggy code:

-- Transaction A
BEGIN;
UPDATE orders SET status = 'Processing' WHERE id = 1;
-- Waiting for `products`

-- Transaction B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
-- Waiting for `orders`

Fix:

-- Both queries run in the same order:
-- First `products`, then `orders`.
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = 'Processing' WHERE id = 1;
COMMIT;

These mistakes show why working with transactions takes attention and experience. But as they say, the more you practice, the less likely you’ll get a ROLLBACK in real life (and your career).