Transient vs. permanent errors: when to retry, and what a public API is allowed to say

The problem

The data source service executes user-defined queries against several databases and serves the results through public, unauthenticated endpoints: other applications fetch a source by its code, no login needed.

When a query failed, the service did this:

} catch {
  return { message: 'Server error.', data: null };
}

One answer for every possible failure. That caused three different problems at once:

1. We couldn't decide whether to retry. A database that's temporarily full and a query with a typo looked identical.
2. Callers couldn't react. A null didn't say whether trying again later would help.
3. We lost the real cause, because nothing was logged. (That's how a random null bug hid for weeks; see "Random nulls: the bug that hid behind a silent catch".)

The obvious fix, "just return the real error", was not an option either. On a public endpoint, a raw PostgreSQL error can reveal table names, column names, schema names, connection hosts or credentials. The fix had to give callers enough to act, give us everything to debug, and give attackers nothing.

The details

Two families of failure

After collecting real errors from the logs, they fell into two groups that need opposite treatment.

Transient: the source is fine, the environment isn't.

• the shared database is full (53300 too_many_connections),
• the server is starting up or shutting down,
• the network dropped, a connection was refused or reset, DNS hiccupped,
• a statement was cancelled by a timeout.

Retrying a moment later usually works.

Permanent: the source itself is broken.

• the table or column doesn't exist (42P01, 42703),
• authentication failed (28P01),
• the SQL has a syntax error,
• the mapping refers to a field that isn't in the result.

Retrying can never help. It only adds load and delay.

Where the information lives

node-postgres exposes two kinds of codes on errors:

• SQLSTATE codes from PostgreSQL (53300, 42P01…), on error.code,
• Node.js errno codes for socket-level problems (ECONNREFUSED, ETIMEDOUT…), also on error.code.

And a few failures come without any code, only a message: a socket dropping mid-query ("Connection terminated unexpectedly") or the pool's own acquire timeout ("timeout exceeded when trying to connect").

The challenges

• Codes are spread across layers: the database, the Node.js network stack and the pool library each report differently.
• Some failures only have a message, so matching must be careful and specific.
• Retries can make things worse. Retrying instantly, or forever, against an overloaded server adds to the overload.
• Labels must be useful but vague. Callers need to know what kind of failure, never where or why exactly.
• The batch must stay predictable. One failed code in a batch of 23 must not hide the other 22 results.

The solution

1. One classifier for every error

export type SourceErrorLabel =
  | 'TOO_MANY_CONNECTIONS'
  | 'CONNECTION_FAILED'
  | 'QUERY_FAILED'
  | 'MAPPING_FAILED'
  | 'NOT_FOUND';

export interface ClassifiedError {
  kind: 'TRANSIENT' | 'PERMANENT';
  label: SourceErrorLabel;
  code?: string;   // SQLSTATE or errno, when available
  detail: string;  // raw message: LOGS ONLY, never a response
}

// Server full / not accepting connections right now
const TOO_MANY_CONNECTIONS = new Set(['53300', '53000', '57P03']);

// Slow or unreachable, not wrong
const TRANSIENT_CONNECTION = new Set([
  '57014',                                  // query_canceled (statement timeout)
  'ECONNREFUSED', 'ECONNRESET', 'ETIMEDOUT', 'EPIPE',
  'EHOSTUNREACH', 'ENETUNREACH', 'EAI_AGAIN',
]);

// Reported without a code by the driver or the pool
const TRANSIENT_MESSAGES = [
  /connection terminated/i,
  /timeout exceeded when trying to connect/i,
  /connection timeout/i,
];

The classifier reads the code safely (errors aren't always Error objects), then decides:

export function classify(error: unknown): ClassifiedError {
  const code = readCode(error);
  const detail = readMessage(error);

  if (code && TOO_MANY_CONNECTIONS.has(code)) {
    return { kind: 'TRANSIENT', label: 'TOO_MANY_CONNECTIONS', code, detail };
  }
  if ((code && TRANSIENT_CONNECTION.has(code)) || TRANSIENT_MESSAGES.some((re) => re.test(detail))) {
    return { kind: 'TRANSIENT', label: 'CONNECTION_FAILED', code, detail };
  }
  return { kind: 'PERMANENT', label: 'QUERY_FAILED', code, detail };
}

Two design choices:

• The default is permanent. Anything unknown is not retried. Retrying unknown errors risks hammering a server for a problem retrying can't fix.
• Each SQLSTATE in the sets is explicit and commented. When someone adds a code, the reason is right there.

MAPPING_FAILED is produced separately, when transforming the rows throws, and NOT_FOUND when the code doesn't match any source.

2. Retry only transient errors, with backoff

const MAX_ATTEMPTS = 3;
const BASE_DELAY_MS = 1_000;

for (let attempt = 1; ; attempt++) {
  try {
    const result = await pool.query(sql);
    return { ok: true, data: result.rows, attempts: attempt };
  } catch (error) {
    const c = classify(error);
    if (c.kind === 'TRANSIENT' && attempt < MAX_ATTEMPTS) {
      logger.warn(`Source ${code}: ${c.label} (${c.code}) on attempt ${attempt}/${MAX_ATTEMPTS}, retrying.`);
      await sleep(BASE_DELAY_MS * attempt);   // 1 s, then 2 s
      continue;
    }
    logger.error(`Source ${code} failed: ${c.code ?? ''} ${c.detail}`);
    return { ok: false, attempts: attempt, ...c };
  }
}

• At most 3 attempts. A server that's still full after three seconds won't be fixed by a fourth.
• Linear backoff (1 s, then 2 s) gives the shared server time to free a slot instead of retrying in the same busy millisecond.
• Permanent errors return immediately, at attempt 1.
• The number of attempts is recorded with each result, so the sync history shows "succeeded on attempt 2", which is an early warning that the server is getting tight.

No retry library was added. A small loop was clearer, and easy to test.

3. Two audiences, two messages

The same failure produces two very different outputs.

In the logs (private): everything needed to debug:

Source SRC-014 failed: 42P01 relation "materials_v2" does not exist

In the public response: a stable, coarse label:

{ "message": "Server error.", "data": null, "error": "QUERY_FAILED" }

The rule, written next to the classifier so nobody "improves" it later:

The execute endpoints are public and unauthenticated, so schema names, connection strings and raw driver text must only ever reach the logs, never a response.

The labels are still useful to callers:

Adding the error field was backward compatible: existing callers only read data, so nothing broke for them, and new callers gained a way to react.

4. Every code gets an answer in a batch

Batches return a map from code to result, and every requested code is present, including unknown ones (NOT_FOUND). Callers can always match each request to an answer, and one broken source never hides the others.

5. Persist the class, not the secret

When the nightly sync records a failure, the source row stores only the coarse label (lastErrorCode = 'QUERY_FAILED'). The full SQLSTATE and message live in the private sync history, visible to maintainers, not to the public.

Lessons learned

• Classify before you react. "Retry or not" depends entirely on whether the failure is in the environment or in the request.
• Use the codes your stack gives you. SQLSTATE and errno codes are precise, stable and documented; parse them instead of guessing from messages.
• Default to not retrying. Unknown errors should fail fast; only known transient ones earn a retry.
• Retry with limits and backoff. Few attempts, growing delays, and never in a tight loop against an overloaded resource.
• Separate audiences. Logs get the truth; public responses get a label. Write that rule down where the code lives.
• Coarse labels are still valuable. "Temporary" vs. "broken" is exactly what a caller needs to decide what to do next.
• Count attempts. Successful retries are an early signal that a shared resource is under pressure.

FAQ

What is the difference between transient and permanent errors?

Transient errors come from temporary conditions (a full server, a network blip, a timeout) and often succeed on retry. Permanent errors come from the request itself (bad SQL, missing table, wrong credentials) and will fail every time, so retrying only adds load.

Which PostgreSQL errors are safe to retry?

Common examples are 53300 (too many connections), 53000 (insufficient resources), 57P03 (cannot connect now) and 57014 (query cancelled by a timeout), plus network errors like ECONNREFUSED, ECONNRESET and ETIMEDOUT. Errors like 42P01 (undefined table) or 42601 (syntax error) are not.

How many times should I retry a failed database call?

Usually a small number, two or three attempts, with an increasing delay between them. If it still fails, report the failure and let a later process (a scheduled job, the user) try again.

Is it safe to return database error messages from an API?

Not on public endpoints. Raw database errors can reveal table names, schemas or hosts. Log the details privately and return a short, generic error category to the client.