Learning center

What a rollback loses after one value became many

When one column becomes a child table, such as one phone number becoming a list of numbers, the new release can store data the old shape cannot hold. A rollback to the old release shows only what the old column contains. In our lab, after three customers changed their numbers in the new release, a rollback would have shown two of them a number that was no longer their primary one and one of them no number at all. Write the primary value back into the old column while a rollback is possible, and count what is still lost.

The split#

customers.phone held one number. Release 2026.11.4 adds customer_phones, with any number of phones per customer and one marked primary, and backfills it. Then customers use the new feature. We ran this on PostgreSQL 16.15:

INSERT INTO customer_phones VALUES (1, '+49 170 1111', false);                        -- Ana adds a mobile
INSERT INTO customer_phones VALUES (2, '+49 171 2222', false);
UPDATE customer_phones SET is_primary = (phone = '+49 171 2222') WHERE customer_id = 2;   -- Ben switches primary
INSERT INTO customer_phones VALUES (3, '+49 172 3333', true);                         -- Cleo adds her first

What a rollback to 2026.11.3, which reads customers.phone, would show:

idnameold release showsprimary nowphones now
1Ana+49 30 1111+49 30 11112
2Ben+49 30 2222+49 171 22222
3Cleo(none)+49 172 33331

Ben's courier would call a number he replaced. Cleo would have no number at all. Ana's mobile number is invisible, which matters less because her primary is right.

Count what a rollback cannot represent#

SELECT count(*) FILTER (WHERE extra.n > 1) AS customers_with_more_phones,
       count(*) FILTER (WHERE c.phone IS DISTINCT FROM extra.primary_phone) AS customers_with_different_primary
FROM customers c
LEFT JOIN LATERAL (SELECT count(*) AS n, max(phone) FILTER (WHERE is_primary) AS primary_phone
                   FROM customer_phones p WHERE p.customer_id = c.id) extra ON true;
Customers with more phonesCustomers with a different primary
22

The first number is data the old shape can never hold. The second is data it could hold but does not, and that one can be fixed.

Write the primary back#

A trigger on the child table keeps the old column equal to the current primary:

CREATE FUNCTION primary_phone_back() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE cid bigint := COALESCE(NEW.customer_id, OLD.customer_id);
BEGIN
  UPDATE customers SET phone = (SELECT phone FROM customer_phones WHERE customer_id = cid AND is_primary)
  WHERE id = cid;
  RETURN NULL;
END $$;

CREATE TRIGGER customer_phones_back AFTER INSERT OR UPDATE OR DELETE ON customer_phones
  FOR EACH ROW EXECUTE FUNCTION primary_phone_back();

UPDATE customer_phones SET is_primary = is_primary;   -- bring existing rows in line

After it, the old release would show +49 30 1111, +49 171 2222 and +49 172 3333, each customer's current primary. The second count dropped to 0. The first stays at 2: the extra numbers exist only in the new table.

The trigger covers one direction. If the old release can run and write customers.phone, a trigger on customers must also update the primary row in customer_phones, as sync two columns both ways while the old and new releases write shows for two columns.

The rollback decision#

Before rolling back past the release that introduced the child table, read both counts. The second must be zero; the first is the number of customers whose extra data the old release will not show, and whose extra data its writes might contradict. Rolling back is acceptable when that number, and what those customers lose, is. The general rule for the last steps 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