Learning center

Sync two columns both ways while the old and new releases write

Once the new release writes only the new column, the old column needs a trigger that fills it backwards, or a rollback reads stale values. A two-way trigger has to decide which column a writer meant, and it can only compare values. In our lab it lost a value the new column could hold and the old one could not: after a rollback, a customer cancellation came back as a plain CANCELLED.

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
idstatusstate
1cancelledCANCELLED_BY_CUSTOMER
2shippedSHIPPED
3shippedSHIPPED

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;
idstatusstate
1cancelledCANCELLED

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.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading