One spinner, three separate budgets hiding behind it

Goal

You measure the three budgets — lock wait, statement execution, and retry count — yourself with two psql sessions. You receive 55P03 and 57014 separately, prove by backend PID that a budget set at transaction scope does not remain on the same connection, confirm that a failed transaction does not close by itself, and then write the budget table.

Why it matters

The three budgets the reading described cannot be told apart on screen. To the user they all look like a single "spinning circle", so they press the button again. A request pressed again is just one more attached to the line waiting for the same row, so it makes the problem worse. To tell the customer when it is fine to press again, you first need to know where and how long you waited, and whether that failure is the kind that may be tried again.

The way to tell them apart in code is not the error sentence but the SQLSTATE. Being cut off because the lock could not be acquired is 55P03 (lock_not_available), the statement itself being canceled is 57014 (query_canceled), and sending the next command inside a failed transaction is 25P02 (in_failed_sql_transaction). Code that compares message strings collapses the moment the locale changes, but these five characters do not change.

The place where you set the budget matters too. If you give true as the third argument of set_config, it lives only in that transaction and disappears on either a commit or a rollback. If you set it on the session or as a database default, the requests of whoever borrows that connection next are cut off in a short time. The cause of that incident is not in this code, so it is very hard to find.

The expected time is 80 minutes. Before it expires, press +time to extend the session. When the session ends, all files under /root disappear.

Environment

PostgreSQL 16 is already running inside the Pod. You connect with psql -X -U lab -d labdb, and the host is 127.0.0.1 (export PGHOST=127.0.0.1). No internet or additional installation is needed.

In this lab you work only inside the schema budget. Put all outputs under /root/budget. Order 9 of customer blue is a test row, so its revision goes up every time you run it and every time it is graded — do not memorize that value itself; read it again when you need it. The grader actually reruns the scripts you made to judge them, so your scripts must produce output of the same shape no matter how many times they run.

You do not handle waiting for a lock with a fixed sleep. Confirming in pg_stat_activity that the holder really holds the lock, and only then moving on, is the assignment of step 1, and all later steps use that script.

Steps

Create the schema budget and the table budget.orders with /root/budget/01-schema.sql — it has five columns, tenant, id, qty, state, and revision, the primary key is (tenant, id), and you insert three rows of customer blue: order 1 (7 units, pending, revision 3), order 2 (4 units, pending, revision 1), and order 9 (6 units, pending, revision 0). Order 9 is a row used for testing throughout this lab, so its version keeps going up. Then create /root/budget/holder.sh — when called as bash holder.sh <초> <주문번호> (the placeholders are the seconds and the order number), a background psql session grabs that row with select ... for update, holds it with pg_sleep, and then commits. The application_name of that session must be lockholder, and the script must poll pg_stat_activity, not use a fixed sleep, and only after confirming that the session is asleep while holding the lock print granted=yes and finish. Finally, run bash holder.sh 3 9 and leave four lines, rows, probe_id, granted, and holder_seconds, in /root/budget/01-holder.txt.
Create /root/budget/attempt.sh — when called as bash attempt.sh <잠금ms> <문장ms> <주문번호> (the placeholders are the lock ms, the statement ms, and the order number), it inside one transaction sets lock_timeout and statement_timeout by giving true as the third argument of set_config, updates that order row of customer blue with revision = revision + 1, and then commits. Whether it succeeds or fails, the exit code is 0, and it prints only three lines to standard output: sqlstate, rows, and elapsed_ms. If there is no error, sqlstate is 00000. Then hold row 9 with bash holder.sh 5 9, run bash attempt.sh 200 1500 9, and leave the result in /root/budget/02-lockwait.txt as five lines: sqlstate, lock_timeout_ms, statement_timeout_ms, waited_ms, and rows.
This time you set the two budgets the other way around. Hold row 9 with bash holder.sh 5 9 and run bash attempt.sh 400 150 9 — the lock budget is 400ms and the statement budget is 150ms. Leave the result in /root/budget/03-stmt.txt as five lines: inverted_lock_ms, inverted_statement_ms, inverted_sqlstate, normal_sqlstate, and first_budget. normal_sqlstate is the code you got in step 2, and in first_budget you write the name of the setting that cut it off first this time (lock_timeout or statement_timeout).
Create /root/budget/leak.sh — start psql only once and ask four times inside the same connection. Once outside a transaction (before); once inside a transaction after setting set_config('lock_timeout','250ms',true) and set_config('statement_timeout','1200ms',true) (inside); once after committing (after_commit); and once after setting them again and rolling back (after_rollback). All four lines are printed in the form 키=값/백엔드PID (the placeholders are the key, the value, and the backend PID). Looking at the run result, leave five lines, inside, after_commit, after_rollback, pid_same, and db_role_settings, in /root/budget/04-noleak.txt. db_role_settings is the number of entries in pg_db_role_setting in which lock_timeout or statement_timeout is stored.
Create /root/budget/retry.sh — when called as bash retry.sh <시도횟수> <잠금ms> <문장ms> <주문번호> (the placeholders are the number of attempts, the lock ms, the statement ms, and the order number), it calls attempt.sh, and only when sqlstate is 55P03, and only when there are attempts left, it pauses briefly and calls it again. The number of attempts includes the first call — if it is 3, that is the first one time and two retries. It passes any other code through as it is and does not call again. If the number of attempts is not an integer from 1 to 16, it does not call attempt.sh even once and prints tries=0, final_sqlstate=22023, and outcome=invalid. On the normal path it prints three lines, tries, final_sqlstate, and outcome, and outcome is applied if final_sqlstate is 00000 and gaveup otherwise. Run it once when nobody is holding the row, once with 3 200 1500 9 after bash holder.sh 6 9, and once with 3 400 150 9 while the same holder is still there, and leave the results in /root/budget/05-retry.txt as seven lines: free_tries, free_outcome, locked_tries, locked_outcome, locked_sqlstate, cancelled_tries, and cancelled_sqlstate.
Create /root/budget/idle.sh — when called as bash idle.sh <주문번호> (the placeholder is the order number), inside one connection it sets the two budgets at transaction scope and tries to grab that row with select ... for update and fails; before rolling back it sends one more arbitrary SELECT to see what happens; then it rolls back; after the rollback it sends a SELECT again, reads the current values of the two budgets, and compares the first and last backend PIDs. The output is six lines: sqlstate, aborted_sqlstate, after_rollback, lock_timeout_after, statement_timeout_after, and pid_same. Hold the row with bash holder.sh 5 9, run bash idle.sh 9, and leave those six lines as they are in /root/budget/06-idle.txt.
Create /root/budget/apply.sh — when called as bash apply.sh <잠금ms> <문장ms> <주문번호> <승인당시revision> (the placeholders are the lock ms, the statement ms, the order number, and the revision at approval time), it sets the two budgets inside one transaction and raises that row's revision by 1 only when both the revision at approval time and the pending state match. The output is three lines, sqlstate, rows, and verdict, and the verdict is as follows — applied if 1 row changes with no error, stale if 0 rows with no error, locked for 55P03, cancelled for 57014, and error for anything else. Read the current revision of row 9 and run it once with that value (applied), once more with the same value (stale), and, after bash holder.sh 5 9, once with 200 1500 at the current revision (locked) and once with 400 150 (cancelled), and leave the results in /root/budget/07-verdict.txt as six lines: fresh_verdict, stale_verdict, locked_verdict, locked_sqlstate, cancelled_verdict, and cancelled_sqlstate.
Finally, write the budget decided in this lab to /root/budget/08-budget.txt as ten lines — lock_timeout_ms is 200, statement_timeout_ms is 1500, attempts is 3, and retry_pause_ms is 200 (the values you have used since step 2). worst_case_db_wait_ms is the number of attempts times the lock budget, and worst_case_elapsed_ms is that plus the pause time times (the number of attempts minus 1). measured_locked_ms and measured_sqlstate are the actual values you get by holding the row with bash holder.sh 5 9 and running bash attempt.sh 200 1500 9 one more time. In retry_on, write the SQLSTATE you retry on, and in propagate, write the SQLSTATE you pass through as it is.

Notes

If you give psql -v VERBOSITY=verbose, the SQLSTATE appears on the error line too. In a script, extract only the five characters from that line and use them.
Common mistake 1: setting the budget on the session with set lock_timeout = .... As long as that connection is alive, the next transaction also uses that budget.
Common mistake 2: forgetting the rollback after a failure. Every next command on the same connection is rejected with 25P02.
Official documentation: Client connection defaults, set_config, Error codes, pg_stat_activity.

Create a state in which another owner is holding the row

Create the schema budget and the table budget.orders with /root/budget/01-schema.sql — it has five columns, tenant, id, qty, state, and revision, the primary key is (tenant, id), and you insert three rows of customer blue: order 1 (7 units, pending, revision 3), order 2 (4 units, pending, revision 1), and order 9 (6 units, pending, revision 0). Order 9 is a row used for testing throughout this lab, so its version keeps going up. Then create /root/budget/holder.sh — when called as bash holder.sh <초> <주문번호> (the placeholders are the seconds and the order number), a background psql session grabs that row with select ... for update, holds it with pg_sleep, and then commits. The application_name of that session must be lockholder, and the script must poll pg_stat_activity, not use a fixed sleep, and only after confirming that the session is asleep while holding the lock print granted=yes and finish. Finally, run bash holder.sh 3 9 and leave four lines, rows, probe_id, granted, and holder_seconds, in /root/budget/01-holder.txt.

The application_name is set by putting PGAPPNAME in front when you call psql. You can tell whether the lock is held by whether the wait_event in pg_stat_activity has become PgSleep — at that point, it means the FOR UPDATE has already finished. If you use a fixed sleep, when the machine is busy the next step starts before the lock is held, and the results become erratic.

Receive, in code, the evidence that the lock budget cut it off

Create /root/budget/attempt.sh — when called as bash attempt.sh <잠금ms> <문장ms> <주문번호> (the placeholders are the lock ms, the statement ms, and the order number), it inside one transaction sets lock_timeout and statement_timeout by giving true as the third argument of set_config, updates that order row of customer blue with revision = revision + 1, and then commits. Whether it succeeds or fails, the exit code is 0, and it prints only three lines to standard output: sqlstate, rows, and elapsed_ms. If there is no error, sqlstate is 00000. Then hold row 9 with bash holder.sh 5 9, run bash attempt.sh 200 1500 9, and leave the result in /root/budget/02-lockwait.txt as five lines: sqlstate, lock_timeout_ms, statement_timeout_ms, waited_ms, and rows.

To compare the SQLSTATE as a string, give psql -v VERBOSITY=verbose and the code appears on the error line too. Grepping the Korean or English sentence of the error message collapses when the locale changes. For waited_ms, you can just copy the elapsed_ms that attempt.sh printed.

If the statement budget is shorter than the lock budget, what cuts it off first?

This time you set the two budgets the other way around. Hold row 9 with bash holder.sh 5 9 and run bash attempt.sh 400 150 9 — the lock budget is 400ms and the statement budget is 150ms. Leave the result in /root/budget/03-stmt.txt as five lines: inverted_lock_ms, inverted_statement_ms, inverted_sqlstate, normal_sqlstate, and first_budget. normal_sqlstate is the code you got in step 2, and in first_budget you write the name of the setting that cut it off first this time (lock_timeout or statement_timeout).

The PostgreSQL documentation says it directly — if statement_timeout is not 0, setting lock_timeout equal to or larger than it is meaningless. That is because the statement budget always fires first. The fact that the two codes differ is what matters. One is waiting and failing to get the lock, and the other is the statement itself being canceled, so what you do next is different.

Prove with the same PID that the budget does not remain on the borrowed connection

Create /root/budget/leak.sh — start psql only once and ask four times inside the same connection. Once outside a transaction (before); once inside a transaction after setting set_config('lock_timeout','250ms',true) and set_config('statement_timeout','1200ms',true) (inside); once after committing (after_commit); and once after setting them again and rolling back (after_rollback). All four lines are printed in the form 키=값/백엔드PID (the placeholders are the key, the value, and the backend PID). Looking at the run result, leave five lines, inside, after_commit, after_rollback, pid_same, and db_role_settings, in /root/budget/04-noleak.txt. db_role_settings is the number of entries in pg_db_role_setting in which lock_timeout or statement_timeout is stored.

If you call psql four times, there are four connections and nothing is proven — that is why it has you print the PID too. Only if the PIDs on the four lines are the same is it the same connection. Setting it as a default on the database or the role (alter database ... set) is forbidden in this lab. Then other people's requests inherit that budget as well.

Only one kind of failure is worth trying again

Create /root/budget/retry.sh — when called as bash retry.sh <시도횟수> <잠금ms> <문장ms> <주문번호> (the placeholders are the number of attempts, the lock ms, the statement ms, and the order number), it calls attempt.sh, and only when sqlstate is 55P03, and only when there are attempts left, it pauses briefly and calls it again. The number of attempts includes the first call — if it is 3, that is the first one time and two retries. It passes any other code through as it is and does not call again. If the number of attempts is not an integer from 1 to 16, it does not call attempt.sh even once and prints tries=0, final_sqlstate=22023, and outcome=invalid. On the normal path it prints three lines, tries, final_sqlstate, and outcome, and outcome is applied if final_sqlstate is 00000 and gaveup otherwise. Run it once when nobody is holding the row, once with 3 200 1500 9 after bash holder.sh 6 9, and once with 3 400 150 9 while the same holder is still there, and leave the results in /root/budget/05-retry.txt as seven lines: free_tries, free_outcome, locked_tries, locked_outcome, locked_sqlstate, cancelled_tries, and cancelled_sqlstate.

Retrying is a count contract, not a time contract. The reason you try only a fixed number of times and give up, instead of waiting indefinitely until the lock is released, is that waiting requests piling up is itself the next incident. If you retry a statement cancellation (57014), the same statement consumes the same time again.

A failed transaction does not close by itself

Create /root/budget/idle.sh — when called as bash idle.sh <주문번호> (the placeholder is the order number), inside one connection it sets the two budgets at transaction scope and tries to grab that row with select ... for update and fails; before rolling back it sends one more arbitrary SELECT to see what happens; then it rolls back; after the rollback it sends a SELECT again, reads the current values of the two budgets, and compares the first and last backend PIDs. The output is six lines: sqlstate, aborted_sqlstate, after_rollback, lock_timeout_after, statement_timeout_after, and pid_same. Hold the row with bash holder.sh 5 9, run bash idle.sh 9, and leave those six lines as they are in /root/budget/06-idle.txt.

If you send the next command inside a transaction that hit an error, the server rejects it — that rejection has its own SQLSTATE too. If a failed request returns to the pool leaving the connection in that state, whatever the next person sends gets the same rejection. Also check whether the budgets disappear with a rollback. If you give psql ON_ERROR_STOP, it exits at the first error and you cannot see what follows.

Tell apart one that failed while waiting and one rejected because the approval went stale

Create /root/budget/apply.sh — when called as bash apply.sh <잠금ms> <문장ms> <주문번호> <승인당시revision> (the placeholders are the lock ms, the statement ms, the order number, and the revision at approval time), it sets the two budgets inside one transaction and raises that row's revision by 1 only when both the revision at approval time and the pending state match. The output is three lines, sqlstate, rows, and verdict, and the verdict is as follows — applied if 1 row changes with no error, stale if 0 rows with no error, locked for 55P03, cancelled for 57014, and error for anything else. Read the current revision of row 9 and run it once with that value (applied), once more with the same value (stale), and, after bash holder.sh 5 9, once with 200 1500 at the current revision (locked) and once with 400 150 (cancelled), and leave the results in /root/budget/07-verdict.txt as six lines: fresh_verdict, stale_verdict, locked_verdict, locked_sqlstate, cancelled_verdict, and cancelled_sqlstate.

The next action is different for all four results. locked can be tried again after a moment, cancelled consumes the same time again if you send the same statement again, and stale is forever 0 rows no matter how many times you resend it, so you need a new approval. This is why the retry condition is set on the sqlstate alone.

Write down in a table where and how long you wait

Finally, write the budget decided in this lab to /root/budget/08-budget.txt as ten lines — lock_timeout_ms is 200, statement_timeout_ms is 1500, attempts is 3, and retry_pause_ms is 200 (the values you have used since step 2). worst_case_db_wait_ms is the number of attempts times the lock budget, and worst_case_elapsed_ms is that plus the pause time times (the number of attempts minus 1). measured_locked_ms and measured_sqlstate are the actual values you get by holding the row with bash holder.sh 5 9 and running bash attempt.sh 200 1500 9 one more time. In retry_on, write the SQLSTATE you retry on, and in propagate, write the SQLSTATE you pass through as it is.

This table becomes the sentence that answers the customer — when it is fine to press again, and how long it takes in the worst case. The numbers written here cover only the DB wait and the retries. Connection setup, multiple SQL statements, and sending the response are outside this table, so do not say it equals the time budget of the whole API.