Transactions

transaction() and its seven shortcuts, one per isolation level: the SQL they send, their retries and the types of the client they give. Then the errors: isDatabaseError() for those from Postgres, and those pgcast throws before any query.

At a glance

• Only the queries you .run(txnClient) are in the transaction; one run on the pool is not.
• On 40001 or 40P01, the whole callback runs again, five attempts in all by default: write it so it can.
• A query error that the callback catches turns COMMIT into ROLLBACK, and nothing is saved: let the error out.

transaction()

db.transaction(txnClientOrQueryable, isolationLevel, callback)

Runs callback in one transaction, then commits it, retries it or rolls it back. It returns a promise of what callback resolves to.

• txnClientOrQueryable: a pg.Pool, from which it checks out a client, released at the end; a client, pg.Client, pg.native.Client or a pg.PoolClient already checked out, used and left connected; or the client of a transaction already open, passed through.
• isolationLevel: an IsolationLevel.
• callback: async txnClient => …, given a TxnClient of that level. The queries of the transaction are those .run(txnClient); one run on the pool, or on any other client, is not in it.

db.serializable(pool, callback) is db.transaction(pool, db.IsolationLevel.Serializable, callback). Two customers book seat F7 of screening 1000 at once:

const bookSeat = (seat: string, customer: string) =>
  db.serializable(pool, async txnClient => {
    const taken = await db.selectOne('seat_bookings', { screening_id: 1000, seat }).run(txnClient);
    if (taken) return false;
    await db.insert('seat_bookings', { screening_id: 1000, seat, customer }).run(txnClient);
    return true;
  });

const [ines, barnaby] = await Promise.all([
  bookSeat('F7', 'Ines Calloway'),
  bookSeat('F7', 'Barnaby Quill'),
]);

One of ines and barnaby is true, the other false. When both read before either commits, Postgres refuses one of the two INSERTs with 40001, and pgcast runs that callback again, which finds the seat taken. That transaction sends:

-- attempt 1
START TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- the callback's selectOne(), then insert(), which Postgres refuses with 40001
ROLLBACK
-- attempt 2, 25 to 250 ms later
START TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- the callback's selectOne(), which returns the other booking
COMMIT

The SQL of the callback's queries: selectOne() and insert().

Given a client you checked out, transaction() runs on it and leaves it connected; releasing it is yours:

const client = await pool.connect();
let before: number, after: number;
try {
  before = await db.count('seat_bookings', { screening_id: 1000 }).run(client);
  await db.readCommitted(client, txnClient =>
    db.insert('seat_bookings', { screening_id: 1000, seat: 'G2', customer: 'Tamsin Rowe' }).run(txnClient));
  after = await db.count('seat_bookings', { screening_id: 1000 }).run(client);
} finally {
  client.release();
}

The two count()s run on client, outside the transaction: after the block, before is 0, after 1.

The SQL it sends

When	SQL
before the callback	START TRANSACTION ISOLATION LEVEL SERIALIZABLE: the level's value, written as it is
the callback resolves	COMMIT
START TRANSACTION, the callback or COMMIT rejects	ROLLBACK, then a retry or the rejection

• These three run on the transaction's client, as the callback's queries do. Each reaches queryListener and resultListener with the transaction's id: a counter from 0, one per transaction opened, kept across retries; a transaction passed through keeps the outer one's.
• No SAVEPOINT: a transaction inside another is the outer one (Passing a transaction through).
• After a query error that the callback catches, Postgres answers the COMMIT with ROLLBACK, not an error, and nothing is saved. transaction() resolves, as in Zapatos 6.6.1, and warns once (PGCAST_DEP003); with transactionRequiresCommit, it rejects.

Retries

When START TRANSACTION, the callback or COMMIT rejects with a code of 40001 (serialization_failure) or 40P01 (deadlock_detected), at any level, transaction() sends ROLLBACK, waits, then starts again: START TRANSACTION, the whole callback, COMMIT.

On 40001 or 40P01, pgcast rolls back, waits and runs the whole attempt again; on any other error, it rolls back and rejects.

Config option	Default	What it sets
transactionAttemptsMax	5	the attempts in all, the first included; 0 or 1 gives one
transactionRetryDelay	{ minMs: 25, maxMs: 250 }	the wait before each retry: a whole number of ms, at random between the two

• After the last attempt, it rejects with that attempt's error.
• Any other rejection: ROLLBACK, then it rejects with it, at once.
• A COMMIT that Postgres answers with ROLLBACK is not retried: pgcast doesn't know which error the callback caught.
• transactionListener gets each message with the transaction's id:

Message	When
Transaction rollback (code ${err.code}) on attempt ${attempt} of ${maxAttempts}, retrying in ${delayBeforeRetry}ms	before the wait
Retrying transaction, attempt ${attempt} of ${maxAttempts}	before START TRANSACTION again
Transaction rollback (code ${err.code}) on attempt ${attempt} of ${maxAttempts}, giving up	before it rejects

The callback may run several times: Retries.

Passing a transaction through

Given the client of a transaction that transaction() has open, it sends nothing: it calls the callback with that client, at once, and returns its promise. No START TRANSACTION, COMMIT, ROLLBACK nor retry of its own; the outer transaction does them. So a function can run in its own transaction, or in the caller's:

const bookSeat = (seat: string, customer: string, txnClientOrPool: typeof pool | db.TxnClientForSerializable) =>
  db.serializable(txnClientOrPool, txnClient =>
    db.insert('seat_bookings', { screening_id: 1001, seat, customer }).run(txnClient));

const pair = await db.serializable(pool, txnClient => Promise.all([
  bookSeat('G1', 'Wren Abernathy', txnClient),
  bookSeat('G2', 'Wren Abernathy', txnClient),
]));

One transaction, both seats or neither: START TRANSACTION ISOLATION LEVEL SERIALIZABLE, the two INSERTs, COMMIT.

• The callback runs at the outer transaction's level, and that level is not checked, at run time or by the type: every TxnClient is a Queryable, so db.serializable(txnClient, …) type-checks with a client of db.readCommitted(), and its callback runs in that READ COMMITTED transaction.
• A function whose parameter is a TxnClientFor… type is checked: IsolationSatisfying.
• What marks the client is its own _zapatos property (TxnClient).

Errors

Rejects with	When	Since
Isolation level must be an IsolationLevel value (e.g. IsolationLevel.Serializable), not '…'	isolationLevel is not a value of IsolationLevel; thrown before a client is checked out or a query sent	pgcast 6.6.2
the callback's error	after ROLLBACK; for 40001 and 40P01, after the last attempt	Zapatos
a TypeError, Cannot destructure property 'code' of 'err' as it is null. (or undefined)	the callback throws null or undefined, after ROLLBACK	Zapatos
"the transaction was rolled back, not committed: a query in it failed and the callback caught the error, so Postgres answered COMMIT with ROLLBACK; let the error out of the callback, or ROLLBACK TO a SAVEPOINT before going on"	with transactionRequiresCommit, Postgres answered the COMMIT with ROLLBACK; not retried, and no ROLLBACK of its own	pgcast 6.7.0

The type refuses a level that is not an IsolationLevel (2345), a string included:

await db.transaction(pool, 'SERIALIZABLE', async txnClient => true);
Argument of type '"SERIALIZABLE"' is not assignable to parameter of type 'IsolationLevel'.

Zapatos 6.6.1 wrote any value after ISOLATION LEVEL as it was, where it could run a second statement (changelog). A transaction passed through is not checked.

serializable()

db.serializable(txnClientOrQueryable, callback)

transaction() at IsolationLevel.Serializable: START TRANSACTION ISOLATION LEVEL SERIALIZABLE. Its callback gets a TxnClientForSerializable.

repeatableRead()

db.repeatableRead(txnClientOrQueryable, callback)

transaction() at IsolationLevel.RepeatableRead: START TRANSACTION ISOLATION LEVEL REPEATABLE READ. Its callback gets a TxnClientForRepeatableRead.

readCommitted()

db.readCommitted(txnClientOrQueryable, callback)

transaction() at IsolationLevel.ReadCommitted: START TRANSACTION ISOLATION LEVEL READ COMMITTED. Its callback gets a TxnClientForReadCommitted.

serializableRO()

db.serializableRO(txnClientOrQueryable, callback)

transaction() at IsolationLevel.SerializableRO: START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY. Its callback gets a TxnClientForSerializableRO. Postgres refuses a write in it (25006).

repeatableReadRO()

db.repeatableReadRO(txnClientOrQueryable, callback)

transaction() at IsolationLevel.RepeatableReadRO: START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY. Its callback gets a TxnClientForRepeatableReadRO. Postgres refuses a write in it (25006).

readCommittedRO()

db.readCommittedRO(txnClientOrQueryable, callback)

transaction() at IsolationLevel.ReadCommittedRO: START TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY. Its callback gets a TxnClientForReadCommittedRO. Postgres refuses a write in it (25006).

serializableRODeferrable()

db.serializableRODeferrable(txnClientOrQueryable, callback)

transaction() at IsolationLevel.SerializableRODeferrable: START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE. Its callback gets a TxnClientForSerializableRODeferrable. Postgres refuses a write in it (25006).

With DEFERRABLE, Postgres may wait for a snapshot, then runs the transaction without the overhead of a serializable one and with no risk of a serialisation failure (40001) (SET TRANSACTION): suited to a long report or a backup.

A report whose two counts read the same snapshot:

const report = await db.serializableRODeferrable(pool, async txnClient => ({
  films: await db.count('films', db.all).run(txnClient),
  bookings: await db.count('seat_bookings', db.all).run(txnClient),
}));

report is { films: 4, bookings: 0 } on the docs schema. The transaction sends:

START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE
-- the callback's two count() queries
COMMIT

The SQL of each query: count(). The other read-only shortcuts send the same, with their level; in readCommittedRO(), each query reads its own snapshot, so a booking committed between the two counts is in the second.

Types

IsolationLevel

enum IsolationLevel {
  Serializable = 'SERIALIZABLE',
  RepeatableRead = 'REPEATABLE READ',
  ReadCommitted = 'READ COMMITTED',
  SerializableRO = 'SERIALIZABLE, READ ONLY',
  RepeatableReadRO = 'REPEATABLE READ, READ ONLY',
  ReadCommittedRO = 'READ COMMITTED, READ ONLY',
  SerializableRODeferrable = 'SERIALIZABLE, READ ONLY, DEFERRABLE',
}

The second argument of transaction(). Each value is written after START TRANSACTION ISOLATION LEVEL; each member has its shortcut:

Member	Shortcut	Its client
Serializable	serializable()	TxnClientForSerializable
RepeatableRead	repeatableRead()	TxnClientForRepeatableRead
ReadCommitted	readCommitted()	TxnClientForReadCommitted
SerializableRO	serializableRO()	TxnClientForSerializableRO
RepeatableReadRO	repeatableReadRO()	TxnClientForRepeatableReadRO
ReadCommittedRO	readCommittedRO()	TxnClientForReadCommittedRO
SerializableRODeferrable	serializableRODeferrable()	TxnClientForSerializableRODeferrable

IsolationSatisfying

type IsolationSatisfying<T extends IsolationLevel>

The levels whose client is accepted where a client of level T is asked for: T and the stricter levels. A read-write level stands for its read-only one, not the reverse; SerializableRODeferrable stands only for itself.

T	IsolationSatisfying<T>
Serializable	Serializable
RepeatableRead	Serializable, RepeatableRead
ReadCommitted	Serializable, RepeatableRead, ReadCommitted
SerializableRO	Serializable, SerializableRO
RepeatableReadRO	Serializable, RepeatableRead, SerializableRO, RepeatableReadRO
ReadCommittedRO	every level but SerializableRODeferrable
SerializableRODeferrable	Serializable, SerializableRO, SerializableRODeferrable

A function that takes a TxnClientForRepeatableRead can be given the client of serializable(), not that of readCommitted() (2345, with strict on or off):

const countBookings = (txnClient: db.TxnClientForRepeatableRead) =>
  db.count('seat_bookings', { screening_id: 1000 }).run(txnClient);

await db.serializable(pool, txnClient => countBookings(txnClient));
await db.readCommitted(pool, txnClient => countBookings(txnClient));
Argument of type 'TxnClientForReadCommitted' is not assignable to parameter of type 'TxnClientForRepeatableRead'.
  Type 'IsolationLevel.Serializable | IsolationLevel.RepeatableRead | IsolationLevel.ReadCommitted' is not assignable to type 'IsolationLevel.Serializable | IsolationLevel.RepeatableRead'.
    Type 'IsolationLevel.ReadCommitted' is not assignable to type 'IsolationLevel.Serializable | IsolationLevel.RepeatableRead'.

TxnClient

interface TxnClient<T extends IsolationLevel> extends pg.PoolClient {
  _zapatos?: { isolationLevel: T; txnId: number };
}

The client a transaction() callback gets: the one checked out from the pool, or the client given.

• _zapatos is set while the transaction runs: its isolationLevel, and its txnId, the id the listeners get. Once the transaction ends, transaction() deletes it, and releases a client it checked out.
• T: the levels the client stands for, an IsolationSatisfying union in the seven types below.
• _zapatos is optional in the type, so any pg.PoolClient is accepted as a TxnClient, with or without a transaction open on it.

TxnClientForSerializable

TxnClient<IsolationSatisfying<IsolationLevel.Serializable>>: the client of serializable().

TxnClientForRepeatableRead

TxnClient<IsolationSatisfying<IsolationLevel.RepeatableRead>>: the client of repeatableRead(); takes that of serializable() too.

TxnClientForReadCommitted

TxnClient<IsolationSatisfying<IsolationLevel.ReadCommitted>>: the client of readCommitted(); takes that of serializable() and repeatableRead() too.

TxnClientForSerializableRO

TxnClient<IsolationSatisfying<IsolationLevel.SerializableRO>>: the client of serializableRO(); takes that of serializable() too.

TxnClientForRepeatableReadRO

TxnClient<IsolationSatisfying<IsolationLevel.RepeatableReadRO>>: the client of repeatableReadRO(); takes that of serializable(), repeatableRead() and serializableRO() too.

TxnClientForReadCommittedRO

TxnClient<IsolationSatisfying<IsolationLevel.ReadCommittedRO>>: the client of readCommittedRO(); takes that of every shortcut but serializableRODeferrable().

TxnClientForSerializableRODeferrable

TxnClient<IsolationSatisfying<IsolationLevel.SerializableRODeferrable>>: the client of serializableRODeferrable(); takes that of serializable() and serializableRO() too.

isDatabaseError()

db.isDatabaseError(err, ...types)

true when the code of err, five characters, starts with the code of one of types; otherwise false, and always with no types.

• err: an object with an optional code string; null or undefined throws a TypeError. On an error from Postgres, .run() rejects with node-postgres's DatabaseError, whose code is the SQLSTATE; it also has constraint, detail, table and the other fields Postgres sends.
  • The type accepts any, pg.DatabaseError and { code?: string }, and refuses unknown and the global Error (below); err instanceof pg.DatabaseError narrows a catch variable to one it accepts.
• types: names, any number; with none, false. Two kinds:

Name	Code	Matches
a class: IntegrityConstraintViolation	23	every code of the class: 23505, 23514, 23503…
a condition, Class_Condition: IntegrityConstraintViolation_UniqueViolation	23505	that code

The names are those of Postgres's error codes, in PascalCase: unique_violation in the class integrity_constraint_violation is IntegrityConstraintViolation_UniqueViolation. The type refuses any other name.

const booking = { screening_id: 1002, seat: 'C4', customer: 'Tamsin Rowe' };
await db.insert('seat_bookings', booking).run(pool);

try {
  await db.insert('seat_bookings', booking).run(pool);
} catch (err: any) {
  if (!db.isDatabaseError(err, 'IntegrityConstraintViolation_UniqueViolation')) throw err;
  console.log(err.constraint);  // seat_bookings_pkey
}

On the docs schema:

Query	code	Condition name
a second booking of the same seat	23505	IntegrityConstraintViolation_UniqueViolation
the seat 'Z100', which the domain seat_label refuses	23514	IntegrityConstraintViolation_CheckViolation
a booking of a screening that does not exist	23503	IntegrityConstraintViolation_ForeignKeyViolation
an insert() in serializableRO()	25006	InvalidTransactionState_ReadOnlySqlTransaction
two concurrent bookings of a seat, at SERIALIZABLE (above)	40001	TransactionRollback_SerializationFailure

• DiskFull, OutOfMemory, TooManyConnections and ConfigurationLimitExceeded have the class's code, 53, as InsufficientResources does: each matches every code of the class. InsufficientResources_DiskFull matches 53100 only.
• The errors pgcast throws itself have no code: false for each (below).
• With strict on, a catch variable is unknown, which the type refuses (2345):

try {
  await db.insert('seat_bookings', { screening_id: 1002, seat: 'C4', customer: 'Tamsin Rowe' }).run(pool);
} catch (err) {
  db.isDatabaseError(err, 'IntegrityConstraintViolation_UniqueViolation');
Argument of type 'unknown' is not assignable to parameter of type 'Error'.
}

• A value typed as the global Error, as instanceof Error narrows it, is refused too, with strict on or off (2559): the parameter's type is { code?: string }, and Error has no property in common with it.

try {
  await db.insert('seat_bookings', { screening_id: 1002, seat: 'C4', customer: 'Tamsin Rowe' }).run(pool);
} catch (err) {
  if (err instanceof Error && db.isDatabaseError(err, 'IntegrityConstraintViolation_UniqueViolation')) {}
Type 'Error' has no properties in common with type 'Error'.
}

Handling them in an app: Errors.

Errors pgcast throws before any query

A plain Error, with no code, before anything is sent. A shortcut throws its own synchronously, from the call itself: .run() is never reached, so a .catch() chained on it does not get them.

The others are thrown as .compile() compiles the query, so .run() rejects with them; transaction() rejects with its own. Each is listed with what throws it:

Thrown by	How	Messages
select(), selectOne(), selectExactlyOne() and the aggregates, given a bad order, lock or aggregate	the call throws	select(), Errors
upsert() of rows with different keys, with upsertRequiresSameKeys	the call throws	Rows with different keys
update() with no column to set	the call throws	update()
truncate() with another option	the call throws	truncate()
an interpolation that has no meaning in SQL: a number, null, db.all, the global object	.run() rejects	Interpolations
db.self with no column; db.parent() with no parent table, so as an insert(), upsert() or update() value	.run() rejects	self, parent()
db.Default, db.self, db.raw(), db.cols() or db.vals() as a filter value; db.raw(), db.cols() or db.vals() as an insert(), upsert() or update() value, or in any vals({…})	.run() rejects	Whereable objects, Conditions, insert(), vals()
transaction() with another isolation level	its promise rejects	transaction(), Errors

After a query, pgcast rejects in three cases of its own: selectExactlyOne() with no row, or a second one under selectExactlyOneChecksForSecondRow, with a NotExactlyOneError; count() and the other aggregates with no row to read, with a TypeError (count()); transaction() whose COMMIT Postgres answers with ROLLBACK, with transactionRequiresCommit (Errors).