Categorizing Extension Upgrade Errors for Automated Triage

Turn a raw ALTER EXTENSION ... UPDATE or pg_upgrade failure into a single machine-readable category — DEPENDENCY_BLOCK, SCHEMA_DRIFT, ABI_MISMATCH, or LOCK_CONTENTION — so a CI/CD gate can route it to the correct remediation queue without a human reading the log.

Up: Error Categorization Frameworks — the classification layer that normalizes upgrade failures into operational tiers; this page is the concrete SQLSTATE-plus-stderr classifier that framework depends on, and it feeds the wider Extension Upgrade Planning & Compatibility Validation process.

Context & When This Applies

Reach for this technique the moment an automated promotion pipeline runs ALTER EXTENSION UPDATE or pg_upgrade unattended and must decide, in code, whether a failure should halt the pipeline, retry, or reschedule. It applies to PostgreSQL 12 and newer, where psql and pg_upgrade reliably emit a five-character SQLSTATE alongside the human-readable message, and it is most valuable across a heterogeneous fleet running compiled extensions such as PostGIS, timescaledb, pgvector, and pg_partman, where the same version bump can fail four different ways on four different nodes.

Manual triage does not scale here: an SRE paged at 02:00 for a could not load library line needs a fundamentally different runbook than one paged for deadlock detected, and the two are indistinguishable to a naive “job exited non-zero” check. Deterministic categorization is what lets the pipeline enforce per-category thresholds — abort immediately on a terminal error, retry with backoff on a transient one — the way Threshold Tuning for Downtime Windows prescribes. Run the classifier against the output captured during async simulation with pg_upgrade --check so a candidate is categorized in staging, well before it reaches a live maintenance window.

SQLSTATE-plus-stderr classifier routing upgrade failures to four categories A raw ALTER EXTENSION or pg_upgrade failure carrying a SQLSTATE and stderr text enters an ordered, most-specific-first classifier that keys on the SQLSTATE class and a message regex. It fans out to four category outputs. An XX class with a could-not-load-library message becomes ABI_MISMATCH, a terminal abort routed to rebuild the base image. A 0A or 42 class with no-update-path or missing-object text becomes DEPENDENCY_BLOCK, a terminal abort routed to fix packaging. A 42 class with already-exists text becomes SCHEMA_DRIFT, a conditional manual disposition routed to ALTER EXTENSION ADD or DROP then retry. A 55 or 40 class with deadlock or lock-timeout text becomes LOCK_CONTENTION, a transient retry routed to backoff and off-peak rescheduling. Any failure that matches no rule falls through to UNKNOWN and manual review. XX 0A / 42 42 55 / 40 Upgrade failure ALTER EXTENSION · pg_upgrade SQLSTATE + raw stderr Ordered classifier SQLSTATE class · first 2 chars message regex · stderr text most-specific rule wins ABI_MISMATCH class XX · could not load library ABORT · terminal → rebuild base image DEPENDENCY_BLOCK class 0A / 42 · no update path ABORT · terminal → fix packaging SCHEMA_DRIFT class 42 · already exists MANUAL · conditional → ADD/DROP, retry LOCK_CONTENTION class 55 / 40 · deadlock / timeout RETRY · transient → backoff / off-peak no rule matches → UNKNOWN · manual review

Concept: SQLSTATE Class Plus Message Signature

PostgreSQL reports every error with a five-character SQLSTATE code whose first two characters name the class. That class is the fast, locale-independent discriminator; the message text is the tiebreaker within a class. Extension upgrades cluster into a small, stable set of classes:

  • Class 42 — syntax/access, and the 42883/42P07/42704 family — surfaces when an update script collides with catalog objects that already exist (a SCHEMA_DRIFT signal) or references a missing prerequisite object (a DEPENDENCY_BLOCK signal). Message text separates the two.
  • Class XX — internal error, plus the could not load library / undefined symbol messages — indicates the compiled .so is wrong for this server: an ABI_MISMATCH. These are the failures that appear only at call time, which is why the dependency tree analysis pre-flight cannot catch them and the classifier must.
  • Class 55 — object not in prerequisite state (55P03 lock-not-available) and 40P01 deadlock detected — is contention on system catalogs while ALTER EXTENSION waits for an AccessExclusiveLock. This is LOCK_CONTENTION, and crucially it is transient: the same job usually succeeds in a quieter window.
  • Class 0A — feature not supported, and the “requires version” / “no update path” messages — is a DEPENDENCY_BLOCK: the version tuple is not reachable on this node until packaging is fixed.

The operational payoff is that only two of these categories are worth retrying automatically. ABI_MISMATCH and DEPENDENCY_BLOCK are terminal — retrying re-runs the same broken artifact — so they must abort the pipeline and escalate. LOCK_CONTENTION is retryable with backoff, and SCHEMA_DRIFT is conditionally retryable only after a corrective ALTER EXTENSION ADD/DROP. Encoding that disposition next to the category is what makes the output actionable rather than merely descriptive.

Runnable Implementation

The classifier below takes the SQLSTATE (from psycopg’s err.sqlstate, or parsed from a pg_upgrade log) and the raw stderr text, and returns a structured verdict. It is ordered most-specific-first so that a message signature can override a broad class match. Drop it into the triage stage of your pipeline as a single module.

#!/usr/bin/env python3
"""Categorize a PostgreSQL extension-upgrade failure for automated triage."""
from __future__ import annotations

import json
import re
from dataclasses import asdict, dataclass


@dataclass
class Verdict:
    category: str          # DEPENDENCY_BLOCK | SCHEMA_DRIFT | ABI_MISMATCH | LOCK_CONTENTION | UNKNOWN
    disposition: str       # abort | retry | reschedule | manual
    retryable: bool
    sqlstate: str | None
    matched_on: str        # which rule fired, for auditability


# Ordered rules: (name, sqlstate_prefixes, message_regex, category, disposition, retryable)
# The FIRST rule that matches wins, so put narrow message signatures ahead of
# broad class-only matches.
RULES = [
    ("abi_undefined_symbol", (),      r"could not load library|undefined symbol|"
                                      r"incompatible library|PG_MODULE_MAGIC",
     "ABI_MISMATCH",     "abort",      False),
    ("dep_no_update_path",   ("0A",),  r"no update path|requires version|"
                                      r"could not open extension control file",
     "DEPENDENCY_BLOCK", "abort",      False),
    ("lock_deadlock",        ("40",),  r"deadlock detected",
     "LOCK_CONTENTION",  "retry",      True),
    ("lock_timeout",         ("55",),  r"lock timeout|could not obtain lock|"
                                      r"canceling statement due to lock",
     "LOCK_CONTENTION",  "reschedule", True),
    ("drift_already_exists", ("42",),  r"already exists",
     "SCHEMA_DRIFT",     "manual",     False),
    ("drift_missing_object", ("42",),  r"does not exist|cannot drop",
     "DEPENDENCY_BLOCK", "abort",      False),
    ("abi_internal",         ("XX",),  r".",  # any XX-class internal error
     "ABI_MISMATCH",     "abort",      False),
]


def classify(sqlstate: str | None, stderr: str) -> Verdict:
    text = (stderr or "").strip()
    state = (sqlstate or "").strip() or None
    for name, prefixes, pattern, category, disposition, retryable in RULES:
        class_ok = bool(prefixes) and state is not None and state[:2] in prefixes
        if pattern == r".":
            # Class-only fallback (e.g. any XX internal error): the SQLSTATE
            # class alone decides it; a blank SQLSTATE never triggers it.
            fires = class_ok
        else:
            # Message-signature rule: the regex is authoritative. A class
            # prefix, when present, only guards it — and a blank SQLSTATE
            # from a pg_upgrade log still lets the signature match.
            msg_ok = re.search(pattern, text, re.IGNORECASE) is not None
            fires = msg_ok and (not prefixes or class_ok or state is None)
        if fires:
            return Verdict(category, disposition, retryable, state, name)
    return Verdict("UNKNOWN", "manual", False, state, "no_rule")


if __name__ == "__main__":
    # Example: a pgcrypto ABI drift surfaced at call time.
    v = classify(
        sqlstate="XX000",
        stderr='could not load library "$libdir/pgcrypto": '
               "undefined symbol: PQencryptPasswordConn",
    )
    print(json.dumps(asdict(v), indent=2))

Wire the verdict directly into the gate: an abort disposition fails the job and escalates, retry re-queues with exponential backoff, reschedule defers the job to an off-peak window, and manual opens a ticket with the raw log attached. Only ABI_MISMATCH and DEPENDENCY_BLOCK should ever block a promotion outright, because both mean the artifact itself is wrong and no amount of retrying will fix it.

Expected Output & Verification

Running the example emits a stable JSON object that downstream automation can branch on:

{
  "category": "ABI_MISMATCH",
  "disposition": "abort",
  "retryable": false,
  "sqlstate": "XX000",
  "matched_on": "abi_undefined_symbol"
}

Verify the classifier against the catalog before trusting a verdict in production. For a suspected DEPENDENCY_BLOCK, confirm the target version is genuinely unreachable on the node rather than merely uninstalled:

-- A non-empty result means the installed version and the on-disk default_version
-- disagree — the DEPENDENCY_BLOCK verdict is real and packaging must be fixed.
SELECT e.extname, e.extversion, a.default_version
FROM pg_extension e
JOIN pg_available_extensions a ON e.extname = a.name
WHERE e.extversion <> a.default_version;

For a LOCK_CONTENTION verdict, confirm a real blocker exists before rescheduling — otherwise the retry loop masks a genuine bug:

-- Rows here confirm ungranted catalog locks blocking the upgrade backend.
SELECT l.locktype, l.mode, l.granted, a.state, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'pg_class'::regclass
  AND NOT l.granted;

A correct pipeline records the Verdict as a structured log line (category, disposition, sqlstate, matched_on, extname, attempt_count) so category trends feed back into the compatibility matrix — a spike in ABI_MISMATCH for one extension usually means a stale package in the base image.

Edge Cases & Gotchas

A blank SQLSTATE from a pg_upgrade log falls through to the message rules. Unlike psql, pg_upgrade writes failures to pg_upgrade_output.d/*/pg_upgrade.log without always surfacing a SQLSTATE. The classifier handles this because every rule can fire on its message signature alone, but confirm you are feeding it the right text:

could not load library "$libdir/postgis-3": No such file or directory

This must classify as ABI_MISMATCH even with sqlstate=None. If it returns UNKNOWN, you passed a truncated log — grep the full file for FATAL|ERROR|could not load|no update path first, exactly as the async simulation verification step does.

A 42501 privilege error masquerades as a dependency problem. When the pipeline role lacks rights, the message reads:

ERROR:  permission denied to create extension "pg_stat_statements"
Detail: Must have CREATE privilege on current database.

This is class 42501, not a missing package — retrying or reinstalling never helps. Route it to manual and fix the grant model per Security Boundaries & Permissions; add an explicit ("42501",) rule ahead of the broad 42 drift rules if privilege failures are common in your fleet.

SCHEMA_DRIFT is only conditionally retryable. An already exists error means a prior partial upgrade left orphaned objects in pg_depend. The default disposition is manual on purpose — blindly retrying re-hits the collision. Rebind the untracked object first, then re-run:

-- Rebind an orphaned object the extension should own, then retry the UPDATE.
ALTER EXTENSION target_ext ADD FUNCTION public.conflicting_fn(integer);

A deadlock and a lock timeout demand different responses. Both are LOCK_CONTENTION, but 40P01 (retry) is safe to re-attempt immediately, whereas 55P03 (reschedule) means a long-running session is holding the catalog — retrying in a hot window just deadlocks again. Terminate the idle blocker or defer to an off-peak window, and pair the retry with a bounded lock_timeout so the ALTER EXTENSION automation never hangs indefinitely. Keep a snapshot and point-in-time recovery checkpoint available so a mid-flight abort has a clean restore target.