PostgreSQL does not create a nested transaction when you send a second BEGIN. It warns and keeps the current transaction; the first successful COMMIT ends it. Use savepoints when a helper needs partial rollback. PostgreSQL: BEGIN

This guide helps a developer working in Mac Terminal diagnose a helper that commits an article-editing operation too early. The examples use synthetic article rows and show how to keep one transaction owner while allowing a helper to undo its own batch. They follow the current PostgreSQL and node-postgres documentation.

What happens when BEGIN and COMMIT appear inside another transaction?

SQL transaction commands operate on the connection’s current transaction. PostgreSQL does not count repeated BEGIN calls and wait for matching COMMIT calls. A second BEGIN leaves the transaction unchanged; a successful plain COMMIT commits that current transaction. Issuing another plain COMMIT afterward produces a warning because no transaction is open. PostgreSQL: BEGIN, PostgreSQL: COMMIT

For a healthy transaction, the boundary trace is:

Command sent on one connection Resulting boundary
First BEGIN Opens the transaction
First article insert Belongs to that transaction
Second BEGIN Warns; creates no inner boundary
Second article insert Belongs to the same transaction
First COMMIT Commits both inserts and ends the transaction
Another statement sent separately Runs in its own transaction under normal autocommit behavior
Later ROLLBACK Cannot undo the already committed inserts

This explains a common application failure: the caller opens a transaction, then invokes a helper that sends its own BEGIN and COMMIT. The helper’s COMMIT ends the caller’s transaction too. A later exception in the caller cannot restore the original all-or-nothing operation. That conclusion follows from PostgreSQL’s documented transaction boundaries. PostgreSQL: Transactions

Reproduce the early commit in Mac Terminal

You need a running PostgreSQL database and an installed psql command with working connection settings. Open Terminal and start a fresh session with psql -X. The -X option skips startup files, making this demonstration less dependent on local customizations. Run the following script in that session; it explicitly enables autocommit. PostgreSQL: psql

The table is temporary and contains only synthetic article titles. ON COMMIT PRESERVE ROWS keeps its rows available for inspection across transaction boundaries; PostgreSQL drops the temporary table when the session ends. This demonstrates rollback behavior within the session, rather than persistent storage after disconnecting. PostgreSQL: CREATE TABLE

\set AUTOCOMMIT on

CREATE TEMP TABLE article_boundary_demo (
    id integer PRIMARY KEY,
    title text NOT NULL
) ON COMMIT PRESERVE ROWS;

BEGIN;
INSERT INTO article_boundary_demo VALUES (1, 'Opening draft');

-- Deliberately incorrect attempt to open an inner transaction.
BEGIN;
INSERT INTO article_boundary_demo VALUES (2, 'Middle draft');
COMMIT;

-- The previous COMMIT has already ended the transaction.
INSERT INTO article_boundary_demo VALUES (3, 'Closing draft');
ROLLBACK;

SELECT id, title FROM article_boundary_demo ORDER BY id;

The expected result contains all three rows. The second BEGIN warns that a transaction is already in progress. The COMMIT commits rows 1 and 2; row 3 then commits through autocommit. The final ROLLBACK has no open transaction to undo. These are expected results derived from the documented behavior, not a reported database execution. PostgreSQL: BEGIN, PostgreSQL: Transactions

Keep this reproduction in interactive psql, as shown. Client submission matters: several SQL statements passed together through one psql -c string can share an implicit transaction unless explicit transaction commands divide them. The documentation recommends separate commands or standard input when separate execution is intended. PostgreSQL: psql

How do savepoints preserve the outer transaction?

A savepoint marks a position inside an existing transaction. ROLLBACK TO SAVEPOINT discards changes after that position while preserving earlier work. RELEASE SAVEPOINT removes the marker and retains the surviving changes within the containing transaction. It does not independently commit those changes. PostgreSQL: SAVEPOINT, PostgreSQL: RELEASE SAVEPOINT

Run this complete example in the same session or another fresh psql session. It creates its own temporary table:

CREATE TEMP TABLE article_savepoint_demo (
    id integer PRIMARY KEY,
    title text NOT NULL
) ON COMMIT PRESERVE ROWS;

BEGIN;
INSERT INTO article_savepoint_demo VALUES (1, 'Opening draft');

SAVEPOINT optional_article;
INSERT INTO article_savepoint_demo VALUES (2, 'Optional draft');
ROLLBACK TO SAVEPOINT optional_article;
RELEASE SAVEPOINT optional_article;

INSERT INTO article_savepoint_demo VALUES (3, 'Closing draft');
COMMIT;

SELECT id, title FROM article_savepoint_demo ORDER BY id;

The expected rows are 1 and 3. Row 2 is discarded at the savepoint, while the opening and closing rows remain part of one transaction until its final COMMIT. Replacing that final COMMIT with ROLLBACK would discard both surviving inserts. PostgreSQL: SAVEPOINT, PostgreSQL: Transactions

Savepoint lifetime also matters when composing helpers. Rolling back to a savepoint leaves that named savepoint available, but destroys later savepoints. Releasing a savepoint removes it and any active savepoints created after it. The example explicitly releases its marker after recovery. PostgreSQL: ROLLBACK TO SAVEPOINT, PostgreSQL: RELEASE SAVEPOINT

How should node-postgres helpers share the transaction?

node-postgres requires every statement in a transaction to use the same client instance, and it provides no automatic transaction nesting abstraction. Pass the caller’s client into helpers. If the application uses a pool, acquire a client with pool.connect(), use that client throughout, and release it afterward; pool.query() can dispatch successive queries to different clients. node-postgres: Transactions, node-postgres: Pool

The following complete program uses one direct Client. Its caller owns BEGIN, COMMIT, and full ROLLBACK. The helper owns a savepoint around an optional two-row batch. Its second insert deliberately repeats article ID 1, causing a uniqueness error; recovery discards the entire optional batch, including its earlier successful insert.

Set up and run the complete example

Use a current supported Node.js version with npm installed. In a working folder, run npm install pg, then save the program below as article-transactions.mjs. It uses your existing PostgreSQL connection environment, including PGHOST, PGPORT, PGDATABASE, and PGUSER where configured. Those settings must identify a working database before running the example. node-postgres: Welcome, node-postgres: Connecting

import pg from 'pg'
const { Client } = pg

async function addOptionalArticles(client) {
  await client.query('SAVEPOINT optional_articles')
  try {
    await client.query(
      'INSERT INTO article_demo (id, title) VALUES ($1, $2)',
      [2, 'Optional draft']
    )
    await client.query(
      'INSERT INTO article_demo (id, title) VALUES ($1, $2)',
      [1, 'Duplicate draft']
    )
  } catch (error) {
    if (error.code !== '23505') throw error
    await client.query('ROLLBACK TO SAVEPOINT optional_articles')
    await client.query('RELEASE SAVEPOINT optional_articles')
    return false
  }
  await client.query('RELEASE SAVEPOINT optional_articles')
  return true
}

async function main() {
  const client = new Client({ connectionTimeoutMillis: 5000 })
  await client.connect()
  try {
    await client.query(`
      CREATE TEMP TABLE article_demo (
        id integer PRIMARY KEY,
        title text NOT NULL
      ) ON COMMIT PRESERVE ROWS
    `)
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED')
    try {
      await client.query(
        'INSERT INTO article_demo (id, title) VALUES ($1, $2)',
        [1, 'Opening draft']
      )
      const optionalKept = await addOptionalArticles(client)
      console.log('Optional batch kept:', optionalKept)
      await client.query(
        'INSERT INTO article_demo (id, title) VALUES ($1, $2)',
        [3, 'Closing draft']
      )
      await client.query('COMMIT')
    } catch (error) {
      try {
        await client.query('ROLLBACK')
      } catch (rollbackError) {
        throw new AggregateError(
          [error, rollbackError], 'Transaction and rollback failed'
        )
      }
      throw error
    }
    const result = await client.query(
      'SELECT id, title FROM article_demo ORDER BY id'
    )
    console.table(result.rows)
  } finally {
    await client.end()
  }
}

main().catch(error => {
  console.error(error)
  process.exitCode = 1
})

Run node article-transactions.mjs. The expected output reports Optional batch kept: false and displays article IDs 1 and 3. Values use the documented query(text, values) parameter interface. The JavaScript was syntax-checked with Node.js; the database scenario was inspected against the documentation but was not executed here. node-postgres: Queries

Recover only from an explicitly accepted failure

PostgreSQL assigns 23505 to unique_violation and recommends checking error codes instead of message text. Here, accepting that code is an illustrative rule for the optional synthetic batch. Other errors propagate to the caller, which attempts to roll back the whole transaction. The helper returns an explicit result so discarded optional work is visible to the caller. PostgreSQL: PostgreSQL Error Codes

Catching a JavaScript exception alone does not restore a failed PostgreSQL transaction. Recovery requires rollback to a previously established savepoint or a full rollback. In particular, an aborted transaction cannot recover merely by releasing its savepoint. PostgreSQL: Transactions, PostgreSQL: RELEASE SAVEPOINT

The helper assumes an active transaction and sequential use of this client. It is not a general recursive transaction wrapper: a reusable nesting abstraction would also need explicit ownership and savepoint-name management.

What isolation limits remain after fixing the boundaries?

Savepoints provide partial rollback within the current transaction; they do not establish a separate isolation policy. Choose the isolation level at the outer boundary. PostgreSQL disallows changing it after the transaction’s first query or data-modification statement, so a helper cannot upgrade an already-used transaction by sending another BEGIN. PostgreSQL: SAVEPOINT, PostgreSQL: SET TRANSACTION

For ordinary queries, retain these distinctions:

Isolation level Relevant limit
READ COMMITTED Successive queries can see newly committed changes from other transactions. One transaction does not imply one fixed snapshot.
REPEATABLE READ Queries share the snapshot established by the first non-transaction-control statement, while seeing their own earlier changes. Serialization failures remain possible.
SERIALIZABLE Successfully committed transactions have an effect consistent with some serial order. Applications must still handle serialization failures.
READ UNCOMMITTED PostgreSQL treats it as READ COMMITTED.

PostgreSQL: Transaction Isolation, PostgreSQL: SET TRANSACTION

A serialization failure requires retrying the complete transaction, including the decisions that produced its SQL. Retrying only the helper’s savepoint work does not refresh the outer transaction’s snapshot. PostgreSQL identifies serialization failures with SQLSTATE 40001. The demonstration program propagates these failures; it does not implement automatic retries. PostgreSQL: Transaction Isolation

How do you choose the right helper boundary?

Use this decision sequence when reviewing a caller and its article helpers:

  1. Everything must succeed together: let the caller own the transaction and pass its client to helpers that only execute queries.
  2. One batch may be discarded while the caller continues: establish a savepoint before that batch, recover only from accepted failures, and return the outcome explicitly.
  3. The batch must commit independently: use a separate client and transaction. Its commit then survives an outer rollback, so it cannot satisfy the original all-or-nothing task.
  4. A concurrent execution produces a serialization failure: restart the complete transaction rather than treating the helper as an independent retry boundary.

These choices follow PostgreSQL’s savepoint semantics and node-postgres’s requirement that a transaction stay on one client. PostgreSQL: SAVEPOINT, node-postgres: Transactions, PostgreSQL: Transaction Isolation

For the article-editing task shown here, keep one transaction owner, pass one client through the call chain, and give optional work a savepoint. Review every helper’s transaction commands: an unexpected COMMIT changes the boundary for the entire connection, while a released savepoint still leaves the caller responsible for the final outcome.

Sources