Error Categorization Frameworks for PostgreSQL Extension Upgrades #

An extension upgrade that fails without a classification layer forces a human to read the log, decide whether the failure was transient or terminal, and either retry or roll back by hand — at exactly the moment (a maintenance window, an on-call page) when that judgement is slowest and most error-prone. An error categorization framework removes that decision from the human path: it maps every failure a promotion can raise — a SQLSTATE from a live ALTER EXTENSION UPDATE, a stderr line from an external pg_upgrade, or an unexpected catalog state — onto one of a small set of operational tiers, and each tier carries a fixed, automated response. This page is for database SREs and platform engineers who need ALTER EXTENSION and pg_upgrade failures to route themselves to retry, rollback, or escalation deterministically, so the promotion gate emits one binary decision instead of a stack trace.

Up: Extension Upgrade Planning & Compatibility Validation — this categorization stage is the fourth gate of that pipeline; the gate consumes the tier this page produces and turns it into a single promote-or-block decision.

Classification Flow at a Glance #

The framework normalizes a raw failure — captured from either a live connection or an external process — into four operational tiers, each bound to one fixed response. Classification happens once, at the point of capture, and every downstream actor keys off the tier rather than re-parsing the original text.

The value of the framework is that the tier, not the operator, decides what happens next: BLOCKING halts the pipeline and pages a DBA, RECOVERABLE retries under exponential backoff, DEPRECATION logs telemetry and schedules follow-up without failing the run, and INFRASTRUCTURE triggers capacity remediation before a bounded retry. The rest of this page builds that classifier, wires it into the promotion gate, and defines the recovery path each tier hands off to.

Prerequisites #

The classifier is read-and-parse only until it triggers a response; capturing structured error context is what makes it reliable, so confirm the environment exposes that context before wiring it into a gate.

• PostgreSQL version: 9.4 or newer on every target. The framework relies on SQLSTATE codes (stable across all supported majors) and on the verbose error fields psycopg2 exposes through Diagnostics; both predate every server you are likely to run. pg_upgrade output parsing applies to any major-version jump from 9.4 forward.
• Python packages: Python 3.8+ with psycopg2 2.8+ (pip install psycopg2-binary) for live ALTER EXTENSION capture. The stderr classifier for pg_upgrade needs only the standard library (re, json, subprocess). Async fleets can capture the same sqlstate attribute from asyncpg’s PostgresError; the driver trade-offs are covered under ALTER EXTENSION Automation.
• Required privileges: Classification itself needs no elevated grant — it reads exception objects and process output. The remediation it triggers (retry, rollback, snapshot restore) inherits whatever role the promotion runs under, which should be a scoped installer role rather than a broad superuser per Security Boundaries & Permissions.
• Catalog state: The classifier assumes the failure it inspects came from a planned promotion whose target was already confirmed reachable in pg_available_extension_versions. An “unreachable target” is a planning failure that the compatibility gate should have blocked upstream, not an error the taxonomy has to re-derive — keep that mapping current with Compatibility Matrix Synchronization.
• Log access: For pg_upgrade, retain the tool’s stderr, the generated *.log files, and the server log for the run. A classifier that only sees the process exit code cannot distinguish a disk-full abort from a privilege rejection.

Core Concept: The SQLSTATE Class Prefix Is the Signal #

The mistake that makes error handling brittle is keying off exact five-character SQLSTATE codes or off human-readable message text. Message strings change between minor releases and are localized; the full code space is large. PostgreSQL solves this for you: every SQLSTATE is a two-character class followed by a three-character subclass, and the class alone already encodes the coarse category the taxonomy needs. Classifying on the class prefix means an unfamiliar subcode still routes correctly — a new Class 53 resource error you have never seen is still infrastructure.

The classes that surface during extension upgrades map cleanly onto the four tiers:

Two subtleties make the class prefix necessary but not sufficient. First, Class 42 is heterogeneous: 42501 (privilege) is genuinely blocking, but 42710/42723 (duplicate object/function) can mean a partial prior upgrade already created the object, which is recoverable by re-checking extversion for idempotency rather than escalating. The classifier therefore treats a short list of specific codes as overrides on top of the class default. Second, a Class 0A feature_not_supported raised mid-ALTER EXTENSION frequently marks a non-transactional step (background-worker registration, shared-memory allocation) that has already committed — which is why this tier is blocking and hands off to a snapshot restore, not a ROLLBACK. The transactional boundaries of the ALTER EXTENSION step itself are analysed under ALTER EXTENSION Automation.

Step-by-Step Implementation #

The framework is built in four steps: capture structured context from a live failure, classify it by class prefix with code-level overrides, decide the response, and expose the same taxonomy to external pg_upgrade output. Each block is complete and copy-pasteable.

Step 1 — Capture structured error context from a live upgrade #

psycopg2 raises subclasses of psycopg2.Error that expose the SQLSTATE on .pgcode and the full verbose fields on .diag. Capture these instead of stringifying the exception — the code and the diagnostic fields are stable, the rendered message is not.

#!/usr/bin/env python3
"""Capture structured error context from a live ALTER EXTENSION UPDATE."""
import psycopg2


def run_extension_update(conn, name: str, target: str) -> dict:
    """Attempt an update; return a structured context dict on failure."""
    stmt = 'ALTER EXTENSION "{}" UPDATE TO %s'.format(name)
    try:
        with conn:  # commit on success, ROLLBACK on exception
            with conn.cursor() as cur:
                cur.execute(stmt, (target,))
        return {"ok": True, "extension": name, "target": target}
    except psycopg2.Error as exc:
        diag = exc.diag
        return {
            "ok": False,
            "extension": name,
            "target": target,
            "sqlstate": exc.pgcode,                     # e.g. "55P03"
            "message": (diag.message_primary or "").strip(),
            "detail": diag.message_detail,
            "hint": diag.message_hint,
            "context": diag.context,
        }

The with conn: block gives transactional cleanup for free: on any exception the transaction rolls back, so ordinary catalog DDL inside the update leaves no partial rows. The context dict is what the classifier consumes — never the raw exception string.

Step 2 — Classify by class prefix with code-level overrides #

The classifier reads the two-character class, applies a small override table for specific codes that defy their class default, and returns a tier plus the reason. Ordering is most-severe-first so a context that somehow matches two rules resolves to the stronger tier.

#!/usr/bin/env python3
"""Map a captured SQLSTATE to one of four operational tiers."""

# Class prefix -> default tier. The two-character class already encodes the
# coarse category, so an unseen subcode still routes correctly.
CLASS_TIERS = {
    "42": "BLOCKING",         # access rule / privilege / syntax
    "0A": "BLOCKING",         # feature not supported (often non-transactional)
    "2B": "BLOCKING",         # dependent objects still exist
    "55": "RECOVERABLE",      # object not in prerequisite state (locks)
    "40": "RECOVERABLE",      # deadlock / serialization
    "08": "RECOVERABLE",      # connection exception
    "57": "RECOVERABLE",      # operator intervention (query_canceled)
    "53": "INFRASTRUCTURE",   # insufficient resources
    "58": "INFRASTRUCTURE",   # system error (io_error)
    "01": "DEPRECATION",      # warning
}

# Specific codes that override their class default.
CODE_OVERRIDES = {
    "42710": "RECOVERABLE",   # duplicate_object: partial prior upgrade, re-check idempotency
    "42723": "RECOVERABLE",   # duplicate_function: same as above
    "53300": "INFRASTRUCTURE",  # too_many_connections (Class 53 already, kept explicit)
}


def classify(context: dict) -> dict:
    """Return {'tier', 'reason', 'sqlstate'} for a failure context."""
    code = (context.get("sqlstate") or "").upper()
    if not code:
        return {"tier": "BLOCKING", "reason": "no SQLSTATE captured",
                "sqlstate": None}

    tier = CODE_OVERRIDES.get(code) or CLASS_TIERS.get(code[:2], "BLOCKING")
    return {
        "tier": tier,
        "sqlstate": code,
        "reason": context.get("message") or "unclassified failure",
    }

An unknown code with an unknown class defaults to BLOCKING: the framework fails safe, escalating anything it cannot prove is recoverable rather than optimistically retrying a doomed upgrade.

Step 3 — Decide the response and enforce it #

Each tier maps to exactly one action. RECOVERABLE retries under bounded exponential backoff; INFRASTRUCTURE signals a capacity remediation hook then retries once; DEPRECATION records telemetry and continues; BLOCKING halts and hands off to the recovery path.

#!/usr/bin/env python3
"""Turn a classified tier into a single enforced action."""
import time

MAX_RETRIES = 3


def handle(conn, name, target, classify_fn, capture_fn) -> dict:
    """Run an upgrade with tier-driven retry/backoff. Returns final outcome."""
    attempt = 0
    while True:
        result = capture_fn(conn, name, target)
        if result["ok"]:
            return {"decision": "promoted", "attempts": attempt + 1}

        verdict = classify_fn(result)
        tier = verdict["tier"]

        if tier == "DEPRECATION":
            # Non-fatal: emit telemetry, let the promotion proceed.
            emit_metric("upgrade.deprecation", verdict)
            return {"decision": "promoted_with_warning", "verdict": verdict}

        if tier in ("RECOVERABLE", "INFRASTRUCTURE"):
            if tier == "INFRASTRUCTURE":
                request_capacity(verdict)          # scale disk / connections
            attempt += 1
            if attempt >= MAX_RETRIES:
                return {"decision": "block", "reason": "retries exhausted",
                        "verdict": verdict}
            time.sleep(min(2 ** attempt, 30))       # capped exponential backoff
            continue

        # BLOCKING (or anything unknown): stop and route to recovery.
        return {"decision": "block", "reason": "terminal", "verdict": verdict}


def emit_metric(_name, _verdict):  # replace with your telemetry client
    ...


def request_capacity(_verdict):    # replace with your autoscaling hook
    ...

request_capacity is where an INFRASTRUCTURE tier becomes useful: a 53100 disk_full triggers a volume expansion, a 53300 too_many_connections drains the pooler, and only then does the bounded retry run — a blind retry against a full disk just burns the maintenance window.

Step 4 — Apply the same taxonomy to external pg_upgrade output #

Major-version jumps run pg_upgrade as an external process that never opens a psycopg2 connection, so there is no .pgcode to read — only stderr and generated log files. A regex taxonomy recovers the same tiers from that text. Keep the rules ordered most-severe-first and stop at the first match.

#!/usr/bin/env python3
"""Classify external pg_upgrade / pg_dump stderr into the same four tiers."""
import re
import sys
import json

STDERR_TIERS = [
    ("BLOCKING", re.compile(
        r"(?:permission denied|must be superuser|"
        r"could not (?:load|find function)|has no update path)", re.I)),
    ("INFRASTRUCTURE", re.compile(
        r"(?:no space left on device|out of memory|"
        r"too many clients already)", re.I)),
    ("RECOVERABLE", re.compile(
        r"(?:deadlock detected|lock not available|could not connect|"
        r"the database system is starting up)", re.I)),
    ("DEPRECATION", re.compile(
        r"(?:WARNING|NOTICE):.*(?:deprecated|obsolete|will be removed)", re.I)),
]


def classify_stderr(text: str) -> dict:
    for tier, pattern in STDERR_TIERS:
        m = pattern.search(text)
        if m:
            return {"tier": tier, "matched": m.group(0)}
    return {"tier": "BLOCKING", "matched": None}  # fail safe on no match


if __name__ == "__main__":
    verdict = classify_stderr(sys.stdin.read())
    print(json.dumps(verdict))
    # Distinct exit codes let the pipeline branch without re-parsing.
    sys.exit({"BLOCKING": 2, "INFRASTRUCTURE": 3,
              "RECOVERABLE": 0, "DEPRECATION": 0}[verdict["tier"]])

Both entry points — the live psycopg2 path and this stderr path — emit the same four tiers, so the promotion gate downstream has a single vocabulary regardless of whether the failure came from an in-place ALTER EXTENSION or an external pg_upgrade. The detailed signal-to-category mapping for every diagnostic query is developed further in Categorizing Extension Upgrade Errors for Automated Triage.

Dry-Run and Validation Gate #

Classification is proven before production by running the whole upgrade inside a rolled-back transaction and asserting the tier the classifier returns. A dry run that exercises the classifier on real failure output is the only way to know the taxonomy covers the codes your extensions actually raise — surface it in staging, routed onto a production-mirroring topology per Test Environment Routing, rather than discovering an unclassified code at 02:00.

#!/usr/bin/env bash
set -euo pipefail

# Idempotent dry-run wrapper: exercise the update inside a transaction that
# always rolls back, so the classifier sees real failure output with no commit.
EXTENSION="pg_stat_statements"
TARGET_VERSION="1.10.1"

# Run psql inside the `if` test: under `set -e` a separate `$?` check would be
# unreachable because the script would already have aborted on failure.
# The guard also checks extversion so the upgrade is idempotent.
if ! psql -v ON_ERROR_STOP=1 <<SQL
BEGIN;
  DO \$\$
  BEGIN
    IF EXISTS (SELECT 1 FROM pg_extension
               WHERE extname = '${EXTENSION}' AND extversion <> '${TARGET_VERSION}') THEN
      ALTER EXTENSION ${EXTENSION} UPDATE TO '${TARGET_VERSION}';
    END IF;
  END \$\$;
ROLLBACK;
SQL
then
  echo "VALIDATION_FAILED: dry-run aborted; capture SQLSTATE and classify"
  exit 1
fi

The gate consumes the classifier’s structured verdict, not the exit code alone. A passing dry run emits a JSON payload the pipeline can assert against expectations before it commits the real upgrade:

{
  "extension": "pg_stat_statements",
  "target": "1.10.1",
  "dry_run": "rolled_back",
  "classification": { "tier": "RECOVERABLE", "sqlstate": "55P03",
                      "reason": "lock not available" },
  "gate_decision": "retry_scheduled"
}

Because the classifier is run against the async replay itself, unexpected non-transactional steps are caught here too — a discipline shared with Async Upgrade Simulation, which surfaces the lock order and catalog diffs the tiers are derived from.

Failure Modes and Error Taxonomy #

The taxonomy exists to make each of these failure classes a deterministic decision rather than a judgement call. The catalog state and characteristic message below are what the classifier keys on; the tier fixes the response.

The single most dangerous miscategorization is a terminal error mislabelled as RECOVERABLE: that loops a doomed upgrade until the window closes. The fail-safe default of BLOCKING for any unrecognised code is the guard against it — the framework only retries what it can prove is transient.

Rollback and Recovery Path #

A BLOCKING verdict routes to recovery rather than leaving the catalog half-advanced. The recovery path depends on whether the failing step was transactional:

1. Transactional failure (Class 42/40/55 caught mid-DDL). The with conn: block already rolled the transaction back; extversion is unchanged and no dependent object was rewritten. Recovery is to fix the root cause (grant the privilege, clear the lock) and re-run the idempotent dry run — no restore needed.
2. Non-transactional failure (Class 0A, committed worker or shared-memory step). ROLLBACK cannot undo it, so recovery restores prior catalog state from the pre-upgrade snapshot. Route to Snapshot & Point-in-Time Recovery to rewind a catalog that advanced past a partial commit, and stage the extension’s explicit downgrade script if one exists.
3. Ambiguous state (promotion aborted with extversion uncertain). Re-read pg_extension to establish the actual installed version, then decide: if it matches the source, re-plan; if it advanced, restore. The broader mid-flight recovery choreography — which workload to fence, when to fail over — lives in Fallback Routing Strategies, and the execution side of a clean rerun is handled by the automated execution and rollback workflows.

A verification query re-establishes ground truth after any recovery, before dependent workloads are released:

-- What version is actually installed now, and is anything still blocked?
SELECT e.extname, e.extversion AS installed, n.nspname AS schema
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
WHERE e.extname = 'pg_stat_statements';

Performance and Scale Considerations #

Classification itself is negligible — a dictionary lookup on a two-character prefix — but the responses it triggers have real cost windows that matter at fleet scale.

• Retry backoff versus the maintenance window. Each RECOVERABLE retry consumes wall-clock time against a finite window. Cap retries (three is a sane default) and, critically, verify the cumulative backoff fits inside the budget computed by Threshold Tuning for Downtime Windows — a classifier that retries past the window boundary is worse than one that blocks early.
• Lock contention during retries. A 55P03 retry re-attempts an AccessExclusiveLock acquisition; on a hot table under load, repeated attempts can queue behind — and block — application traffic. Use lock_timeout on the upgrade session so a retry fails fast rather than parking the lock queue.
• Fleet-wide classification, not per-node judgement. Across hundreds of clusters the same SQLSTATE will recur; because the taxonomy is deterministic, an operator only ever inspects genuinely new codes. Aggregate tier counts per rollout so a spike in INFRASTRUCTURE verdicts flags a capacity problem across the fleet rather than one node at a time.
• INFRASTRUCTURE remediation latency. A volume expansion or pooler drain is not instant; the bounded retry after request_capacity must wait for the remediation to actually complete, not fire immediately, or it re-fails into the same full disk.

FAQ #

Related Pages #

• Extension Upgrade Planning & Compatibility Validation — the staged validation pipeline whose gate consumes the tier this framework produces.
• Categorizing Extension Upgrade Errors for Automated Triage — per-vector diagnostic queries and resolution workflows for each failure signal.
• Async Upgrade Simulation — replays the upgrade against a clone so the classifier sees real failure output before production.
• Threshold Tuning for Downtime Windows — the window budget that bounds how many RECOVERABLE retries are safe.
• ALTER EXTENSION Automation — the transactional boundaries that decide whether a failure rolls back or needs a restore.
• Snapshot & Point-in-Time Recovery — the recovery path a BLOCKING non-transactional failure hands off to.