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.