---
title: "Sync two columns both ways while the old and new releases write | Proofline"
description: "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."
url: https://proofline.sh/learn/postgres-two-way-sync-trigger-conflict/
---

[Learning center](/learn/)

# 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.

Schema conflicts The Proofline team November 22, 2026 3 min read

## 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](https://www.postgresql.org/docs/16/trigger-definition.html) 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](/learn/postgres-sync-trigger-old-writer/), and the decision to stop syncing is in [decide when to stop writing the old column](/learn/when-to-stop-dual-writing/).

## 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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Use a trigger when the old release must keep filling the new column](/learn/postgres-sync-trigger-old-writer/)
    
    Schema conflicts The Proofline team November 22, 2026 3 min read
    
-   ### [Decide when to stop writing the old column](/learn/when-to-stop-dual-writing/)
    
    Schema conflicts The Proofline team November 22, 2026 3 min read
