public inbox for [email protected]
help / color / mirror / Atom feedFrom: Josh Kupershmidt <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Todo item: Include the symbolic SQLSTATE name in verbose error reports
Date: Wed, 1 Apr 2026 09:44:15 -0400
Message-ID: <CAK3UJRGc2RKfQMjwVDAwsuOJuobq3d4+Qf9KWK1NHmK1vaqyxQ@mail.gmail.com> (raw)
Hi,
Attached is a patch implementing an old feature request on the wiki's Todo:
"Include the symbolic SQLSTATE name in verbose error reports"
The output should only be different in verbose mode, i.e. if you:
\set VERBOSITY verbose
The old output in verbose mode would look like:
# SELECT * FROM nonexistent_table;
ERROR: 42P01: relation "nonexistent_table" does not exist
LINE 1: SELECT * FROM nonexistent_table;
LOCATION: parserOpenTable, parse_relation.c:1461
The new output looks like:
# select * FROM nonexistent;
ERROR: 42P01 (ERRCODE_UNDEFINED_TABLE): relation "nonexistent" does not
exist
LINE 1: select * FROM nonexistent;
^
LOCATION: parserOpenTable, parse_relation.c:1461
so that we tell the user clearly what "42P01" means, in this example.
Cheers,
Josh
Attachments:
[application/octet-stream] include-symbolic-SQLSTATE-name-in-verbose-mode-output.patch (5.2K, 3-include-symbolic-SQLSTATE-name-in-verbose-mode-output.patch)
download | inline diff:
diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt
index 5b25402ebbe..98ff688ee27 100644
--- a/src/backend/utils/errcodes.txt
+++ b/src/backend/utils/errcodes.txt
@@ -18,6 +18,9 @@
# src/pl/tcl/pltclerrcodes.h
# the same, for PL/Tcl
#
+# src/interfaces/libpq/sqlstate_names.h
+# a lookup table mapping SQLSTATE codes to symbolic names for libpq
+#
# doc/src/sgml/errcodes-table.sgml
# a SGML table of error codes for inclusion in the documentation
#
diff --git a/src/backend/utils/generate-sqlstate-names.pl b/src/backend/utils/generate-sqlstate-names.pl
new file mode 100644
index 00000000000..99e7a9b36a6
--- /dev/null
+++ b/src/backend/utils/generate-sqlstate-names.pl
@@ -0,0 +1,100 @@
+#!/usr/bin/perl
+#
+# Generate the sqlstate_names.h header from errcodes.txt
+# Copyright (c) 2000-2026, PostgreSQL Global Development Group
+
+use strict;
+use warnings FATAL => 'all';
+use Getopt::Long;
+
+my $outfile = '';
+
+GetOptions('outfile=s' => \$outfile) or die "$0: wrong arguments";
+
+open my $errcodes, '<', $ARGV[0]
+ or die "$0: could not open input file '$ARGV[0]': $!\n";
+
+my $outfh;
+if ($outfile)
+{
+ open $outfh, '>', $outfile
+ or die "$0: could not open output file '$outfile': $!\n";
+}
+else
+{
+ $outfh = *STDOUT;
+}
+
+my @entries;
+
+while (<$errcodes>)
+{
+ chomp;
+
+ # Skip comments and empty lines
+ next if /^#/;
+ next if /^\s*$/;
+
+ # Skip section headers
+ next if /^Section:/;
+
+ die "unable to parse errcodes.txt"
+ unless /^([^\s]{5})\s+[EWS]\s+([^\s]+)/;
+
+ push @entries, [$1, $2];
+}
+
+close $errcodes;
+
+# Sort by SQLSTATE
+@entries = sort { $a->[0] cmp $b->[0] } @entries;
+
+print $outfh <<'HEADER';
+/* autogenerated from src/backend/utils/errcodes.txt, do not edit */
+#ifndef SQLSTATE_NAMES_H
+#define SQLSTATE_NAMES_H
+
+#include <string.h>
+
+typedef struct
+{
+ const char sqlstate[6];
+ const char *symbolic_name;
+} SqlstateNameEntry;
+
+static const SqlstateNameEntry sqlstate_name_table[] = {
+HEADER
+
+for my $e (@entries)
+{
+ print $outfh "\t{\"$e->[0]\", \"$e->[1]\"},\n";
+}
+
+print $outfh <<'FOOTER';
+};
+
+static inline const char *
+sqlstate_to_symbolic_name(const char *sqlstate)
+{
+ int lo = 0;
+ int hi = sizeof(sqlstate_name_table) / sizeof(sqlstate_name_table[0]) - 1;
+
+ while (lo <= hi)
+ {
+ int mid = (lo + hi) / 2;
+ int cmp = strncmp(sqlstate, sqlstate_name_table[mid].sqlstate, 5);
+
+ if (cmp < 0)
+ hi = mid - 1;
+ else if (cmp > 0)
+ lo = mid + 1;
+ else
+ return sqlstate_name_table[mid].symbolic_name;
+ }
+ return NULL;
+}
+
+#endif /* SQLSTATE_NAMES_H */
+FOOTER
+
+close $outfh if ($outfile);
diff --git a/src/interfaces/libpq/Makefile b/src/interfaces/libpq/Makefile
index 0963995eed4..af06d6733e6 100644
--- a/src/interfaces/libpq/Makefile
+++ b/src/interfaces/libpq/Makefile
@@ -146,6 +146,12 @@ endif
fe-connect.o: fe-connect.c $(top_builddir)/src/port/pg_config_paths.h
fe-misc.o: fe-misc.c $(top_builddir)/src/port/pg_config_paths.h
+# generate sqlstate_names.h from src/backend/utils/errcodes.txt
+sqlstate_names.h: $(top_srcdir)/src/backend/utils/errcodes.txt $(top_srcdir)/src/backend/utils/generate-sqlstate-names.pl
+ $(PERL) $(top_srcdir)/src/backend/utils/generate-sqlstate-names.pl --outfile $@ $<
+
+fe-protocol3.o: fe-protocol3.c sqlstate_names.h
+
$(top_builddir)/src/port/pg_config_paths.h:
$(MAKE) -C $(top_builddir)/src/port pg_config_paths.h
@@ -188,5 +194,6 @@ clean distclean: clean-lib
$(MAKE) -C test $@
rm -rf tmp_check
rm -f $(OBJS) $(OBJS_SHLIB) $(OBJS_STATIC) pthread.h libpq-refs-stamp
+ rm -f sqlstate_names.h
# Might be left over from a Win32 client-only build
rm -f pg_config_paths.h
diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c
index 8c1fda5caf0..04a3c9a9ece 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -28,6 +28,7 @@
#include "common/int.h"
#include "libpq-fe.h"
#include "libpq-int.h"
+#include "sqlstate_names.h"
#include "mb/pg_wchar.h"
#include "port/pg_bswap.h"
@@ -1080,7 +1081,14 @@ pqBuildErrorMessage3(PQExpBuffer msg, const PGresult *res,
{
val = PQresultErrorField(res, PG_DIAG_SQLSTATE);
if (val)
- appendPQExpBuffer(msg, "%s: ", val);
+ {
+ const char *symbolic = sqlstate_to_symbolic_name(val);
+
+ if (symbolic)
+ appendPQExpBuffer(msg, "%s (%s): ", val, symbolic);
+ else
+ appendPQExpBuffer(msg, "%s: ", val);
+ }
}
val = PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY);
if (val)
diff --git a/src/interfaces/libpq/meson.build b/src/interfaces/libpq/meson.build
index b0ae72167a1..d847d5c4414 100644
--- a/src/interfaces/libpq/meson.build
+++ b/src/interfaces/libpq/meson.build
@@ -42,6 +42,15 @@ export_file = custom_target('libpq.exports',
kwargs: gen_export_kwargs,
)
+gen_sqlstate_names = files('../../backend/utils/generate-sqlstate-names.pl')
+sqlstate_names_h = custom_target('sqlstate_names',
+ input: ['../../backend/utils/errcodes.txt'],
+ output: ['sqlstate_names.h'],
+ command: [perl, gen_sqlstate_names, '--outfile', '@OUTPUT@', '@INPUT0@'],
+)
+generated_sources += sqlstate_names_h
+libpq_sources += sqlstate_names_h
+
# port needs to be in include path due to pthread-win32.h
libpq_inc = include_directories('.', '../../port')
libpq_c_args = ['-DSO_MAJOR_VERSION=5']
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: Todo item: Include the symbolic SQLSTATE name in verbose error reports
In-Reply-To: <CAK3UJRGc2RKfQMjwVDAwsuOJuobq3d4+Qf9KWK1NHmK1vaqyxQ@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