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 covering replicas, learners first. 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

A client needs only each node's PostgreSQL port, 5432; the cluster transport ports (7190 to 7192) carry traffic between nodes and are never dialed by a client (see Ports).

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, which means one of two things depending on whether your transaction had asked to commit. 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 table whose placement changed mid-transaction, a shard group with no leader to read a snapshot from, lost commit state, and a stale schema epoch.	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) before COMMIT was sent	The connection died before any server could answer: the node was killed, or the socket dropped mid-statement, while the transaction had not asked to commit. Your driver raises its own transport error, not a database error.	Reconnect to another node and re-execute. Treat it exactly like 57P01: the transaction never asked to commit, so nothing was decided.
No SQLSTATE after COMMIT (or an autocommit write) was sent	The connection died after the commit was asked for and before its answer arrived.	Treat it exactly like 08007: a commit is decided once every shard group it touched holds a durable vote for it, not when the answer reaches you, and a commit whose coordinating node died after that point is finished by the other nodes. Reconnect, verify whether it applied, then retry only if the write is idempotent or protected by the outbox pattern below.

Those last two rows are not a footnote. A lost connection 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 case a client written only against the SQLSTATE column will miss. Which row applies depends only on whether your code had sent COMMIT (or, outside a transaction block, the writing statement itself) when the connection died. Every retry driver on this page re-runs its transaction body after a lost connection, which is safe in both rows only because each body follows the outbox recipe below, exactly as their retry of 08007 is.

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

# SCRAMDB_NODES here is this script's own convenience variable, read by
# the sample only; ScramDB itself never reads it.
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. With no
    /// SQLSTATE the outcome is unknown if the socket died after `COMMIT` was sent,
    /// so re-running is safe only because the body follows the outbox recipe.
    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 (see above)
    }
}

/// `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 repository's examples. That is not a claim you have to take on faith. An automated test 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. 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.