# Retries

Source: https://pgcast.dev/retries

Book seats at a screening under `SERIALIZABLE`, so that two requests at once can't break a rule, and let pgcast run the transaction again when Postgres refuses one. Then what goes in the callback and what stays out of it, and how to test a retry. It assumes the basics of the [tutorial](https://pgcast.dev/tutorial).

## Put the rule and its writes in one callback

The rule: four seats per customer per screening, at most. No constraint can say that, so the code counts, then books, both in one [`serializable()`](https://pgcast.dev/transaction#serializable) transaction:

```ts
const bookSeats = (txnClientOrPool: pg.Pool | db.TxnClientForSerializable,
  screening_id: number, seats: string[], customer: string) =>
  db.serializable(txnClientOrPool, async txnClient => {
    const held = await db.count('seat_bookings', { screening_id, customer }).run(txnClient);
    if (held + seats.length > 4) return null;  // four each, at most
    return db.insert('seat_bookings', seats.map(seat => ({ screening_id, seat, customer }))).run(txnClient);
  });

// the same customer, in two tabs at once
const [tabOne, tabTwo] = await Promise.all([
  bookSeats(pool, 1000, ['D4', 'D5'], 'Ines Calloway'),
  bookSeats(pool, 1000, ['E4', 'E5', 'E6'], 'Ines Calloway'),
]);
```

Ines has two tabs open, and books from both at once. Each transaction counts 0 seats held, then books, which would give her five. At `SERIALIZABLE`, Postgres refuses one of the two, at its `INSERT` or at its `COMMIT`, with 40001 (`serialization_failure`); pgcast rolls it back and runs the whole callback again. Here, `tabTwo`'s transaction sends:

```sql
-- attempt 1
START TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- count(): 0 seats held, then insert() of E4, E5 and E6
COMMIT
-- refused with 40001: tabOne has committed D4 and D5
ROLLBACK
-- attempt 2, 25 to 250 ms later
START TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- count(): 2 seats held, and 2 + 3 > 4, so no insert()
COMMIT
```

The tab whose transaction commits first gets its rows, `seat_bookings.JSONSelectable[]`; the other gets `null`, here `tabTwo`. The SQL of the callback's queries: [`count()`](https://pgcast.dev/select#count) and [`insert()`](https://pgcast.dev/insert#insert); what pgcast sends around them, and when it retries: [Retries](https://pgcast.dev/transaction#retries).

- Run every query of the transaction on `txnClient`. A query on `pool` is not in it: it isn't rolled back, and the next attempt runs it again.
- Two requests may still run one after the other, and then neither is retried: the second simply counts the first one's seats.

## Keep side effects out of the callback

The callback runs once per attempt, up to five times by default. Postgres rolls back its queries each time; nothing else is undone. So what isn't a query on `txnClient` goes before the transaction or after it:

```ts
const screening_id = 1000, seats = ['D4', 'D5'], customer = 'Ines Calloway';

// before: runs once
const limit = await fetchSeatLimit(customer);

const booked = await db.serializable(pool, async txnClient => {
  const held = await db.count('seat_bookings', { screening_id, customer }).run(txnClient);
  if (held + seats.length > limit) return null;
  return db.insert('seat_bookings', seats.map(seat => ({ screening_id, seat, customer }))).run(txnClient);
});

// after: runs once, and only once committed
if (booked) await sendConfirmation(customer, booked.map(booking => booking.seat));
```

| Work | Where |
|---|---|
| the queries of the rule and the booking | in the callback, on `txnClient` |
| a `fetch()`, a file, a price from another service | before |
| an email, a payment, a message on a queue | after the `await`, from what the callback returned |
| a counter, an array, a log line | from the return value, not changed in the callback |

Moved into the callback, a retried booking sends two confirmation emails, and pushes its seats twice onto an array outside it.

## Let the error through

pgcast retries only what reaches it. Don't swallow a database error inside the callback: after it, Postgres refuses every query of the transaction with 25P02 (`current transaction is aborted, commands ignored until end of transaction block`), and if the callback returns, pgcast sends `COMMIT`, which Postgres turns into a rollback without an error.

The promise then resolves with what the callback returned, nothing is saved, and a 40001 caught this way is never retried; pgcast warns once, `PGCAST_DEP003`. With [`transactionRequiresCommit: true`](https://pgcast.dev/runtime-config#Config.transactionRequiresCommit), the default from 7.0, the promise rejects instead, still without a retry: pgcast never saw the error.

If you catch one inside, rethrow it: an `Error`, not `null` or `undefined`, for which pgcast rejects with a `TypeError` of its own instead ([#179](https://github.com/AlexandreDemartin/pgcast/issues/179)).

Catch it outside the transaction, once pgcast has given up, with [`isDatabaseError()`](https://pgcast.dev/transaction#isDatabaseError):

```ts
const reply = async (seats: string[], customer: string) => {
  try {
    const booked = await bookSeats(pool, 1001, seats, customer);
    return booked ? 'Booked' : 'Four seats each, at most';
  } catch (err: any) {
    if (db.isDatabaseError(err, 'IntegrityConstraintViolation_UniqueViolation')) return 'Seat taken';
    if (db.isDatabaseError(err, 'TransactionRollback_SerializationFailure', 'TransactionRollback_DeadlockDetected'))
      return 'Busy: please try again';
    throw err;
  }
};
```

| What happened | `bookSeats()` | `reply()` |
|---|---|---|
| booked | resolves with the rows | `'Booked'` |
| over the four seats | resolves with `null` | `'Four seats each, at most'` |
| a seat already booked by someone else | rejects with 23505, sometimes after a retry | `'Seat taken'` |
| a conflict on every attempt | rejects with the last attempt's 40001 or 40P01 | `'Busy: please try again'` |

## Book inside a larger transaction

Given the `txnClient` of a transaction already open, `bookSeats()` joins it: `serializable()` sends nothing of its own and calls the callback at once ([Passing a transaction through](https://pgcast.dev/transaction#passing-a-transaction-through)). A couple books two seats, both or neither:

```ts
// a couple: both bookings, or neither
const couple = await db.serializable(pool, async txnClient => {
  const wren = await bookSeats(txnClient, 1000, ['G1'], 'Wren Abernathy');
  const barnaby = await bookSeats(txnClient, 1000, ['G2'], 'Barnaby Quill');
  if (!wren || !barnaby) throw new Error('Both seats, or neither');  // rolls back the other one
  return [...wren, ...barnaby];
});
```

One transaction: `START TRANSACTION ISOLATION LEVEL SERIALIZABLE`, the two `count()`s and `insert()`s, `COMMIT`. If either `bookSeats()` resolves with `null`, the `throw` sends `ROLLBACK` in place of `COMMIT`, and the `await` rejects with that `Error`, not retried. On a 40001, it is the outer transaction that rolls back and runs its callback again, both bookings included.

Typed `pg.Pool | db.TxnClientForSerializable`, the first parameter refuses the client of a transaction at a lower level, where the rule would no longer hold (2345, with `strict` on or off):

```ts
await db.readCommitted(pool, txnClient => bookSeats(txnClient, 1000, ['G1'], 'Wren Abernathy'));  // error TS2345
```

Typed [`db.Queryable`](https://pgcast.dev/sql-template#Queryable), it would take that client, and `bookSeats()` would run at `READ COMMITTED` ([#177](https://github.com/AlexandreDemartin/pgcast/issues/177)).

## Tune and watch the retries

Three options of [`setConfig()`](https://pgcast.dev/runtime-config#setConfig), for every transaction of the process:

```ts
db.setConfig({
  transactionAttemptsMax: 10,
  transactionRetryDelay: { minMs: 50, maxMs: 500 },
  transactionListener: (message, txnId) => console.log(`[txn ${txnId}] ${message}`),
});
```

A retried transaction then logs:

```txt
[txn 0] Transaction rollback (code 40001) on attempt 1 of 10, retrying in 292ms
[txn 0] Retrying transaction, attempt 2 of 10
```

- [`transactionAttemptsMax`](https://pgcast.dev/runtime-config#Config.transactionAttemptsMax): the attempts in all. `1` turns the retries off.
- [`transactionRetryDelay`](https://pgcast.dev/runtime-config#Config.transactionRetryDelay): the wait before each retry. The client stays checked out of the pool while it waits.
- [`transactionListener`](https://pgcast.dev/runtime-config#Config.transactionListener): every message it gets is in [Retries](https://pgcast.dev/transaction#retries).

## Test a retry

Two real requests at once may or may not conflict, so a test can't count on it. Throw the failure yourself instead: pgcast retries any error whose `code` is `'40001'`. From an outer transaction, `bookSeats()` joins it, so its rows are rolled back with the failed attempt:

```ts
db.setConfig({ transactionAttemptsMax: 2, transactionRetryDelay: { minMs: 0, maxMs: 0 } });

test('a booking runs again after a serialization failure', async () => {
  await db.deletes('seat_bookings', { screening_id: 1003 }).run(pool);  // so that it runs again on the same database
  let attempts = 0;
  const booked = await db.serializable(pool, async txnClient => {
    const seats = await bookSeats(txnClient, 1003, ['H1', 'H2'], 'Tamsin Rowe');
    if (++attempts === 1) throw Object.assign(new Error('A serialization failure, for the test'), { code: '40001' });
    return seats;
  });

  assert.equal(attempts, 2);
  assert.deepEqual(booked?.map(booking => booking.seat), ['H1', 'H2']);
  assert.equal(await db.count('seat_bookings', { screening_id: 1003, customer: 'Tamsin Rowe' }).run(pool), 2);
});

test('a booking gives up after its last attempt', async () => {
  let attempts = 0;
  await assert.rejects(db.serializable(pool, async () => {
    attempts++;
    throw Object.assign(new Error('A serialization failure, for the test'), { code: '40001' });
  }), { code: '40001' });
  assert.equal(attempts, 2);
});
```

Here with `test` from `node:test` and `assert` from `node:assert/strict`; any test runner does. The first test proves the callback can run twice: the first attempt's two seats are gone, and the second books them once.

- The second test gives up: its callback throws on every attempt, so with `transactionAttemptsMax: 2` it runs twice, and the promise rejects with the very error it threw, after `on attempt 2 of 2, giving up`.
- `transactionRetryDelay` at `0` keeps the test fast. `setConfig()` holds for the whole process: put back what the other tests expect ([`getConfig()`](https://pgcast.dev/runtime-config#getConfig)).

## See also

- [`transaction()`](https://pgcast.dev/transaction#transaction): its isolation levels, the SQL it sends, its retries and its errors.
- [`TxnClientForSerializable`](https://pgcast.dev/transaction#TxnClientForSerializable) and [`IsolationSatisfying`](https://pgcast.dev/transaction#IsolationSatisfying): which client a level accepts.
- [Errors](https://pgcast.dev/handle-errors): the errors of an app, from Postgres and from pgcast.