Introduction
A group wants to attend together. Booking one seat from a two-person request is not success. You will save a reusable SQL statement that returns a real confirmation for the entire group, or makes no change when there is not enough room. A real unique-key failure will test whether the claim and the confirmation belong to the same transaction.
This independent lab prepares fresh workshops and original bookings; it uses no files from the previous lab. You need its transaction, row-lock and ROLLBACK concepts, plus basic PostgreSQL SELECT, INSERT and UPDATE. Named client parameters, integer casts and a data-modifying common table expression are explained here. Workshop Desk is a shell; Desk A and Desk B are separate psql connections to the same database on one machine. The author's estimated duration includes reading and ordinary mistakes, without a novice timing study.
Create /home/labex/project/workshop-desk/reserve.sql. Keep the supplied tools, table definitions, original bookings, and every workshop's ID, title, capacity and note. Your file contains one SQL statement and no psql commands, shell commands, explicit BEGIN/COMMIT, configuration changes or external resources. It takes the four documented parameters and returns the actual booking's four fields. Equivalent safe SQL is accepted. This is a controlled READ COMMITTED exercise, without payments, automatic retries or distributed admission guarantees.
Save a Claim That Feeds a Confirmation
In this step, you will save one statement that cannot create a confirmation unless it has actually claimed the group's places.
Start in Workshop Desk. ./ runs a prepared program from the current directory. The status program reads real tables without filling in missing work:
./desk status
Workshop 201 has capacity 6, one original booked seat and 5 places left. Workshop 202 has 3 places, and 203 has 4. original-family|201|1|Original must remain. The first display uses workshop_id|title|capacity|places_left|booked_seats; the second uses request_code|workshop_id|seats|desk.
Your file will accept these psql variables: workshop_id, seats, request_code and desk. They are client parameters supplied separately for each request, not database columns or permanent server settings. The form :'name' asks psql to insert that variable as a safely quoted SQL string literal; it preserves an apostrophe inside a request code. Do not replace it with an unquoted :name. PostgreSQL's ::integer casts the workshop and positive group-size text to whole numbers.
WITH claimed AS (...) names a common table expression, or CTE: an intermediate relation used by this statement. Here the CTE performs a conditional UPDATE. Its RETURNING workshop_id supplies one row only when the complete requested group fits. INSERT ... SELECT ... FROM claimed consumes that actual returned row. An empty claim therefore produces no INSERT. The final RETURNING reports the booking actually inserted. Do not reread the base table expecting a data-modifying CTE's new values to be visible there in the same statement; use its RETURNING relation.
The subtraction uses the requested size, and the WHERE condition checks that same size. One statement runs in an implicit transaction: either both the claim and the INSERT succeed, or an SQL error rolls back both. In the later two-desk test, an explicit transaction will hold that same statement uncommitted so you can observe its lock.
Save the complete statement. cat > reserve.sql replaces that file with the following text. The quoted here-document marker SQL prevents the shell from changing its contents. Paste the closing SQL alone on its line. Run this in the shell Workshop Desk, not in either database desk:
cat > reserve.sql <<'SQL'
WITH claimed AS (
UPDATE workshops
SET places_left = places_left - :'seats'::integer
WHERE workshop_id = :'workshop_id'::integer
AND places_left >= :'seats'::integer
AND :'seats'::integer > 0
RETURNING workshop_id
)
INSERT INTO bookings (request_code, workshop_id, seats, desk)
SELECT :'request_code', workshop_id, :'seats'::integer, :'desk'
FROM claimed
RETURNING request_code, workshop_id, seats, desk;
SQL
Read the saved file before using it. cat displays it; a typo should be corrected in the saved source, not hidden in a separate command:
cat reserve.sql
desk book runs this exact file using native psql parameters; it does not supply a reference query, create fallback bookings or repair balances. Its arguments are workshop ID, positive group size, unique request code and desk label. Run a two-person normal request:
./desk book 201 2 normal-group A
The actual returned line is:
normal-group|201|2|A
Inspect the source of truth:
./desk status
Workshop 201 now has 3 places and 3 booked seats. The original family remains alongside your exact two-person booking. A returned line alone is not proof of a sound algorithm: the check also reads real rows, keeps originals and calls your unchanged source on different isolated data, including real competing connections. Those temporary fixture databases never repair this live desk.
If SQL fails, the program prints the native error and exits nonzero; it does not call that a shortage. In an interactive explicit transaction, first use ROLLBACK in that same desk. A typo that has not committed changes nothing. If you accidentally committed a new mistaken trial booking, remove only its exact code and recalculate only its workshop balance in one transaction, as taught in the previous lab. For example, in Desk A this no-op-safe recovery removes only a hypothetical typo-trial on 201:
BEGIN;
DELETE FROM bookings WHERE request_code = 'typo-trial' AND workshop_id = 201;
UPDATE workshops
SET places_left = capacity -
(SELECT COALESCE(SUM(seats), 0) FROM bookings WHERE workshop_id = 201)
WHERE workshop_id = 201;
COMMIT;
Never remove original-family or a legitimate requested booking. Inspect status before deciding which work remains. Do not rerun an already successful request merely because a later command failed.
Test Waiting Groups and a Failed Confirmation
In this step, you will run the same saved file in two independent native connections, keep another workshop moving, and prove that a failed INSERT cannot consume places.
Switch to Desk A. Its prompt means you are inside psql, not the shell. SQL ends with semicolons. psql commands beginning with a backslash control the client and have no semicolon. \set sets a client variable in this one connection; it does not carry over to another tab. \i reads and executes the named file. The absolute filename works independently of the client's current directory.
Start a READ COMMITTED transaction and submit a group of two for workshop 202, which initially has three places:
BEGIN ISOLATION LEVEL READ COMMITTED;
\set workshop_id 202
\set seats 2
\set request_code race-A
\set desk A
\i /home/labex/project/workshop-desk/reserve.sql
A sees race-A | 202 | 2 | A and INSERT 0 1: the statement inserted one booking row representing two seats. This is not yet committed. The * in the prompt marks the open transaction.
Switch to Desk B and prepare its independent request:
BEGIN ISOLATION LEVEL READ COMMITTED;
\set workshop_id 202
\set seats 2
\set request_code race-B
\set desk B
\i /home/labex/project/workshop-desk/reserve.sql
B waits for A's row lock, without returning a prompt. Leave it waiting and switch to Workshop Desk:
./desk waits
Desk B's wait_event_type is Lock, with A's actual backend process ID in blocked_by. Under READ COMMITTED, after A commits the waiting UPDATE will recheck sufficient places against the updated row. The earlier visible balance is not a reservation.
Still in Workshop Desk, use the same saved file to book a different workshop:
./desk book 203 2 other-workshop C
It returns other-workshop|203|2|C and finishes while A still holds 202. Do not introduce a single global lock that needlessly stops this unrelated workshop.
Switch to Desk A:
COMMIT;
Switch to Desk B. Its pending file now finishes with zero returned rows and INSERT 0 0. Only one place remains, so this two-person group does not fit. Do not split the group or insert a separate confirmation. End B's transaction:
ROLLBACK;
Switch to Workshop Desk:
./desk status
Workshop 202 has one place left and exactly race-A|202|2|A; there is no race-B. Workshop 203 has two places left and its two-person booking. Check the relationship, not just a success exit code.
Next make the INSERT fail while there is still enough room to claim a seat on 202. A primary key requires each request code to be unique across the whole bookings table, not just within a workshop. The code original-family already belongs to 201. Reusing it here should produce SQLSTATE 23505, a unique-key violation. This is an expected failure; it is not a no-availability result:
./desk book 202 1 original-family A
The file execution reports its native error and exits with status 3. Capture that status immediately, before any other shell command replaces $? (the previous command's exit status):
printf 'Previous native exit: %s\n' "$?"
Expect Previous native exit: 3. A nonzero status distinguishes the error from a successful no-row result. The statement's implicit transaction must undo the preceding claim. Read the ledger again:
./desk status
Workshop 202 still has one place, and original-family is unchanged on 201. A shortcut such as ON CONFLICT DO NOTHING after the claim can hide the INSERT failure while committing the decrement. That is not an atomic refusal: the count would stop matching the actual bookings. The checker tests this case with enough room, so an empty claim cannot accidentally make the error test pass.
Use a fresh request code to book that final place legitimately:
./desk book 202 1 after-error A
Expect after-error|202|1|A. Now request another group of two when no places remain:
./desk book 202 2 too-late B
The program prints No booking returned. Inspect the real ledger before drawing a conclusion. This call exits zero because the SQL was valid and returned no booking; it must leave no too-late row and make no balance change.
./desk status
The final new bookings are normal-group (201, 2, A), race-A (202, 2, A), other-workshop (203, 2, C) and after-error (202, 1, A), plus the untouched original family. There is no race-B or too-late. Remaining places are 3, 0 and 2 respectively.
If you cancel a waiting file with Ctrl+C, SQLSTATE 57014 aborts its explicit transaction. As practiced previously, use ROLLBACK in that same database tab before retrying. First inspect the committed ledger: the other desk may already have succeeded. An interactive SQL error and an ordinary zero-row result require different decisions.
Summary
Your saved SQL turns an actual conditional place claim into exactly one whole-group confirmation. Two native connections demonstrated waiting and re-evaluation, while another workshop continued. A duplicate request code caused a real INSERT failure and rolled back its claim; a later legitimate request then used the still-available place. The reusable file and live ledger preserve original data and distinguish a shortage from an SQL error.
