To reject a stale document save, include the revision originally loaded by the editor in the update’s condition, then increment it atomically. SERIALIZABLE alone cannot express that requirement when the document was loaded in an earlier transaction.

This tutorial is for a developer working in Terminal on a Mac who needs a PostgreSQL article editor to reject outdated replacement text. It uses synthetic article rows and a complete node-postgres demonstration. The task is precise: save only when the stored revision still matches the revision on which the submitted draft was based.

Why can SERIALIZABLE allow an outdated replacement?

PostgreSQL defines serializable execution as producing an effect equivalent to running the committed transactions one at a time in some order. That guarantee concerns database transactions and their operations. PostgreSQL: Transaction Isolation.

Consider this illustrative sequence:

  1. An editor loads article 1 at revision 1, and the read transaction ends.
  2. Another save replaces the article text and commits revision 2.
  3. The original editor submits replacement text based on revision 1.
  4. A new serializable transaction updates article 1 using only its ID.

The last transaction can succeed: the transactions already ran sequentially. Nothing in an update conditioned only on id = 1 says that the submitted text requires revision 1. This is an application of the documented serializability definition, rather than an exception to it.

Overlapping transactions are different. PostgreSQL can reject a serializable transaction when a concurrent update conflicts with its snapshot or when dependencies would prevent serial execution. The snapshot is established by the first non-transaction-control statement; merely issuing BEGIN earlier does not preserve an earlier editor read. PostgreSQL: Transaction Isolation.

The diagnostic question is therefore: Where does the save explicitly require the revision that the editor loaded? If the answer is nowhere, changing the isolation level does not supply that missing condition.

What must the revision check guarantee?

Treat each loaded draft as a pair: its editable text and its base revision. The save carries the article ID, replacement text and unchanged base revision. The database update must match both the ID and that revision, replace the text, and increment the stored revision in the same statement.

For example, the condition WHERE id = $1 AND revision = $2 expresses the prerequisite, while SET body = $3, revision = revision + 1 performs the transition. PostgreSQL updates rows satisfying the condition, and RETURNING supplies values from rows actually updated. A zero-row update is a successful SQL command, so the application must interpret it explicitly. PostgreSQL: UPDATE.

This pattern is often called optimistic concurrency control or compare-and-swap. For this article’s design, every operation that changes editable article content must advance the revision. Keep revisions non-null, do not reset them, and do not reuse an article identity with an old revision token. Otherwise, equality could falsely suggest that an old draft still matches.

A separate read followed by an update using only the ID leaves the requirement out of the write itself. Likewise, fetching the newest revision just before saving and substituting it for the submitted revision changes the meaning of the request: it allows replacement of a version the editor did not use as its base.

Run a complete synthetic example on your Mac

Have a running PostgreSQL instance, an existing scratch database, and a current or supported LTS Node.js installation with npm. Open Terminal, create a working folder, and install the driver with the documented npm install pg command. node-postgres: Introduction and Install.

mkdir revision-write-demo
cd revision-write-demo
npm install pg

Save the following complete program as revision-demo.mjs in that folder. It uses the documented ESM-compatible default import. Both Client and Pool can read PostgreSQL connection settings from environment variables; configure those settings for your existing scratch database before running it. node-postgres: ESM, node-postgres: Connecting.

The program compares two synthetic articles. Article 1 receives two unconditional serializable saves. Article 2 receives two revision-checked saves using the same original revision. Its temporary table keeps the demonstration separate from permanent article tables. The JavaScript syntax was checked with Node.js; the database demonstration was not executed in the research environment.

import pg from 'pg';
import assert from 'node:assert/strict';
const { Client } = pg;
const client = new Client();

async function saveArticle(id, expectedRevision, body) {
  for (let attempt = 1; attempt <= 3; attempt += 1) {
    let active = false;
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      active = true;
      const result = await client.query(
        `UPDATE revision_demo_articles
         SET body = $3, revision = revision + 1
         WHERE id = $1 AND revision = $2
         RETURNING id, body, revision`,
        [id, expectedRevision, body]
      );
      if (result.rowCount === 0) {
        await client.query('ROLLBACK');
        active = false;
        return { status: 'stale-or-missing' };
      }
      await client.query('COMMIT');
      active = false;
      return { status: 'saved', article: result.rows[0] };
    } catch (error) {
      if (active) await client.query('ROLLBACK');
      if (error.code !== '40001' || attempt === 3) throw error;
    }
  }
}

await client.connect();
try {
  await client.query(`
    CREATE TEMP TABLE revision_demo_articles (
      id integer PRIMARY KEY,
      body text NOT NULL,
      revision integer NOT NULL CHECK (revision > 0)
    )
  `);
  await client.query(
    `INSERT INTO revision_demo_articles (id, body, revision)
     VALUES (1, 'Opening draft', 1), (2, 'Opening draft', 1)`
  );
  const loaded = await client.query(
    'SELECT id, body, revision FROM revision_demo_articles ORDER BY id'
  );
  const expectedRevision = loaded.rows[1].revision;

  // Illustrative blind saves: each is a separate transaction.
  for (const body of ['First edit', 'Stale replacement']) {
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
    try {
      await client.query(
        `UPDATE revision_demo_articles
         SET body = $1, revision = revision + 1 WHERE id = 1`,
        [body]
      );
      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    }
  }

  const first = await saveArticle(2, expectedRevision, 'First edit');
  const stale = await saveArticle(2, expectedRevision, 'Stale replacement');
  assert.equal(first.status, 'saved');
  assert.equal(first.article.revision, 2);
  assert.equal(stale.status, 'stale-or-missing');

  const final = await client.query(
    'SELECT id, body, revision FROM revision_demo_articles ORDER BY id'
  );
  assert.deepEqual(final.rows, [
    { id: 1, body: 'Stale replacement', revision: 3 },
    { id: 2, body: 'First edit', revision: 2 }
  ]);
  console.table(final.rows);
  console.log(stale.status);
} finally {
  await client.end();
}

If the existing scratch database is named revision_demo, run the command below. Substitute your actual database name and retain any connection environment settings your local instance requires. node-postgres: Connecting.

PGDATABASE=revision_demo node revision-demo.mjs

The expected final values are:

Article Body Revision Meaning
1 Stale replacement 3 Both unconditional saves committed
2 First edit 2 The second revision-checked save was rejected

The final status printed should be stale-or-missing.

The sequence deliberately avoids timing-dependent concurrency. It demonstrates the central boundary directly: an outdated replacement can succeed even when every save transaction is serializable, because the saves run in a valid serial order.

How should node-postgres handle each outcome?

The program passes values separately from SQL text using the driver’s documented parameterized-query API. It also checks rowCount, which reports how many rows an update processed; an empty rows array alone is not a general replacement for checking update counts. node-postgres: Queries, node-postgres: pg.Result.

Use the following decision table when adapting the function:

Outcome Application action
One updated row and successful commit Accept the save and retain the returned revision
Zero updated rows Reject this save as stale or missing; preserve the submitted draft
SQLSTATE 40001 Roll back and retry the complete transaction, subject to a bounded policy
Other error, or retry limit reached Surface the failure; do not report a successful save

A zero-row result does not establish whether the article was deleted or its revision changed. The demonstration intentionally combines those outcomes. If the interface needs more detail, load the current article afterward and present that result as a later observation. Do not describe it as proof of the precise state at the failed update.

Serialization failures use SQLSTATE 40001. PostgreSQL requires retrying the complete transaction, including the logic deciding statements and values. Retrying can require multiple attempts and does not guarantee eventual completion. The example uses three attempts as an illustrative application policy. PostgreSQL: Serialization Failure Handling.

Keep expectedRevision unchanged across those attempts. It represents the submitted draft’s prerequisite. If another save advanced the revision, a fresh transaction can then reject the old token with zero updated rows. Automatically replacing the token with the newest revision would remove the stale-write protection.

The function returns success only after COMMIT completes. All transaction statements use the same Client, as node-postgres requires. When adapting this to a pool, acquire a client with pool.connect(), use that client throughout the transaction, and release it afterward. node-postgres: Transactions, node-postgres: pg.Pool.

Do you still need SERIALIZABLE or SELECT FOR UPDATE?

For this narrowly scoped, single-row conditional update, Read Committed can enforce the revision prerequisite too: after waiting for a concurrent updater that commits, PostgreSQL re-evaluates the update’s condition against the updated row. That does not extend Read Committed into a guarantee for complex multi-row rules. PostgreSQL: Transaction Isolation.

Keep SERIALIZABLE when the surrounding transaction depends on broader reads and writes that require its guarantees. The revision condition expresses the editor’s prerequisite; isolation governs interactions among the transaction’s database operations. Choose both according to the actual task.

SELECT FOR UPDATE can support a transaction that needs to inspect a row and perform several dependent operations. Its row lock blocks conflicting writers until the transaction ends. Under Repeatable Read or Serializable, locking a row changed since the transaction’s snapshot can produce an error. PostgreSQL: Explicit Locking.

Acquiring that lock only when a save arrives still leaves the application responsible for comparing the submitted base revision. Locking the current row cannot tell the database which earlier revision produced the draft. For the one-row replacement shown here, the conditional update already combines the comparison and change.

Before integrating the pattern, check every content-writing path, preserve the editor’s draft on rejection, and update the editor’s base revision only after a confirmed save. When a stale save is rejected, offer the current article alongside the preserved draft so the reader can reconcile the text and submit a new save based on the revision actually reviewed.

Sources