The trigger#
Release 2026.11.3 writes status. Release 2026.11.5 writes only state, which has values the old column cannot express, such as CANCELLED_BY_CUSTOMER. During the rollout both run. The trigger compares the row before and after the update to find out which column the writer changed. We ran this on PostgreSQL 16.15:
CREATE FUNCTION orders_sync() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
status_changed boolean := NEW.status IS DISTINCT FROM OLD.status;
state_changed boolean := NEW.state IS DISTINCT FROM OLD.state;
BEGIN
IF status_changed AND state_changed THEN
NULL; -- the writer set both: keep what it wrote
ELSIF status_changed THEN
NEW.state := upper(NEW.status); -- 2026.11.3 wrote status
ELSIF state_changed THEN
NEW.status := CASE NEW.state -- 2026.11.5 wrote state
WHEN 'CANCELLED_BY_CUSTOMER' THEN 'cancelled'
WHEN 'CANCELLED_BY_MERCHANT' THEN 'cancelled'
ELSE lower(NEW.state) END;
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER orders_sync BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_sync();
A BEFORE trigger that changes NEW does not fire itself again, so the two directions do not loop. The trigger behavior chapter describes how the returned row replaces the one being written.
Three writers#
UPDATE orders SET state = 'CANCELLED_BY_CUSTOMER' WHERE id = 1; -- 2026.11.5
UPDATE orders SET status = 'shipped' WHERE id = 2; -- 2026.11.3
UPDATE orders SET status = 'shipped', state = 'PAID' WHERE id = 3; -- sets both; state already was PAID
| id | status | state |
|---|---|---|
| 1 | cancelled | CANCELLED_BY_CUSTOMER |
| 2 | shipped | SHIPPED |
| 3 | shipped | SHIPPED |
Orders 1 and 2 are what each release meant. Order 3 shows the limit of comparing values. The writer named both columns, but state was already PAID, so the trigger saw only status change and overwrote state. A trigger cannot see the SET list, only the values. If a writer can set both columns to disagreeing values, the column whose value did not change loses. That is acceptable only if you decide in advance which column wins; here, the old one does.
What the rollback loses#
We rolled back to 2026.11.3. Support reopened order 1 and cancelled it again, which in the old release means two writes to status:
UPDATE orders SET status = 'paid' WHERE id = 1;
UPDATE orders SET status = 'cancelled' WHERE id = 1;
| id | status | state |
|---|---|---|
| 1 | cancelled | CANCELLED |
The trigger did its job, and the information is still gone. The old column has one word for three new values, so every old-release write of cancelled turns into the most general new value. Rolling forward again does not bring CANCELLED_BY_CUSTOMER back.
Before you rely on it#
Count the rows whose new value has no exact old equivalent. That is how many rows an old-release write can flatten:
SELECT state, count(*) FROM orders
WHERE state NOT IN (SELECT upper(s) FROM unnest(ARRAY['paid','shipped','cancelled']) AS s)
GROUP BY state;
If that number matters, keep the rollback target on a release that reads and writes state, or have the old release refuse writes to those rows. The one-way trigger that comes before this one is in use a trigger when the old release must keep filling the new column, and the decision to stop syncing is in decide when to stop writing the old column.