Rename a Column While the Old Badge App Is Running

Goal

You migrate the alien festival's name badge app from name to display_name. You make it work even while the old app is still changing names, and after even the transition app has been retired, you remove the old column.

Why it matters

Even renaming a single column can break apps, batches, and query tools that are running at the same time. You practice separating expand → compatible reads and writes → backfill → constraint hardening → retirement confirmation → contract. First study Python functions and exceptions, SQL transactions, and the earlier conditional-change lab. The expected time is 130 minutes. Extend with +time before it expires and finish within 180 minutes at most. Files disappear when the session ends. Keep the code you need separately.

Environment and common contract

The deliverable is /root/schema/worker.py. You use the image's Python 3, PostgreSQL 16, and psycopg 3.2.3, and no installation, download, or extra permission is needed. You can write to /root as the postgres user.

The grader prepares fictional name badges in a unique temporary schema of the local labdb and cleans up only the schema it created. Use the search_path and the DSN of the con you are given. Do not touch the public tables or hardcode the schema name, data, or IDs. The table, column, and trigger names are the fixed contract below, and SQL data values are passed as parameters.

CREATE TABLE badges(id integer PRIMARY KEY, name text NOT NULL);

The ID is an exact int 1–2147483647 that is not a bool. The name is an exact str of 1–100 characters and does not allow leading or trailing whitespace or character codes 0–31 and 127. Korean, emoji, and single quotes are allowed. The backfill ids is an exact list of 1–16 items, and duplicate IDs are rejected. A wrong direct input is a ValueError, and you do not turn a SQL error into an arbitrary success value.

Every con is a borrowed connection, autocommit=True and Read Committed, with no open transaction at the start of an external call. You do not close the connection and leave no open transaction after success or failure. expand, install_bridge, backfill, add_guard, enforce, and cutover apply lock_timeout=500ms and statement_timeout=2000ms in their own transaction and restore the original connection settings after success or failure. The limits apply per SQL statement and are not a sum of the whole function time. Do not wrap these functions in a separate outer transaction. Only cutover_file owns a connection.

If fault is absent, omit it, and if present, call it with the string specified in each step as the argument. A hook error before the commit rolls back that function's entire change and propagates the original error. An error in after-commit is propagated while preserving the change that was already committed. expand, install_bridge, add_guard, and cutover are functions that run once in each earlier step. They do not promise an unconditional rerun of a successful DDL. If you lose the response after the commit, you have to check the current schema and the deployment record.

Compatibility trigger rules

On BEFORE INSERT, if only name is present, it copies to display_name, and if only display_name is present, it copies to name. If both have the same value, it is allowed as it is, and if both are NULL or they differ from each other, it is rejected with SQLSTATE 23514.

On BEFORE UPDATE, if only name changed, it copies the new name to display_name, and if only display_name changed, it copies the new display_name to name. If both values changed, only the same non-NULL value on both is allowed. For an existing row where neither changed but display_name is NULL, it fills in name. If a NULL remains in the final two fields or the values differ from each other, it is SQLSTATE 23514. This rule synchronizes a valid single-field write, but it does not automatically correct a request that clears to NULL or two contradictory values.

Order of progress

Before the expansion, the old client's name reads and writes work. After the expansion, read_compatible reads the existing rows. After the compatibility trigger is installed, you perform the per-target backfill while receiving writes from both the old and new clients. The NOT VALID constraint can also be added before the full backfill, but enforce succeeds only when no NULL remains. Before the contraction, you need all of the following: a real NOT NULL, a validated constraint, and identical old and new names.

legacy_retired is the retirement approval of the old app that writes only name, and transition_retired is the retirement approval of transition reads and batches such as COALESCE(display_name,name). The final app does not reference the old column at all. The two approval flags are merely records of an external confirmation, and the function does not automatically prove that every app in the organization has terminated. In a real service, you have to separately check the owners, the deployed versions, query observations, and the rollback plan.

Steps

Set the boundary of the badge input — implement a Conflict that subclasses Exception and request(person_id,name). Validate the input contract below and return a new dict with the two keys id and name. A wrong input is a ValueError, and you do not arbitrarily trim whitespace or convert to a number.
Open the new column while preserving the old column — expand(con,fault=None) sets the time limits in its own transaction and adds a nullable text column display_name to badges. The order is the after-column hook, then the after-commit hook after the real COMMIT. It preserves the existing id, name, rows, and constraints, and the new column of the existing rows is NULL. It does no rename, default, or immediate backfill. The normal return value is None.
Build a transition app that reads both before and after the backfill — read_compatible(con,person_id) validates the ID and returns the new name if the new column is not NULL, and the old name if it is NULL. A missing ID gives None, and it does not change data. This function is a transition client from after the expansion until the old column is removed, and it is not the final client.
Receive writes from both old and new apps — install_bridge(con,fault=None) creates the trigger function sync_badge_name(), calls after-function, creates the badge_compat BEFORE INSERT OR UPDATE FOR EACH ROW trigger, calls after-trigger, and then calls after-commit after the real COMMIT. The installation of the two objects is atomic, and it does not backfill the existing rows. The trigger follows the two-way rules below. The normal return value is None.
Write a backfill that does not overwrite concurrent modifications — backfill(con,ids,fault=None) validates the target list before writing and sets the time limits in its own transaction. Among the given IDs, it fills only the rows whose current display_name is NULL, using the DB's current name. It returns an ascending list of the IDs it changed. The hooks are after-write after the change and after-commit after the real COMMIT, and on failure it rolls back this whole backfill. Nonexistent IDs and already migrated IDs are skipped. Rerunning the same targets returns an empty list.
Separate the check of existing rows from the restriction on new writes — add_guard(con) adds display_present CHECK(display_name IS NOT NULL) NOT VALID in its own transaction with the time limits set, and returns None. enforce(con,fault=None) VALIDATEs the same constraint and calls after-validate, runs SET NOT NULL on the real column and then calls after-notnull, and calls after-commit after the real COMMIT, and returns None. A remaining NULL propagates the original DB exception and does not backfill automatically.
Drop the old column only after the retirement approvals of two generations — cutover(con,approvals,fault=None) validates, before writing, an exact dict that has only the two keys legacy_retired and transition_retired and that each value is exactly True. In its own transaction it sets the time limits and acquires the ACCESS EXCLUSIVE lock on badges. It checks that display_name has a real NOT NULL, that display_present has been validated, and that name and display_name match in every row, and if not complete, it is a Conflict. It removes only badge_compat and sync_badge_name() and calls after-bridge-drop, removes only the name column and calls after-column-drop, and calls after-commit after the real COMMIT, in that order. It returns True and preserves the existing IDs and the new names.
Verify the final app and a client termination during the DDL — read_current(con,person_id) validates the ID, queries only display_name, and returns the name, or None for a missing ID. write_current(con,person_id,name) validates the input and then, in its own transaction, modifies only the display_name of that ID and returns an id and name dict. A missing ID is a Conflict and is not added. cutover_file(dsn,approvals,fault=None) owns a connection with psycopg.connect(dsn,autocommit=True,connect_timeout=2), passes on the result or error of cutover, and closes on both success and failure. The final app must work both before and after the contraction.

Notes

Self-diagnosis: python3 -B /opt/lab/fixtures/schema/check.py 8 /root/schema/worker.py. If you change the number to the current step, it checks cumulatively. The limit for one grading is 40 seconds.
It checks whether the backfill preserves the latest change under another connection's real row lock. It does not look only at whether the same SQL string was submitted.
After the old column is removed, the old client and the transition client must actually get an UndefinedColumn error. The final client must keep working. Observing this difference is part of what you learn.
The DDL client terminations are at three points: after the trigger is removed, after the column is removed, and after the commit. The DB server is not terminated. Because the lab image has fsync and full_page_writes turned off, this does not prove power failure durability or zero-downtime throughput.
Do not use a production DB, real personal information, or external services. The whole process runs only inside a per-student disposable DB.

Set the boundary of the badge input

Implement a Conflict that subclasses Exception and request(person_id,name). Validate the input contract below and return a new dict with the two keys id and name. A wrong input is a ValueError, and you do not arbitrarily trim whitespace or convert to a number.

A name that contains SQL syntax characters is also normal data. Distinguish value validation from SQL parameterization.

Open the new column while preserving the old column

expand(con,fault=None) sets the time limits in its own transaction and adds a nullable text column display_name to badges. The order is the after-column hook, then the after-commit hook after the real COMMIT. It preserves the existing id, name, rows, and constraints, and the new column of the existing rows is NULL. It does no rename, default, or immediate backfill. The normal return value is None.

A SELECT on another connection can also make the DDL wait. Do not wait for the lock forever, but preserve the original connection settings.

Build a transition app that reads both before and after the backfill

read_compatible(con,person_id) validates the ID and returns the new name if the new column is not NULL, and the old name if it is NULL. A missing ID gives None, and it does not change data. This function is a transition client from after the expansion until the old column is removed, and it is not the final client.

Look together at the argument order of COALESCE and the column reference that remains in the SQL. Even if the first argument has a value, you cannot reference a column that no longer exists.

Receive writes from both old and new apps

install_bridge(con,fault=None) creates the trigger function sync_badge_name(), calls after-function, creates the badge_compat BEFORE INSERT OR UPDATE FOR EACH ROW trigger, calls after-trigger, and then calls after-commit after the real COMMIT. The installation of the two objects is atomic, and it does not backfill the existing rows. The trigger follows the two-way rules below. The normal return value is None.

Comparing with NULL needs IS DISTINCT FROM. Distinguish the direction of change between the old value and the new value, and do not quietly overwrite two contradictory values.

Write a backfill that does not overwrite concurrent modifications

backfill(con,ids,fault=None) validates the target list before writing and sets the time limits in its own transaction. Among the given IDs, it fills only the rows whose current display_name is NULL, using the DB's current name. It returns an ascending list of the IDs it changed. The hooks are after-write after the change and after-commit after the real COMMIT, and on failure it rolls back this whole backfill. Nonexistent IDs and already migrated IDs are skipped. Rerunning the same targets returns an empty list.

If you keep the old name in the app with a SELECT and then UPDATE, you can lose a modification that was committed during the wait. Think about the current NULL condition of the UPDATE and copying the value inside the DB.

Separate the check of existing rows from the restriction on new writes

add_guard(con) adds display_present CHECK(display_name IS NOT NULL) NOT VALID in its own transaction with the time limits set, and returns None. enforce(con,fault=None) VALIDATEs the same constraint and calls after-validate, runs SET NOT NULL on the real column and then calls after-notnull, and calls after-commit after the real COMMIT, and returns None. A remaining NULL propagates the original DB exception and does not backfill automatically.

NOT VALID does not mean the constraint is turned off. Check separately that the existing rows' validation is complete and the real NOT NULL in pg_attribute.

Drop the old column only after the retirement approvals of two generations

cutover(con,approvals,fault=None) validates, before writing, an exact dict that has only the two keys legacy_retired and transition_retired and that each value is exactly True. In its own transaction it sets the time limits and acquires the ACCESS EXCLUSIVE lock on badges. It checks that display_name has a real NOT NULL, that display_present has been validated, and that name and display_name match in every row, and if not complete, it is a Conflict. It removes only badge_compat and sync_badge_name() and calls after-bridge-drop, removes only the name column and calls after-column-drop, and calls after-commit after the real COMMIT, in that order. It returns True and preserves the existing IDs and the new names.

The row counts being equal does not mean the names are equal. Do not commit the approval check, the data check, and the DDL separately.

Verify the final app and a client termination during the DDL

read_current(con,person_id) validates the ID, queries only display_name, and returns the name, or None for a missing ID. write_current(con,person_id,name) validates the input and then, in its own transaction, modifies only the display_name of that ID and returns an id and name dict. A missing ID is a Conflict and is not added. cutover_file(dsn,approvals,fault=None) owns a connection with psycopg.connect(dsn,autocommit=True,connect_timeout=2), passes on the result or error of cutover, and closes on both success and failure. The final app must work both before and after the contraction.

The client process is really terminated during the DDL. If it is before the commit, everything is restored; if it is after the commit, the new schema is kept. Do not handle a lost response with an unconditional recall.