public inbox for [email protected]
help / color / mirror / Atom feedFrom: Aaryan Parik <[email protected]>
To: [email protected]
Subject: [PATCH] psql: Display SQLSTATE macro name in verbose error reports
Date: Sun, 24 May 2026 00:48:22 +0530
Message-ID: <CAFAL27jsbbRkjj=zwudUU7vpmNHDgRCQXYEv_qx6D9uc7LLYvQ@mail.gmail.com> (raw)
Hi hackers,
Currently, when psql displays verbose error messages (\set VERBOSITY
verbose or \errverbose), it prints the 5-character SQLSTATE code. However,
developers often have to manually look up the corresponding C macro name
(e.g. ERRCODE_SYNTAX_ERROR) in the source code when writing C extensions or
dealing with internal backend code.
Attached is a small patch that improves the verbose error output in psql by
appending the symbolic SQLSTATE macro name to the end of the error report.
*Changes included in this patch:*
*1. Created a small Perl script in src/bin/psql to parse
src/backend/utils/errcodes.txt and generate a C header (sqlstate_names.h)
containing a mapping of SQLSTATE codes to their macro names.2. Hooked the
generation of this header into the psql Makefile and meson.build.3. Updated
psql's error reporting (ExecQueryAndProcessResults and
exec_command_errverbose) to perform a lookup and append the macro name if
VERBOSITY is verbose.*
Example output:
ERROR: 42601: syntax error at or near "FRO"
LINE 1: SELECT * FRO;
^
LOCATION: scanner_yyerror, scan.l:1220
SQLSTATE name: ERRCODE_SYNTAX_ERROR
I believe this will be a handy quality-of-life improvement for developers.
Looking forward to any feedback
Best regards,
Aaryan Parik
Attachments:
[application/octet-stream] psql-sqlstate-macro-name.patch (6.9K, 3-psql-sqlstate-macro-name.patch)
download | inline diff:
From 4098c6d1a27f60027b9e4afa5d3ace98cd969712 Mon Sep 17 00:00:00 2001
From: Aaryan Parik <[email protected]>
Date: Sat, 23 May 2026 19:10:57 +0000
Subject: [PATCH] psql: Display SQLSTATE macro name in verbose error reports
---
src/bin/psql/Makefile | 7 ++-
src/bin/psql/command.c | 9 ++++
src/bin/psql/common.c | 28 ++++++++++
src/bin/psql/generate-errcode-names.pl | 71 ++++++++++++++++++++++++++
src/bin/psql/meson.build | 11 ++++
5 files changed, 125 insertions(+), 1 deletion(-)
create mode 100644 src/bin/psql/generate-errcode-names.pl
diff --git a/src/bin/psql/Makefile b/src/bin/psql/Makefile
index be00326..3b66ec1 100644
--- a/src/bin/psql/Makefile
+++ b/src/bin/psql/Makefile
@@ -64,6 +64,11 @@ psqlscanslash.c: FLEX_NO_BACKUP=yes
tab-complete.c: gen_tabcomplete.pl tab-complete.in.c
$(PERL) $^ --outfile $@
+common.o command.o: sqlstate_names.h
+
+sqlstate_names.h: generate-errcode-names.pl $(top_srcdir)/src/backend/utils/errcodes.txt
+ $(PERL) $^ $@
+
install: all installdirs
$(INSTALL_PROGRAM) psql$(X) '$(DESTDIR)$(bindir)/psql$(X)'
$(INSTALL_DATA) $(srcdir)/psqlrc.sample '$(DESTDIR)$(datadir)/psqlrc.sample'
@@ -77,7 +82,7 @@ uninstall:
clean distclean:
rm -f psql$(X) $(OBJS) lex.backup
rm -rf tmp_check
- rm -f sql_help.h sql_help.c psqlscanslash.c tab-complete.c
+ rm -f sql_help.h sql_help.c psqlscanslash.c tab-complete.c sqlstate_names.h
check:
$(prove_check)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 01b8f11..7d2731b 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -28,6 +28,7 @@
#include "command.h"
#include "common.h"
#include "common/logging.h"
+#include "sqlstate_names.h"
#include "common/string.h"
#include "copy.h"
#include "describe.h"
@@ -1656,6 +1657,14 @@ exec_command_errverbose(PsqlScanState scan_state, bool active_branch)
{
pg_log_error("%s", msg);
PQfreemem(msg);
+
+ const char *sqlstate = PQresultErrorField(pset.last_error_result, PG_DIAG_SQLSTATE);
+ if (sqlstate)
+ {
+ const char *sym = get_sqlstate_symbolic_name(sqlstate);
+ if (sym)
+ pg_log_error("SQLSTATE name: %s\n", sym);
+ }
}
else
puts(_("out of memory"));
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 1a4e2ea..4a54a08 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -22,6 +22,7 @@
#include "command.h"
#include "common.h"
#include "common/logging.h"
+#include "sqlstate_names.h"
#include "copy.h"
#include "crosstabview.h"
#include "fe_utils/cancel.h"
@@ -454,8 +455,21 @@ AcceptResult(const PGresult *result, bool show_error)
const char *error = PQerrorMessage(pset.db);
if (strlen(error))
+ {
pg_log_info("%s", error);
+ if (pset.verbosity == PQERRORS_VERBOSE)
+ {
+ const char *sqlstate = PQresultErrorField(result, PG_DIAG_SQLSTATE);
+ if (sqlstate)
+ {
+ const char *sym = get_sqlstate_symbolic_name(sqlstate);
+ if (sym)
+ pg_log_info("SQLSTATE name: %s\n", sym);
+ }
+ }
+ }
+
CheckConnection();
}
@@ -1784,8 +1798,22 @@ ExecQueryAndProcessResults(const char *query,
const char *error = PQresultErrorMessage(result);
if (strlen(error))
+ {
pg_log_info("%s", error);
+ if (pset.verbosity == PQERRORS_VERBOSE)
+ {
+ const char *sqlstate = PQresultErrorField(result, PG_DIAG_SQLSTATE);
+ if (sqlstate)
+ {
+ const char *sym = get_sqlstate_symbolic_name(sqlstate);
+ if (sym)
+ pg_log_info("SQLSTATE name: %s\n", sym);
+ }
+ }
+ }
+
+
CheckConnection();
if (!is_watch)
SetResultVariables(result, false);
diff --git a/src/bin/psql/generate-errcode-names.pl b/src/bin/psql/generate-errcode-names.pl
new file mode 100644
index 0000000..80d5087
--- /dev/null
+++ b/src/bin/psql/generate-errcode-names.pl
@@ -0,0 +1,71 @@
+#!/usr/bin/perl
+#
+# Generate the sqlstate_names.h header from errcodes.txt
+# This maps the 5-character SQLSTATE code to its symbolic macro name for psql's verbose error reports.
+
+use strict;
+use warnings;
+
+my $infile = shift;
+my $outfile = shift;
+
+open my $in, '<', $infile or die "Could not open $infile: $!";
+open my $out, '>', $outfile or die "Could not open $outfile: $!";
+
+print $out "/* autogenerated from src/backend/utils/errcodes.txt, do not edit */\n";
+print $out "/* This file maps SQLSTATE codes to their symbolic names. */\n\n";
+
+print $out "static const char *\n";
+print $out "get_sqlstate_symbolic_name(const char *sqlstate)\n";
+print $out "{\n";
+print $out " if (sqlstate == NULL)\n";
+print $out " return NULL;\n\n";
+
+my %sqlstates;
+
+while (<$in>) {
+ chomp;
+ s/#.*//; # Remove comments
+ next if /^\s*$/; # Skip empty lines
+ next if /^Section:/; # Skip section headers
+
+ if (/^([^\s]{5})\s+[EWS]\s+([^\s]+)/) {
+ my $sqlstate = $1;
+ my $macro_name = $2;
+ $sqlstates{$sqlstate} = $macro_name;
+ }
+}
+
+# We use a simple if-else chain. Since there are around 200-300 codes, a switch statement
+# based on strings isn't natively supported in C, but a series of if-strcmp is fast enough
+# for error reporting, or we can use a small static array and binary search it.
+# A static array and binary search is cleaner and scales better.
+
+print $out " static const struct {\n";
+print $out " const char sqlstate[6];\n";
+print $out " const char *macro_name;\n";
+print $out " } errcodes[] = {\n";
+
+foreach my $sqlstate (sort keys %sqlstates) {
+ print $out " {\"$sqlstate\", \"$sqlstates{$sqlstate}\"},\n";
+}
+
+print $out " };\n\n";
+print $out " int low = 0;\n";
+print $out " int high = (sizeof(errcodes) / sizeof(errcodes[0])) - 1;\n\n";
+print $out " while (low <= high) {\n";
+print $out " int mid = low + (high - low) / 2;\n";
+print $out " int cmp = strcmp(errcodes[mid].sqlstate, sqlstate);\n";
+print $out " if (cmp == 0)\n";
+print $out " return errcodes[mid].macro_name;\n";
+print $out " else if (cmp < 0)\n";
+print $out " low = mid + 1;\n";
+print $out " else\n";
+print $out " high = mid - 1;\n";
+print $out " }\n\n";
+
+print $out " return NULL;\n";
+print $out "}\n";
+
+close $in;
+close $out;
diff --git a/src/bin/psql/meson.build b/src/bin/psql/meson.build
index 922b284..8a11de7 100644
--- a/src/bin/psql/meson.build
+++ b/src/bin/psql/meson.build
@@ -34,6 +34,17 @@ tabcomplete = custom_target('tabcomplete',
generated_sources += tabcomplete
psql_sources += tabcomplete
+sqlstate_names = custom_target('sqlstate_names',
+ input: '@SOURCE_ROOT@/src/backend/utils/errcodes.txt',
+ output: 'sqlstate_names.h',
+ command: [
+ perl, files('generate-errcode-names.pl'), '@INPUT@', '@OUTPUT@',
+ ],
+)
+generated_sources += sqlstate_names
+psql_sources += sqlstate_names
+
+
sql_help = custom_target('psql_help',
output: ['sql_help.c', 'sql_help.h'],
depfile: 'sql_help.dep',
--
2.53.0
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH] psql: Display SQLSTATE macro name in verbose error reports
In-Reply-To: <CAFAL27jsbbRkjj=zwudUU7vpmNHDgRCQXYEv_qx6D9uc7LLYvQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox