Learning center

Add an optimistic lock version that the old release cannot bypass

A new version column protects only the writes that check and increment it. The release that is still running does neither, so its changes leave the version alone, and the new release's next save passes its check and overwrites them. In our lab, a phone number the old release saved was silently replaced. A BEFORE UPDATE trigger that increments the version on every update made the new release's save fail instead.

The lost update#

The new release adds a version column and saves a profile form with WHERE version = $loaded. We ran this on PostgreSQL 16.15:

ALTER TABLE profiles ADD COLUMN version bigint NOT NULL DEFAULT 1;

-- 2026.11.4 loads the profile: phone +49 30 1234, version 1
-- 2026.11.3 saves a phone change; it knows nothing about version
UPDATE profiles SET phone = '+49 30 9999' WHERE id = 1;

-- 2026.11.4 saves its form, built from what it loaded
UPDATE profiles SET email = 'ana@new.example', phone = '+49 30 1234', version = version + 1
WHERE id = 1 AND version = 1;
-- UPDATE 1
idemailphoneversion
1ana@new.example+49 30 12342

The new release's check passed, because the old release's update did not change the version. The phone number the customer had just changed went back to the old one. Neither release reported a problem, and the version history says the row changed once.

Make every update increment it#

CREATE FUNCTION bump_version() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.version := OLD.version + 1;
  RETURN NEW;
END $$;

CREATE TRIGGER profiles_version BEFORE UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION bump_version();

The same sequence, from version 1:

UPDATE profiles SET phone = '+49 30 9999' WHERE id = 1;           -- 2026.11.3: version becomes 2
UPDATE profiles SET email = 'ana@new.example', phone = '+49 30 1234', version = version + 1
WHERE id = 1 AND version = 1;
-- UPDATE 0
idemailphoneversion
1ana@example.com+49 30 99992

The new release's save matched no row, which is the conflict it was built to detect. It can reload and show the customer the current phone number. The trigger also overrides the new release's own version = version + 1, so the version moves by exactly one per update whoever writes.

When the trigger is not enough#

The old release still overwrites in the other direction. When it saves a form it loaded before a new-release save, nothing stops it, because it never checks the version. The trigger only makes the new release's checks see every write. If old-release overwrites matter for this table, keep the old release's update paths for it out of the rollout, for example by deploying the version check in a release whose predecessor already sends partial updates.

A rollback to the old release keeps the trigger running, so the versions stay meaningful when the new release returns.

The compatibility test#

In a test against the migrated schema, load a row with the new release, update it with the previous release's statement, then save with the new release. Pass when the save updates no row. The other mixed-release checks are in moving data while two application releases share one database.

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