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
| id | phone | version | |
|---|---|---|---|
| 1 | ana@new.example | +49 30 1234 | 2 |
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
| id | phone | version | |
|---|---|---|---|
| 1 | ana@example.com | +49 30 9999 | 2 |
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.