Technical Analysis: Including Symbolic SQLSTATE Names in Verbose Error Reports

Core Problem

PostgreSQL's verbose error reporting mode (activated via \set VERBOSITY verbose in psql) displays the 5-character SQLSTATE code alongside error messages. For example, 42P01 appears before the message text. However, these codes are opaque to most developers—few people have memorized the mapping between codes like 42P01 and their symbolic meanings (ERRCODE_UNDEFINED_TABLE). The proposal is to augment verbose output by appending the symbolic C-level constant name to make the code immediately interpretable without requiring a documentation lookup.

This is a long-standing item on the PostgreSQL wiki's Todo list, indicating community awareness of the usability gap but also suggesting low priority or unresolved design concerns.

Proposed Solution

Josh Kupershmidt's patch modifies the error formatting path to include the symbolic SQLSTATE name in parentheses after the numeric code in verbose mode:

ERROR:  42P01 (ERRCODE_UNDEFINED_TABLE): relation "nonexistent" does not exist

The implementation likely adds a lookup table or function in libpq (client-side) that maps the 5-character SQLSTATE string to its corresponding symbolic constant name (e.g., ERRCODE_UNDEFINED_TABLE). This mapping would be derived from src/backend/utils/errcodes.txt or the generated header src/include/utils/errcodes.h.

Key Technical Concerns and Tradeoffs

1. Granularity Limitation of SQLSTATE Codes

Tom Lane raises a fundamental conceptual issue: the SQL standard's SQLSTATE system is intentionally coarse-grained. Multiple distinct error conditions map to the same SQLSTATE code. For example, many different constraint violations might share 23000 or related codes. This means the symbolic name can actually be misleading—it suggests a specific meaning when the actual error might be a different condition that happens to share the same code due to the spec's limited taxonomy. The error message text itself is far more informative than the SQLSTATE symbolic name.

2. Cross-Version Skew Problem (Architectural)

This is the most architecturally significant concern. If the SQLSTATE-to-name mapping lives in libpq (the client library), then there's an inherent version coupling problem:

• A newer server might define new SQLSTATE codes that an older libpq doesn't know about
• A server might reclassify errors between versions (rare but possible)
• The client would need to be updated in lockstep with server changes

This violates PostgreSQL's careful maintenance of cross-version compatibility between libpq and servers. The alternative—having the server send the symbolic name—would require protocol changes or additional fields in the error response, which is a much heavier lift for marginal benefit.

3. Output Stability / Backward Compatibility

Applications that parse psql's verbose output (testing frameworks, log analyzers, CI systems) could break if the format changes. While verbose mode is less likely to be parsed than standard mode, it's not zero-risk. The cost/benefit calculation is unfavorable: the benefit is minor (a developer convenience that a quick grep of errcodes.h could provide), while the cost includes potential breakage and ongoing maintenance of the mapping.

Assessment

Tom Lane's response is effectively a rejection of the approach on multiple grounds. The combination of (a) limited practical utility due to SQLSTATE granularity issues, (b) architectural concerns about client-server version coupling, and (c) backward compatibility risks creates a strong case against inclusion. The fact that this Todo item existed for years without implementation is itself diagnostic—it's a feature that sounds useful in the abstract but has significant practical downsides.

A more viable alternative (not discussed in thread) might be to have the server optionally include the symbolic name in a new PGresult field, avoiding the cross-version problem, but this would require protocol-level changes that are disproportionate to the feature's value.