Upserts skip your optimistic lock unless they bump it
A version column protects the writes that go through your update path. The usual upsert updates an existing row through its own conflict branch, which sets the data columns and leaves the version where it was. Then a copy someone loaded before the upsert still passes the version check, and it writes over the upsert without an error.
The fix is one more assignment in that conflict branch. The test for it is at the end.
How the version check works
Optimistic locking works off a version column on each row. You read it with the row, and when you save, you only update the row if the version is still the one you read, and you bump it in the same statement:
update doc set title = $1, version = version + 1
where id = $2 and version = $3;
Zero rows updated means someone else saved in between. Your code throws a conflict and the user reloads.
Where the upsert slips through
Here’s the usual Postgres upsert. It merges the data columns and nothing else:
insert into doc (id, title) values (1, 'd2')
on conflict (id) do update set title = excluded.title
returning title, version;
I ran the whole sequence against Postgres 16. Request A loads row 1 at version 1. Request B runs the upsert above. Then request A saves its stale copy with the normal check:
-- after B's upsert
title | version
-------+---------
d2 | 1
-- A's update ... where id = 1 and version = 1
title | version
-----------+---------
d1-edited | 2
A’s write went through. B’s title is gone and no error was raised, because B changed the row without touching the version.
MikroORM had exactly this. Its em.upsert() merged only the data columns on conflict until a fix merged on 2026-11-07, so a copy loaded before the upsert flushed over it without an OptimisticLockError. If your ORM has an upsert and a version property, check whether its upsert bumps the version.
The fix
Bump the version in the conflict branch, the same way your update does:
insert into doc (id, title) values (1, 'd2')
on conflict (id) do update
set title = excluded.title, version = doc.version + 1;
Two gotchas.
First, the table name in doc.version matters. Inside do update, Postgres has both the existing row and the excluded row in scope, so a bare version + 1 fails:
ERROR: column reference "version" is ambiguous
Second, only the update branch gets the bump. A fresh insert starts at the column default, which is what you want.
With the fix, I ran the sequence again. B’s upsert moves the row to version 2, and A’s save finds no row at version 1 and updates nothing.
The test that catches it
Here’s the test. It runs on Node’s built-in test runner and pg against a real Postgres:
import { test, after } from "node:test";
import assert from "node:assert/strict";
import pg from "pg";
const db = new pg.Client(process.env.DATABASE_URL);
await db.connect();
after(() => db.end());
// The repository code under test. Swap in your ORM's upsert to check it.
async function upsertDoc(id, title) {
await db.query(
`insert into doc (id, title) values ($1, $2)
on conflict (id) do update set title = excluded.title, version = doc.version + 1`,
[id, title],
);
}
async function saveWithLock(row) {
const res = await db.query(
`update doc set title = $1, version = version + 1
where id = $2 and version = $3`,
[row.title, row.id, row.version],
);
if (res.rowCount === 0) throw new Error("stale write");
}
test("an upsert invalidates copies loaded before it", async () => {
await db.query("drop table if exists doc");
await db.query("create table doc (id int primary key, title text not null, version int not null default 1)");
await db.query("insert into doc (id, title) values (1, 'first')");
const { rows: [stale] } = await db.query("select * from doc where id = 1");
await upsertDoc(1, "from the upsert");
await assert.rejects(saveWithLock({ ...stale, title: "from the stale copy" }), /stale write/);
});
Remove the version = doc.version + 1 part and it fails with Missing expected rejection. Put it back and it passes. Copy it for each upsert on a versioned table.
Put the db.end() in after(), not at the end of the test. The first version of this test I wrote ended the client inside the test, so a failing assertion skipped it and the run hung on the open connection.
Other write paths to check
Any write that skips your update method has the same problem:
- bulk updates (
update ... where status = 'pending') issued straight to the database mergestatements and other “insert or update” helpers- raw SQL in a migration or a backfill script that runs while the app is live