[PATCH] psql: Display SQLSTATE macro name in verbose error reports

First seen: 2026-05-23 19:18:22+00:00 · Messages: 3 · Participants: 3

Latest Update

2026-05-27 · claude-opus-4-6

Incremental Update: Tom Lane's Architectural Distinction

Tom Lane provides a brief but technically significant clarification distinguishing this patch from the earlier submission by Josh (referenced in the duplicate thread).

Key Technical Point

Implementation layer difference: The earlier patch by Josh placed the SQLSTATE-to-macro-name resolution logic in libpq (the client library), whereas this current patch places it in psql (the client application). Tom characterizes this as giving the current patch "a somewhat smaller blast radius for client compatibility problems."

This is architecturally significant because:

However, Tom notes the compatibility concern is "still not zero" even in psql — likely referring to the client-server version skew issue (psql compiled against one version's errcodes.txt connecting to a server running a different version).

Continued Concerns

Tom explicitly states that "the other comments I posted in the other thread still apply" — meaning he has unresolved concerns from the prior review that this submission has not addressed. The specifics of those comments are not restated here, but this signals the patch is not yet acceptable to a committer without further work.

History (1 prior analysis)
2026-05-25 · claude-opus-4-6

Technical Analysis: psql SQLSTATE Macro Name Display in Verbose Error Reports

Core Problem

When PostgreSQL's psql client displays verbose error messages (activated via \set VERBOSITY verbose or \errverbose), it includes the raw 5-character SQLSTATE code (e.g., 42601). While this code is defined by the SQL standard and is machine-readable, developers working on C extensions or internal backend code must manually cross-reference these codes against the symbolic macro names defined in PostgreSQL's source (e.g., ERRCODE_SYNTAX_ERROR). This lookup friction slows down development workflows, particularly when writing error-handling code in C that references these macros directly.

Proposed Solution

The patch introduces a mechanism to resolve SQLSTATE codes to their corresponding C macro names at runtime within psql, appending this information to verbose error output.

Architecture of the Patch

The implementation consists of three components:

  1. Code Generation via Perl Script: A new Perl script in src/bin/psql/ parses the authoritative src/backend/utils/errcodes.txt file (which is the single source of truth for all PostgreSQL error codes) and generates a C header file sqlstate_names.h containing a static mapping from 5-character SQLSTATE strings to their macro name strings.

  2. Build System Integration: The generation step is hooked into both the legacy Makefile and the meson.build systems, ensuring the header is regenerated when errcodes.txt changes.

  3. Runtime Lookup in psql: The error display paths (ExecQueryAndProcessResults for inline errors and exec_command_errverbose for the \errverbose command) are modified to perform a lookup against the generated mapping and append a line like SQLSTATE name: ERRCODE_SYNTAX_ERROR when verbosity is set to verbose.

Example Output (Before vs. After)

Before:

ERROR:  42601: syntax error at or near "FRO"
LINE 1: SELECT * FRO;
                 ^
LOCATION:  scanner_yyerror, scan.l:1220

After:

ERROR:  42601: syntax error at or near "FRO"
LINE 1: SELECT * FRO;
                 ^
LOCATION:  scanner_yyerror, scan.l:1220
SQLSTATE name: ERRCODE_SYNTAX_ERROR

Technical Considerations and Tradeoffs

Design Decisions

  • Code generation approach: Rather than hardcoding a mapping or querying the server, the patch generates a static lookup table from the canonical errcodes.txt. This is architecturally consistent with how PostgreSQL already generates errcodes.h for the backend — it reuses the same source of truth.

  • Client-side resolution: The mapping lives in psql itself rather than being served by the backend. This means psql's mapping could potentially drift from the server's actual codes if connected to a different PostgreSQL version. However, since SQLSTATE codes are rarely removed and the symbolic names are stable, this is a minor concern.

  • Perl dependency: The code generation adds a build-time Perl dependency for psql, though Perl is already required for PostgreSQL's build process in numerous other places.

Potential Concerns

  1. Duplicate submission: As noted in the thread, this patch appears to be identical to a previously submitted patch, which raises process concerns about whether feedback from the earlier submission has been incorporated.

  2. Maintenance burden: A new generated header adds complexity to the build system. Whether this quality-of-life improvement justifies the added build infrastructure is a judgment call.

  3. Output format: The label "SQLSTATE name:" is somewhat ambiguous — it's actually the C macro name, not the "name" of the SQLSTATE in any standards sense. A label like "SQLSTATE macro:" might be more precise.

  4. Lookup performance: For a static mapping of ~200-300 error codes, even a linear scan is negligible in the context of error display. However, the implementation details (hash table, sorted array with binary search, or linear scan) aren't specified in the thread.

Status

The patch appears to be a resubmission of an earlier proposal. The thread was quickly flagged as a duplicate by Jian He, pointing to the prior submission. No substantive technical review has occurred in this thread iteration, and the discussion is likely to be redirected to the original thread.

Relationship to Existing Infrastructure

PostgreSQL already has src/backend/utils/errcodes.txt as the canonical source, with multiple code generation scripts that produce:

  • src/include/utils/errcodes.h (C macros for backend use)
  • src/pl/plpgsql/src/plerrcodes.h (PL/pgSQL error codes)
  • Various documentation files

This patch would add another consumer of that file, which is a well-established pattern in the PostgreSQL codebase.