PostgreSQL transactions: keep a workshop booking consistent

Start with one booking rule

This evergreen lesson fills the October 1 slot in our learning series and was first published on October 5, 2026. It is a database fundamentals exercise, not a database release announcement. The example uses fictional workshop records and should be attempted in a disposable practice database.

Imagine a workshop with two remaining seats. When learner L1 books, the application should reduce the available seats to one and store one booking. Write both expected changes before opening a query editor. A seat count without a booking would leave the team unable to explain where the place went.

Understand the transaction boundary

PostgreSQL supports transaction blocks using BEGIN and COMMIT. ROLLBACK cancels the uncommitted changes in that block. This groups related database work into an all-or-nothing result. Transactions do not automatically decide which business rules your application should enforce; you still need to define those rules and check the outcome.

Draw a small box around the two writes in your notes: update the workshop, then insert the booking. Keep activities such as emailing the learner outside that box in this first exercise. A database rollback does not undo an email already sent, so do not describe external actions as if they were part of the same guarantee.

Source: PostgreSQL documentation: Transactions

Prepare a tiny practice dataset

Create a workshops table with an integer primary key and an integer seats_remaining column defined as NOT NULL with CHECK (seats_remaining >= 0). Create a bookings table with a primary key, a NOT NULL workshop_id foreign key and a NOT NULL learner_code. Add a UNIQUE constraint across workshop_id and learner_code.

Insert workshop 101 with two seats and no bookings. Save a query result showing that starting state. The constraints express three different requirements: a seat count is present and non-negative, a booking points to an existing workshop, and the same learner cannot book that workshop twice. They complement the transaction rather than replacing it.

Source: PostgreSQL documentation: Constraints

Run a successful booking and an intentional rollback

In one database session, begin a transaction. Run UPDATE workshops SET seats_remaining = seats_remaining - 1 WHERE id = 101 AND seats_remaining > 0; and verify that exactly one row changed. Only then insert booking 1 for workshop 101 and learner L1, and commit. Query both tables: expect one seat remaining and one booking.

Next, begin another transaction and decrement the same workshop as before. Before inserting another booking, deliberately issue ROLLBACK to simulate an application abandoning the operation. Query both tables again. The expected state is still one seat and one booking. Capture the commands and results so another learner can repeat the experiment from the same starting data.

Source: PostgreSQL documentation: Transactions

Make the failure explainable

Try a third transaction that decrements the seat count and attempts to insert another booking for L1 with a different booking primary key. The learner/workshop uniqueness constraint should reject the insert. Issue ROLLBACK to clear the failed transaction, then check that the seat count has returned to one and the original booking remains.

Write a short evidence record with the initial rows, the rejected operation, the observed error and the final rows. If the results differ, inspect whether your query tool automatically committed a statement or whether you used different sessions. Do not fix a duplicate booking error by removing the constraint; first check whether the request itself was repeated.

Source: PostgreSQL documentation: ConstraintsPostgreSQL documentation: Transactions

Explain the limits and choose the next test

This exercise covers a controlled sequence in one session. A real booking service also needs to handle concurrent requests, zero updated rows, retries, authorization and connection failures. In particular, the application must refuse the insert when the guarded seat update changes no rows. Otherwise, a transaction can faithfully save a logically incorrect booking.

For a portfolio, present the requirement and both outcomes together. Explain why you chose the transaction boundary, which constraints helped, and which behaviours remain untested. That is more useful than saying you know every database feature. Continue with a two-session booking experiment as a separate task, or use the related Full Stack + AI learning path to practise application-side validation.

Sources and further reading

• PostgreSQL documentation: Transactions · checked 2026-10-05
• PostgreSQL documentation: Constraints · checked 2026-10-05

Spot an error? Email info@spothub.in with the article link and correction.