Add a ticket state#
A support tool stores ticket states in an enum. A new feature adds waiting_on_customer. We ran this on PostgreSQL 16.15.
CREATE TYPE ticket_state AS ENUM ('open', 'closed');
CREATE TABLE tickets (id integer PRIMARY KEY, state ticket_state NOT NULL);
INSERT INTO tickets VALUES (1, 'open'), (2, 'closed');
A migration that adds the value and moves tickets into it in the same transaction fails:
BEGIN;
ALTER TYPE ticket_state ADD VALUE 'waiting_on_customer' AFTER 'open';
UPDATE tickets SET state = 'waiting_on_customer' WHERE id = 1;
ERROR: unsafe use of new value "waiting_on_customer" of enum type ticket_state
HINT: New enum values must be committed before they can be used.
PostgreSQL 12 and later allow ADD VALUE inside a transaction block, but, as the ALTER TYPE documentation says, the new value cannot be used until the transaction commits. The failed UPDATE aborts the transaction, and ROLLBACK undoes the ADD VALUE with it. Put the ADD VALUE in its own migration and any data change in a later one:
ROLLBACK;
-- migration 1: commits on its own
ALTER TYPE ticket_state ADD VALUE 'waiting_on_customer' AFTER 'open';
Retry the migration#
A migration rerun after a partial failure meets the value it already added:
ALTER TYPE ticket_state ADD VALUE 'waiting_on_customer' AFTER 'open';
-- ERROR: enum label "waiting_on_customer" already exists
ALTER TYPE ticket_state ADD VALUE IF NOT EXISTS 'waiting_on_customer' AFTER 'open';
-- NOTICE: enum label "waiting_on_customer" already exists, skipping
IF NOT EXISTS lets the statement run again without an error. Unlike the index case, an existing enum label cannot be half built, so skipping it hides nothing.
The previous release reads the new value#
Once a new release writes waiting_on_customer, every reader sees it, including the release that is still serving during a rolling deployment and the one you would restore in a rollback. If that release maps database values into a closed set in code, the read fails. A Python enum shows the shape of the failure:
from enum import Enum
class TicketState(Enum):
OPEN = "open"
CLOSED = "closed"
TicketState("waiting_on_customer")
# ValueError: 'waiting_on_customer' is not a valid TicketState
Java's Enum.valueOf throws IllegalArgumentException the same way, and so does any runtime check against a closed set of values. The failing request is often a list page that reads every ticket, so one row in the new state breaks the page for everyone on the old release.
Ship it in three steps:
- Add the value to the database.
- Deploy a release that reads the new value and handles unknown values without failing, but does not write it.
- Deploy a release that writes it, once no earlier release is running or kept as a rollback target.
You cannot take it back#
PostgreSQL has no ALTER TYPE ... DROP VALUE:
ALTER TYPE ticket_state DROP VALUE 'waiting_on_customer';
-- ERROR: syntax error at or near "VALUE"
Renaming the value changes what the stored rows read as, and code that still uses the old label breaks:
ALTER TYPE ticket_state RENAME VALUE 'waiting_on_customer' TO 'pending';
SELECT * FROM tickets WHERE state = 'waiting_on_customer';
-- ERROR: invalid input value for enum ticket_state: "waiting_on_customer"
Removing a value means creating a new type, converting the column, which rewrites the table, and dropping the old type. If the set of states changes often, a text column with a CHECK constraint or a lookup table with a foreign key is easier to change in both directions.
What to rehearse#
Rehearse the release order, not the SQL. Write a row with the new value in staging, then run the previous application release against that database and exercise every path that reads the column, including list pages, exports, background jobs and analytics loaders. The rehearsal passes when the previous release still serves those paths.
Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.