To keep an article list and its count consistent, run both reads in one REPEATABLE READ READ ONLY transaction on the same node-postgres client. This tutorial shows the pattern and a two-session check you can run from a Mac.

Why can an article list disagree with its count?

Suppose a desktop script fetches published articles in the craft topic, then counts matching articles. Between those reads, another session changes an article’s topic. The returned list can contain two articles while the count says one.

PostgreSQL’s default isolation level, READ COMMITTED, gives each ordinary SELECT a snapshot at that statement’s start. Successive reads can therefore see different committed data even inside one transaction. PostgreSQL: Transaction Isolation.

For this task, the desired result is one coherent view of the article rows. A later edit can appear on the next run. PostgreSQL’s REPEATABLE READ keeps successive reads on the snapshot established by the transaction’s first non-transaction-control statement; merely executing BEGIN does not establish that snapshot. PostgreSQL: Transaction Isolation.

The implementation has two requirements: select the isolation level before reading, and keep every related query on the same client. node-postgres explicitly requires one client for a transaction and warns against using pool.query() for its statements. node-postgres: Transactions.

Prepare a small article fixture on your Mac

Use a running local PostgreSQL server, the psql command, and a supported Node.js installation with npm. The commands below assume your existing local connection works with the postgres database. If your working database has another name, substitute it consistently. PostgreSQL installation and connection setup are prerequisites.

Open Terminal and create a project folder. The documented node-postgres package is pg. node-postgres: Welcome.

mkdir article-snapshot-demo
cd article-snapshot-demo
npm init -y
npm install pg
export PGDATABASE=postgres
psql -d postgres

At the psql prompt, run this setup once. It creates a dedicated table containing only synthetic article rows. The explicit identifiers also make the later exercise easy to follow.

CREATE TABLE public.article_snapshot_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    topic text NOT NULL,
    published boolean NOT NULL
);

INSERT INTO public.article_snapshot_demo
    (id, title, topic, published)
VALUES
    (1, 'Paper Shapes', 'craft', true),
    (2, 'Color Studies', 'craft', true),
    (3, 'Desk Layouts', 'design', true);

If the table already exists, stop and inspect the previous fixture rather than deleting or replacing it automatically. The examples assume exactly these three starting rows. Leave psql with \q before running the Node.js script.

Read the list and count through one checked-out client

Save the following complete program as read-articles.mjs in the project folder. new Pool() uses the documented PostgreSQL connection environment variables, including the PGDATABASE value exported above. node-postgres: Connecting.

The program acquires one client, starts the transaction with its characteristics already specified, performs both reads, and commits before returning the assembled result. PostgreSQL supports these characteristics directly in BEGIN; the transaction ends with COMMIT or ROLLBACK. PostgreSQL: BEGIN.

import pg from 'pg';
const { Pool } = pg;

const pool = new Pool({ connectionTimeoutMillis: 5000 });
pool.on('error', (error) => {
  console.error('Idle database client error:', error.message);
  process.exitCode = 1;
});

async function readArticles(topic) {
  const client = await pool.connect();
  let began = false;
  let discardClient = false;

  try {
    await client.query(
      'BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY'
    );
    began = true;

    const articles = await client.query(
      `SELECT id, title, topic
       FROM public.article_snapshot_demo
       WHERE topic = $1 AND published = true
       ORDER BY id`,
      [topic]
    );

    const count = await client.query(
      `SELECT count(*)::text AS total
       FROM public.article_snapshot_demo
       WHERE topic = $1 AND published = true`,
      [topic]
    );

    await client.query('COMMIT');
    began = false;

    return {
      topic,
      articles: articles.rows,
      total: count.rows[0].total
    };
  } catch (error) {
    if (began) {
      try {
        await client.query('ROLLBACK');
      } catch (rollbackError) {
        discardClient = true;
        throw new AggregateError(
          [error, rollbackError],
          'Article read failed; rollback also failed'
        );
      }
    } else {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  const result = await readArticles('craft');
  console.log(JSON.stringify(result, null, 2));
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

Run the program from the same Terminal session:

node read-articles.mjs

With the unchanged fixture, the expected result contains articles 1 and 2, with total equal to the string "2". The count is explicitly returned as text, so this example does not require conversion to a JavaScript number. Both queries use the same topic and publication filter; isolation cannot correct mismatched predicates.

The cleanup is part of the usable pattern. A failed read triggers rollback, and a failed rollback causes the client to be discarded. node-postgres documents client.release(true) for destroying a checked-out client, and pool.end() for shutting down a script’s pool. node-postgres: Pool API.

This is a standalone script, so it ends its pool after one run. In an existing application, keep the application’s pool lifecycle and place the transaction function within it. Every helper participating in these reads must receive the checked-out client; a helper that quietly calls pool.query() breaks that boundary. node-postgres: Transactions.

Verify the snapshot with two Terminal sessions

A fast script may finish before another session changes anything. Use the following deliberately ordered exercise to make the relevant timing explicit. These are session-specific SQL steps using the table already created, rather than independent setup programs. The expected database results follow the documented isolation behavior; they are not presented as an executed PostgreSQL test.

Open two Terminal windows and run psql -d postgres in each. Before starting the reader, restore the synthetic fixture’s topic in session B. Wait for this statement to finish:

-- Session B: restore the fixture before beginning the exercise.
UPDATE public.article_snapshot_demo
SET topic = 'craft'
WHERE id = 2;

In session A, start the reader transaction and fetch the list. Pause after the query completes:

-- Session A: run these statements, then pause.
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;

SELECT id, title
FROM public.article_snapshot_demo
WHERE topic = 'craft' AND published = true
ORDER BY id;

The list should contain Paper Shapes and Color Studies. Now switch to session B and commit a topic change using this standalone statement. Outside an explicit transaction block, PostgreSQL automatically commits a successful statement. PostgreSQL: BEGIN.

-- Session B: run while session A remains open.
UPDATE public.article_snapshot_demo
SET topic = 'design'
WHERE id = 2;

Return to session A and finish the related read:

-- Session A: finish the original reader transaction.
SELECT count(*)::text AS total
FROM public.article_snapshot_demo
WHERE topic = 'craft' AND published = true;

COMMIT;

The expected count is 2, matching session A’s earlier list. Afterward, a fresh run of the Node.js script should return only Paper Shapes and a total of "1". The old result and the new result each represent a coherent view.

For a controlled comparison, repeat the entire exercise, including session B’s initial restoration, but start session A with BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY;. Keep the same pause and update order. The expected first list contains two articles, while the later count is 1. This comparison isolates the transaction setting as the changed condition.

Choose the right boundary for related reads

Use this decision aid when adapting the example. First decide which outputs must agree, then choose the smallest database operation that supplies them.

Reader task Suitable approach Condition to preserve
Return one article list from one ordinary query One query can use READ COMMITTED No later query needs to share its snapshot.
Return a list and a separately queried count One REPEATABLE READ READ ONLY transaction Use one client and matching filters.
Fetch multiple batches for one coherent result Keep the reads within the same transaction A new transaction establishes a new view.
Coordinate reads across separate database sessions Explicit snapshot export and import Independent transactions do not automatically share a snapshot.

For multiple sessions, PostgreSQL provides pg_export_snapshot() and SET TRANSACTION SNAPSHOT. The exporter must remain open until the other transactions import its snapshot. An importing transaction must use REPEATABLE READ or SERIALIZABLE and import before its first query or data-modification statement. Serializable import has additional restrictions documented in the command reference. PostgreSQL: Snapshot Synchronization Functions, PostgreSQL: SET TRANSACTION.

For this desktop list-and-count task, retaining one client avoids that extra coordination. Finish the database reads and close the transaction before waiting for a person to choose another action.

What does this isolation level leave unresolved?

PostgreSQL’s REPEATABLE READ prevents phantom reads, but serialization anomalies remain possible. A stable snapshot therefore does not establish that concurrent workflows behaved as if executed one at a time. It also does not freeze sequence values; sequences have special transactional behavior. PostgreSQL: Transaction Isolation.

READ ONLY disallows changes to ordinary tables, with documented exceptions such as certain temporary-table operations. It does not itself choose a stable snapshot. Set both characteristics before reading: PostgreSQL will not let you change the isolation level after the transaction’s first query or data-modification statement. PostgreSQL: SET TRANSACTION.

When a report requires stronger serializable semantics, PostgreSQL supports SERIALIZABLE READ ONLY DEFERRABLE. It may wait while obtaining its snapshot, then runs without serialization-failure cancellation. That tradeoff is distinct from the ordinary article-read requirement demonstrated here. PostgreSQL: SET TRANSACTION.

For the article list and count, keep the implementation boundary explicit: acquire one client, begin with the required characteristics, read all related results, commit, and release the client. Reuse the same filters and publish the assembled result only after the transaction completes. That gives the next desktop action one coherent set of article data to work with.

Sources