Postgres in depth · Chapter 37 of 65

The purchase

By the end of this chapter you can group changes into a transaction that is saved completely or not at all, undo part of one with a savepoint, and find the transactions somebody forgot to finish. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Safety, security and storage
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

One purchase on LCO is four rows: an order, its order item, a payment and an enrollment. Last week the payment gateway timed out after the payment row was written. Sana now has a student who paid and cannot open the course, and Arjun's revenue numbers count money for a course nobody got.

So far every statement you ran was saved the moment it finished. Four statements are four separate saves, and anything can happen between them: an error, a crash, a closed laptop. LCO needs the four rows to arrive together or not at all.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    purchase.sql: Diya buys Complete JavaScript

    Diya Patel buys Complete JavaScript for 1999.00 at 2026-09-20 10:00:00+05:30. Record the whole purchase in one transaction, so it is saved completely or not at all.

    Add four rows: an order (Diya's, status paid, total 1999.00, created_at and paid_at both that moment, no coupon); its order item (Complete JavaScript, price 1999.00); its payment (gateway_ref pay_lco37diya, amount 1999.00, status captured, created_at that moment); and her enrollment (Complete JavaScript, enrolled_at that moment, price_paid 1999.00).

    Name the student and the course, never an id: the second dataset has other ids. The Check runs your file the way lab run does, one statement at a time, and then reads the rows from another session.

  2. Task 2

    refund.sql: a refund with a savepoint

    Sana refunds Aarav Shah's latest paid order (the paid order of his with the latest created_at). In one transaction:

    first set that order's status to refunded and add a payment for it with gateway_ref rfnd_lco37aarav, amount equal to the order's total, status refunded and created_at 2026-09-21 12:00:00+05:30.

    Then set a savepoint and delete the enrollments that order gave him (his enrollments in the courses of its order items). Kabir decides refunded students keep their access: roll back to the savepoint, then commit.

    Afterwards another session sees the order refunded, the refund payment, and all of Aarav's enrollments still there.

  3. Task 3

    swap-sections.sql: swap two sections

    Neha wants Core ideas taught before Getting started in React from Zero: swap the positions of its sections 1 and 2 (Getting started becomes 2, Core ideas becomes 1). No other section changes.

    Afterwards sections must still refuse two sections at the same position in one course: keep a unique constraint on (course_id, position).

    Careful: a single UPDATE that swaps the two positions fails, because Postgres checks this unique rule row by row unless the constraint is declared DEFERRABLE.

  4. Task 4 · interview question

    Interview: open-transactions.sql, forgotten transactions

    Somebody's transaction has been open for hours and is holding things up. List the sessions in the current database whose transaction is open but which are doing nothing right now.

    Return three columns: pid, usename and xact_start (when the transaction began). Order: oldest transaction first, then pid.

    Careful: a transaction that hit an error and was never ended shows a slightly different state from one that just forgot to COMMIT. Count both. Leave out sessions in other databases and sessions that are simply idle.

What you will learn

  • Transactions: BEGIN, COMMIT, ROLLBACK, and why every statement is already one.
  • ACID: the four promises a transaction makes, and the part of Postgres that keeps each one.
  • Errors and savepoints: what an error does to a transaction, and how to undo only part of it.
  • Deferrable constraints: rules checked at the end instead of row by row.
  • Open transactions: how to find the one somebody forgot.

More in Safety, security and storage

Build The purchase yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.