Connecting to a cluster: the client contract

By the end of this page you will know how to point a stock PostgreSQL driver at any node in a ScramDB cluster, the complete set of errors a node loss can produce and exactly what to do about each one, and how to make a non-idempotent write safe to retry even when its outcome is unknown.

Connect to any node

Connect anywhere with a stock PostgreSQL driver, retry on a small, closed set of SQLSTATEs, and never write custom error parsing: that is the entire client contract a ScramDB cluster makes, and every claim on this page is behavior the engine guarantees, not advice you have to take on faith.

Every node serves every table. A node answers what it holds locally and moves the smallest thing necessary otherwise: a point write hops internally to its leader, an analytical read runs locally, on a covering replica, or as a scatter across bucket owners. That choice is cost-based and visible in EXPLAIN; you never make it yourself.

postgresql://user@node-a:5432/appdb
postgresql://user@node-b:5432/appdb   # identical capability

One pool against any node is the correct default. If your driver supports multi-host failover, list several hosts in the DSN and let it pick among them on connect:

postgresql://user@node-a:5432,node-b:5432,node-c:5432/appdb

Roles (voter, learner, hybrid) exist inside the engine. A client never sees them and never needs a per-role connection string, a "connect to the leader" helper, or a read/write split: whichever node you happen to connect to coordinates whatever you send it, forwarding the parts it doesn't hold locally.

The retry table

Everything that can go wrong when a node dies collapses into five outcomes: four carry a SQLSTATE, and the fifth is the absence of one. That closed set is the whole contract; nothing cluster-shaped is allowed to fall outside it.

SQLSTATE	Meaning	What your client does
40001 serialization_failure	The transaction left NO effect. Covers write conflicts, a leadership change that never accepted a proposal, timestamp unavailability, a stale schema epoch, and a refused staleness bound.	Re-execute the transaction. Safe unconditionally.
08007 transaction_resolution_unknown	The outcome is UNKNOWN. The entry was accepted and MAY still commit.	Reconnect, verify whether it applied, then retry only if the write is idempotent or protected by the outbox pattern below.
57P01 admin_shutdown	The node is draining and never accepted the statement.	Reconnect to another node.
57P03 cannot_connect_now	The cluster has not formed yet.	Back off and reconnect.
No SQLSTATE (a driver-level connection error)	The connection died before any server could answer: the node was killed, or the socket dropped mid-statement. Your driver raises its own transport error, not a database error.	Reconnect to another node and re-execute. Treat it exactly like 57P01: nothing was decided, so nothing is in doubt.

That last row is not a footnote. It is the MOST common failure during a node loss, because a killed node closes its sockets before it can send an error, and it is the one row a client written only against the SQLSTATE column will miss. Every retry driver on this page handles it in the same branch as 57P01.

The message that comes back alongside the code always names the real cause, even under a shared SQLSTATE, so a log line still tells you whether a 40001 was an election, a stale schema epoch, or a refused staleness bound, rather than leaving you to guess from the code alone.

Why 08007 is never folded into 40001

One retryable code would be simpler. It would also be wrong. A 40001 is provable: the engine knows the transaction left no effect, so re-executing it is unconditionally safe. A 08007 means the opposite of certainty: the write was accepted and may already have committed, under a leader that changed before its outcome could be observed. Retry that blindly and you risk applying a non-idempotent write twice, silently, with no error to tell you it happened.

The fix is not a smarter retry loop. It's the outbox pattern below, which makes retrying an in-doubt write safe by construction instead of hopeful. If you ever find yourself string-matching an error message to decide whether to retry, stop: the SQLSTATE is sufficient, and any case where it is not is a bug worth reporting.

Retry drivers, tested against a real cluster

Each driver below implements the table above in about a dozen lines, and none of them do any string matching. 08007 is retried in each one only because the transaction body is written against the outbox recipe in the next section; without that, the correct move on 08007 is to stop and verify, not retry.

Python (psycopg 3)

import os
import time
import psycopg

NODES = os.environ.get("SCRAMDB_NODES", "node-a:5432,node-b:5432,node-c:5432").split(",")

RETRY_DEFINITE = "40001"
IN_DOUBT = "08007"
UNAVAILABLE = ("57P01", "57P03")


def connect(deadline: float):
    """Connect to whichever node answers first. A draining or not-yet-formed
    node is skipped, which is exactly what its SQLSTATE means."""
    last = None
    attempt = 0
    while time.monotonic() < deadline:
        for node in NODES:
            host, _, port = node.partition(":")
            try:
                return psycopg.connect(
                    host=host,
                    port=int(port or 5432),
                    user="app",
                    dbname="appdb",
                    connect_timeout=5,
                    autocommit=True,
                )
            except Exception as e:  # noqa: BLE001 - any connect failure: try the next node
                last = e
        attempt += 1
        time.sleep(min(0.1 * attempt, 1.0))
    raise SystemExit(f"no node accepted a connection: {last}")


def run_with_retry(work, budget_s: float = 120.0):
    """The whole retry contract in about a dozen lines, with no string
    matching anywhere: only a SQLSTATE decides whether to retry."""
    deadline = time.monotonic() + budget_s
    backoff = 0.05
    conn = None
    while True:
        if conn is None:
            conn = connect(deadline)
        try:
            return work(conn)
        except Exception as e:  # noqa: BLE001 - classified by SQLSTATE below
            code = getattr(e, "sqlstate", None)
            if code is not None and code not in (RETRY_DEFINITE, IN_DOUBT, *UNAVAILABLE):
                raise  # a real application error: a constraint, a typo, a permission
            # 40001 / 08007 / 57P01 / 57P03 / a dead socket (code None): drop
            # the session and re-run the same body.
            try:
                conn.close()
            except Exception:  # noqa: BLE001 - already dead
                pass
            conn = None
            if time.monotonic() >= deadline:
                raise
            time.sleep(backoff)
            backoff = min(backoff * 2, 0.5)

getattr(e, "sqlstate", None) is the whole trick: psycopg sets .sqlstate on every server-raised error and leaves it absent on a transport failure, which is exactly the "no SQLSTATE" row of the table above. There is no exception-type inventory to keep in sync.

SQLAlchemy

import time
from sqlalchemy import create_engine
from sqlalchemy.exc import DBAPIError, OperationalError

RETRYABLE = {"40001", "08007", "57P01", "57P03"}


def engines(nodes, user, dbname):
    """One engine (one pool) per node. Any of them can serve any statement."""
    return [
        create_engine(
            f"postgresql+psycopg://{user}@{host}:{port}/{dbname}",
            pool_pre_ping=True,  # a killed node's stale pooled connection is
            pool_size=4,         # discarded on checkout instead of raising
            future=True,
        )
        for host, port in nodes
    ]


def _sqlstate(exc: BaseException) -> str | None:
    """SQLAlchemy wraps the driver error; the SQLSTATE lives on `.orig`."""
    return getattr(getattr(exc, "orig", None), "sqlstate", None)


def _retryable(exc: BaseException) -> bool:
    code = _sqlstate(exc)
    if code is not None:
        return code in RETRYABLE
    # No SQLSTATE means the failure never reached the server.
    return isinstance(exc, (OperationalError, DBAPIError))


def run_with_retry(pools, work, budget_s: float = 120.0):
    """Retry the whole transaction against the next pool in the ring."""
    deadline = time.monotonic() + budget_s
    backoff = 0.05
    idx = 0
    last = None
    while time.monotonic() < deadline:
        engine = pools[idx % len(pools)]
        idx += 1
        try:
            with engine.begin() as conn:
                return work(conn)
        except Exception as e:  # noqa: BLE001 - classified by SQLSTATE above
            last = e
            if not _retryable(e):
                raise
            time.sleep(backoff)
            backoff = min(backoff * 2, 0.5)
    raise SystemExit(f"never completed within the budget: {last}")

The only ScramDB-aware code in this file is the four-line SQLSTATE check in _retryable; everything else is stock SQLAlchemy against the PostgreSQL dialect. 40001 surfaces as sqlalchemy.exc.OperationalError wrapping the driver's error, with the real SQLSTATE on .orig.sqlstate, never in the message text.

Rust (tokio-postgres)

use std::future::Future;
use std::time::{Duration, Instant};
use tokio_postgres::{Client, NoTls};

const DEFINITE_RETRY: &str = "40001";
const IN_DOUBT: &str = "08007";
const UNAVAILABLE: [&str; 2] = ["57P01", "57P03"];

/// What the client does about a failure.
enum Class {
    /// `40001`: the transaction left no effect. Re-execute unconditionally.
    Retry,
    /// `08007`: the outcome is unknown. Safe to re-execute only because the
    /// transaction is written against the outbox recipe.
    InDoubt,
    /// `57P01` / `57P03`, or no SQLSTATE at all: reconnect to another node.
    Reconnect,
    /// Anything else is a real application error and must surface.
    Fatal,
}

fn classify(e: &tokio_postgres::Error) -> Class {
    match e.code().map(|c| c.code()) {
        Some(DEFINITE_RETRY) => Class::Retry,
        Some(IN_DOUBT) => Class::InDoubt,
        Some(c) if UNAVAILABLE.contains(&c) => Class::Reconnect,
        Some(_) => Class::Fatal,
        None => Class::Reconnect, // the socket died before any answer came back
    }
}

/// `host:port` -> the libpq `host=.. port=..` pair tokio-postgres expects.
fn fix(node: &str) -> String {
    match node.split_once(':') {
        Some((h, p)) => format!("{h} port={p}"),
        None => node.to_string(),
    }
}

/// Connect to whichever node answers first. The list is a failover
/// convenience, not a routing decision: every node serves every table.
async fn connect_any(nodes: &[String], deadline: Instant) -> Result<Client, String> {
    let mut last = String::from("no nodes configured");
    let mut backoff = Duration::from_millis(50);
    loop {
        for node in nodes {
            let dsn = format!("host={} user=app dbname=appdb connect_timeout=5", fix(node));
            match tokio_postgres::connect(&dsn, NoTls).await {
                Ok((client, conn)) => {
                    tokio::spawn(async move {
                        let _ = conn.await;
                    });
                    return Ok(client);
                }
                Err(e) => last = e.to_string(),
            }
        }
        if Instant::now() >= deadline {
            return Err(format!("no node accepted a connection: {last}"));
        }
        tokio::time::sleep(backoff).await;
        backoff = (backoff * 2).min(Duration::from_millis(500));
    }
}

/// The retry driver: reconnect-and-re-run, driven entirely by [`classify`].
async fn run_with_retry<F, Fut, T>(
    nodes: &[String],
    conn: &mut Option<Client>,
    budget: Duration,
    mut work: F,
) -> Result<T, String>
where
    F: FnMut(Client) -> Fut,
    Fut: Future<Output = (Client, Result<T, tokio_postgres::Error>)>,
{
    let deadline = Instant::now() + budget;
    let mut backoff = Duration::from_millis(50);
    loop {
        let client = match conn.take() {
            Some(c) => c,
            None => connect_any(nodes, deadline).await?,
        };
        let (client, result) = work(client).await;
        match result {
            Ok(value) => {
                *conn = Some(client);
                return Ok(value);
            }
            Err(e) => {
                if matches!(classify(&e), Class::Fatal) {
                    return Err(format!("unretryable: {e}"));
                }
                // The session is dropped rather than reused: a failed
                // transaction may have left it aborted, and a reconnect is
                // also how a killed node is failed away from.
                drop(client);
                if Instant::now() >= deadline {
                    return Err(format!("never completed within the budget: {e}"));
                }
                tokio::time::sleep(backoff).await;
                backoff = (backoff * 2).min(Duration::from_millis(500));
            }
        }
    }
}

e.code().map(|c| c.code()) is tokio-postgres's own accessor for the SQLSTATE; None is exactly the transport-error case, and no downcasting to a specific error type is needed anywhere in this file.

The full runnable versions of all three, plus a working money-transfer demo that exercises every branch, live in the ScramDB repo under examples/cluster-client/ (psycopg and SQLAlchemy) and scramdb/examples/cluster_client.rs (tokio-postgres). That is not a claim you have to take on faith. scramdb/tests/cluster_client_leader_kill.rs runs this same retry driver against a real 3-node cluster through two separate kills, one of a peer node and one of the node the client itself is connected to (forcing an actual reconnect), then asserts that every SQLSTATE the cluster returned during the run is inside this page's table, and that the account balances involved still balance to the penny afterward. The test's own name says what it proves: the_example_app_survives_a_leader_kill_using_only_documented_sqlstates. This page's retry table is tested, not asserted.

The outbox recipe for a non-idempotent write

An idempotent write is never a problem: SET balance = 100 WHERE id = 1 gives the same result no matter how many times it runs, so retrying an 08007 for it is free. A non-idempotent write, a balance moved by a relative amount, a payment charged, an email sent, is different: run it twice and you've moved the money twice. 08007 means you cannot tell from the error alone whether it ran once or not at all, so the retry has to be made safe at the data model, not guessed at in the retry loop.

The recipe: give every write a request id you control, and record that id in the same transaction as the write's effect. A retry with the same id then either finds its own prior effect and stops, or finds nothing and does the work for the first time; either way the effect happens exactly once.

CREATE TABLE outbox (
    request_id  text PRIMARY KEY,
    reply       text
);

BEGIN;
  -- 1. Has this exact request already been applied? A row here means yes:
  --    read the reply back and stop, without touching accounts again.
  SELECT reply FROM outbox WHERE request_id = $1;

  -- 2. Nothing came back: do the real, non-idempotent work.
  UPDATE accounts SET balance = balance - $2 WHERE id = $3;
  UPDATE accounts SET balance = balance + $2 WHERE id = $4;

  -- 3. Record request_id as handled, atomically with the work in step 2:
  --    both commit together, or neither does.
  INSERT INTO outbox (request_id, reply) VALUES ($1, $5);
COMMIT;

Wired into the psycopg retry driver above, the whole non-idempotent transfer is:

def transfer(request_id: str, src: int, dst: int, amount: int) -> None:
    def work(conn):
        with conn.transaction():
            prior = conn.execute(
                "SELECT reply FROM outbox WHERE request_id = %s", (request_id,)
            ).fetchone()
            if prior is not None:
                return  # already applied: do not repeat the transfer
            conn.execute(
                "UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src)
            )
            conn.execute(
                "UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst)
            )
            conn.execute(
                "INSERT INTO outbox (request_id, reply) VALUES (%s, %s)",
                (request_id, f"moved {amount} from {src} to {dst}"),
            )

    run_with_retry(work)

After an 08007, reconnect and re-run this exact transaction with the same request_id. If the first attempt committed, step 1 finds the row and you stop, having applied the transfer exactly once. If it did not commit, step 1 finds nothing and the transfer happens now, for the first and only time. Either way, the retry driver never has to know which case it's in: it just runs the same body again.

This is the standard pattern for exactly-once effects under an uncertain outcome (Weikum and Vossen, Transactional Information Systems, chapter 17), and it is the only correct answer to an in-doubt outcome for a non-idempotent write. A retry counter is not: capping retries at three, or five, or any number, bounds how many times you try, but does nothing about the one retry that happens to land after the original attempt actually committed. The count is irrelevant; the double apply happens on whichever attempt follows a true commit, regardless of where the limit is set. Only an idempotency key checked inside the same transaction as the effect closes that gap.

Next

• Failover for the two timers that decide how fast a node loss turns into one of the errors above, and exactly what happens to your TCP connection when it's your own node that goes down.
• Distributed queries for what a statement that spans nodes actually does once you're connected.
• Cluster overview for the full list of guarantees a cluster gives you beyond the client contract on this page.
• Scaling for what a learner node actually is, one of the roles this page's DSN never has to name.