TT Lab
Get started
Learn Learning paths Courses

Irreversible Changes

Two orders approved at dawn, one still eligible by noon

Continue in TT Lab

Goal

Cancel the two approved orders using SQL alone. Record the observation at approval time in a table, build an UPDATE that changes a row only when the customer, number, version, quantity, and state are all unchanged, and confirm in a real database that one order that another owner changed in the meantime gets 0 rows changed.

Why it matters

The contract you saw in the reading (the approval covers id, revision, and qty, and the customer discriminator tenant is separate) is turned into tables and a function here. There is always time between the moment you looked at the approval screen and the moment you run. Blocking that gap with a table lock stops other work, so instead you leave the observation at approval time as small data and, when applying, compare by condition whether that observation is still valid. If the comparison does not match, you do not quietly change the value to the current value; you leave that item alone and have it judged again. The table in this lab has the primary key (tenant, id), so it also shows in the same place that if you drop the customer from the condition, another customer's order is canceled along with it.

The fde-revision-lab in the very next module implements the same contract in Python as eight functions. Before that, this lab first looks at what the contract itself looks like inside the database, in a single layer of SQL.

The expected time is 70 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 scope. Do not modify the existing lab data in public or other schemas. Put all outputs under /root/scope. The grader checks the numbers written in the files by asking the live tables again, and for the function steps it sets up sample rows inside its own transaction, calls your function, and then rolls back — so the data does not change no matter how many times it is graded.

Steps

  1. Create /root/scope/01-schema.sql to create the schema scope and two tables. scope.orders has five columns, tenant, id, qty, state, and revision, and the primary key is (tenant, id). state allows only pending, paid, and cancelled, qty is 1 to 1000, and revision is 0 or more. Insert six rows — blue/1 has 7 units, pending, revision 3; blue/2 has 4 units, pending, revision 1; blue/3 has 9 units, pending, revision 0; blue/4 has 5 units, paid, revision 2; green/1 has 7 units, pending, revision 3; and green/2 has 4 units, pending, revision 1. scope.baseline has the same five columns with the same primary key, and you fill it by copying the scope.orders you just created as is — it is the observation from the moment the lookup screen showed it in the morning. After applying the SQL, leave four lines, rows, tenants, pk, and baseline_rows, in /root/scope/01-schema.txt.
  2. Create the table scope.approval with /root/scope/02-approval.sql — it has five columns, change_id, tenant, id, revision, and qty, and the primary key is (change_id, tenant, id). With the change ID chg-rain-01, pick from orders 1 and 2 of customer blue only those that were pending in the morning observation (scope.baseline) and insert them along with the revision and qty at that time. Before inserting, delete any existing rows with the same change_id so that the result is the same no matter how many times you run it. Then leave three lines, change_id, targets, and digest, in /root/scope/02-approval.txt. The digest is a string in which the approval rows are written in id order as id:revision:qty and joined with commas.
  3. Create the function scope.validate_targets(p jsonb) returns jsonb with /root/scope/03-validate.sql. The input is an array of approval targets. Raise an exception with SQLSTATE 22023 if the input is not an array, if it has 0 elements or more than 16, if an element is not an object, if the keys are not exactly the three id, revision, and qty, if any of the three values is not a JSON number (true/false and strings are not numbers), if it is not an integer, if id is outside 1 to 2147483647, if revision exceeds 2147483646, if qty is outside 1 to 1000, or if the same id appears twice. If it passes, return a new array sorted by id ascending, in which each element has only the three keys id, revision, and qty.
  4. Create the function scope.apply_change(p_change_id text) returns table(changed integer, skipped integer) with /root/scope/04-apply.sql. If that change ID has no approval rows at all, raise an exception with SQLSTATE 22023. If it has, update scope.orders, but change only the rows where tenant, id, revision, and qty equal the values at approval time and state is pending. A changed row gets state cancelled and its revision goes up by 1. changed is the number of rows actually changed, and skipped is the number of approval targets minus changed. In this step, only create the function and do not apply it to chg-rain-01.
  5. Over lunch, another owner fixed the quantity of blue's order 2 to 5. It is a normal business change, so the revision goes up by 1 as well. Create that change with /root/scope/05-drift.sql, adding a condition so that it is applied only while revision is still 1, so that the result is the same no matter how many times you run it. Then leave six lines, tenant, id, new_qty, new_revision, approved_qty, and approved_revision, in /root/scope/05-drift.txt. Take the first three from scope.orders as it is now, and the last two from scope.approval.
  6. Apply chg-rain-01 for real — it is select * from scope.apply_change('chg-rain-01'). Leave the result in /root/scope/06-apply.txt as five lines, changed, skipped, applied_id, blocked_id, and other_tenant_changed. applied_id is the number of the order that was actually canceled, and blocked_id is the number of the order that was an approval target but did not change. other_tenant_changed is the number of rows of customer green that differ from the morning observation, and you must count it by matching scope.baseline against scope.orders.
  7. Create the function scope.reconcile(p_change_id text) returns table(id integer, verdict text) with /root/scope/07-reconcile.sql. For each approval row, look at scope.orders as it is now and attach a verdict — if there is no row with the same customer and number, missing; if there is one, state is cancelled, revision is the approved value + 1, and qty equals the approved value, matching; everything else is drifted. The result is in id ascending order and does not include numbers that are not in the approval. It must be a read-only function. After creating it, call it with chg-rain-01 and leave four lines, matching, drifted, missing, and verdicts, in /root/scope/07-reconcile.txt. verdicts is a string in which the verdicts are written as id:판정 in id order and joined with commas (the placeholder is the verdict).
  8. Finally, create the reconciliation table to send to the customer in /root/scope/08-report.txt as six lines — approved_targets, applied, drifted, missing, changed_rows_total, and changed_outside_approval. Take the first four from the function of step 7, changed_rows_total as the number of rows where any of qty, state, or revision differs when you match scope.baseline against scope.orders, and changed_outside_approval as the number of those differing rows that are not approval targets of chg-rain-01. Do not write any of the six values by hand; put the query results in as they are.

Notes

Keep the target table and the morning observation together

Create /root/scope/01-schema.sql to create the schema scope and two tables. scope.orders has five columns, tenant, id, qty, state, and revision, and the primary key is (tenant, id). state allows only pending, paid, and cancelled, qty is 1 to 1000, and revision is 0 or more. Insert six rows — blue/1 has 7 units, pending, revision 3; blue/2 has 4 units, pending, revision 1; blue/3 has 9 units, pending, revision 0; blue/4 has 5 units, paid, revision 2; green/1 has 7 units, pending, revision 3; and green/2 has 4 units, pending, revision 1. scope.baseline has the same five columns with the same primary key, and you fill it by copying the scope.orders you just created as is — it is the observation from the moment the lookup screen showed it in the morning. After applying the SQL, leave four lines, rows, tenants, pk, and baseline_rows, in /root/scope/01-schema.txt.

If you make the primary key id alone, you cannot even insert green's order 1. The premise of this table is that the same order number can exist when the customer is different. Use create table if not exists and on conflict do nothing so that running it again gives the same result. On the pk line, write the primary key column names in order, joined with commas.

Pin the observation at approval time into a table

Create the table scope.approval with /root/scope/02-approval.sql — it has five columns, change_id, tenant, id, revision, and qty, and the primary key is (change_id, tenant, id). With the change ID chg-rain-01, pick from orders 1 and 2 of customer blue only those that were pending in the morning observation (scope.baseline) and insert them along with the revision and qty at that time. Before inserting, delete any existing rows with the same change_id so that the result is the same no matter how many times you run it. Then leave three lines, change_id, targets, and digest, in /root/scope/02-approval.txt. The digest is a string in which the approval rows are written in id order as id:revision:qty and joined with commas.

The approval snapshot holds the values from the moment of approval, not the current values. That is why you read from scope.baseline, not scope.orders. If you build the digest with string_agg plus order by, you get the same string even if the row order changes.

Filter out duplicates, empty lists, and non-numbers before writing

Create the function scope.validate_targets(p jsonb) returns jsonb with /root/scope/03-validate.sql. The input is an array of approval targets. Raise an exception with SQLSTATE 22023 if the input is not an array, if it has 0 elements or more than 16, if an element is not an object, if the keys are not exactly the three id, revision, and qty, if any of the three values is not a JSON number (true/false and strings are not numbers), if it is not an integer, if id is outside 1 to 2147483647, if revision exceeds 2147483646, if qty is outside 1 to 1000, or if the same id appears twice. If it passes, return a new array sorted by id ascending, in which each element has only the three keys id, revision, and qty.

jsonb_typeof distinguishes true/false as boolean and a quoted number as string. A decimal point is not caught by typeof, so extract the value as a string and check once more that it is an integer. To specify the SQLSTATE when raising an exception, use raise exception using errcode.

A function that changes only when everything approved is unchanged

Create the function scope.apply_change(p_change_id text) returns table(changed integer, skipped integer) with /root/scope/04-apply.sql. If that change ID has no approval rows at all, raise an exception with SQLSTATE 22023. If it has, update scope.orders, but change only the rows where tenant, id, revision, and qty equal the values at approval time and state is pending. A changed row gets state cancelled and its revision goes up by 1. changed is the number of rows actually changed, and skipped is the number of approval targets minus changed. In this step, only create the function and do not apply it to chg-rain-01.

If you attach the approval table to the UPDATE with a FROM clause, you can put all five conditions in one statement. To count exactly how many rows actually changed, it is accurate to wrap RETURNING in a CTE and count — if you count with a SELECT first and then UPDATE, you miss changes made in between. The grader sets up sample rows in its own transaction, calls this function, and rolls back, so the function can use a fixed schema name.

After the approval, another owner changes one order

Over lunch, another owner fixed the quantity of blue's order 2 to 5. It is a normal business change, so the revision goes up by 1 as well. Create that change with /root/scope/05-drift.sql, adding a condition so that it is applied only while revision is still 1, so that the result is the same no matter how many times you run it. Then leave six lines, tenant, id, new_qty, new_revision, approved_qty, and approved_revision, in /root/scope/05-drift.txt. Take the first three from scope.orders as it is now, and the last two from scope.approval.

The approval snapshot must not change by even one character in this step. Only the current row has changed. The very fact that the two numbers now differ from each other is the reason 0 rows get changed in the next step.

When you apply, only one order changes

Apply chg-rain-01 for real — it is select * from scope.apply_change('chg-rain-01'). Leave the result in /root/scope/06-apply.txt as five lines, changed, skipped, applied_id, blocked_id, and other_tenant_changed. applied_id is the number of the order that was actually canceled, and blocked_id is the number of the order that was an approval target but did not change. other_tenant_changed is the number of rows of customer green that differ from the morning observation, and you must count it by matching scope.baseline against scope.orders.

There are two approval targets, but one has a value that differs from the morning observation. The row changes only when all five conditions match, so this row is simply passed over — it is 0 rows, not an error. Green's order 1 has the same number, version, and quantity as blue's order 1. Here it shows whether the customer is in the condition.

Reconcile by target, not by count

Create the function scope.reconcile(p_change_id text) returns table(id integer, verdict text) with /root/scope/07-reconcile.sql. For each approval row, look at scope.orders as it is now and attach a verdict — if there is no row with the same customer and number, missing; if there is one, state is cancelled, revision is the approved value + 1, and qty equals the approved value, matching; everything else is drifted. The result is in id ascending order and does not include numbers that are not in the approval. It must be a read-only function. After creating it, call it with chg-rain-01 and leave four lines, matching, drifted, missing, and verdicts, in /root/scope/07-reconcile.txt. verdicts is a string in which the verdicts are written as id:판정 in id order and joined with commas (the placeholder is the verdict).

If you put the approval rows on the left and LEFT JOIN the current rows, a target that has disappeared remains as NULL, so you can tell missing apart. You must put both the customer and the number in the join condition so that the same number of another customer does not attach. Even if the quantity is the same, a different version means a later, different change.

Write the approved scope and the actual changes side by side

Finally, create the reconciliation table to send to the customer in /root/scope/08-report.txt as six lines — approved_targets, applied, drifted, missing, changed_rows_total, and changed_outside_approval. Take the first four from the function of step 7, changed_rows_total as the number of rows where any of qty, state, or revision differs when you match scope.baseline against scope.orders, and changed_outside_approval as the number of those differing rows that are not approval targets of chg-rain-01. Do not write any of the six values by hand; put the query results in as they are.

The last line is the conclusion of this lab. It is the number that shows, by target rather than by count, that nothing changed outside the approved scope. When you compare the morning observation with now, NULL can get mixed in, so it is safe to use is distinct from.