public inbox for [email protected]
help / color / mirror / Atom feedTodo item: Include the symbolic SQLSTATE name in verbose error reports
2+ messages / 2 participants
[nested] [flat]
* Todo item: Include the symbolic SQLSTATE name in verbose error reports
@ 2026-04-01 13:44 Josh Kupershmidt <[email protected]>
2026-05-25 18:58 ` Re: Todo item: Include the symbolic SQLSTATE name in verbose error reports Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Josh Kupershmidt @ 2026-04-01 13:44 UTC (permalink / raw)
To: pgsql-hackers
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']
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Todo item: Include the symbolic SQLSTATE name in verbose error reports
2026-04-01 13:44 Todo item: Include the symbolic SQLSTATE name in verbose error reports Josh Kupershmidt <[email protected]>
@ 2026-05-25 18:58 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2026-05-25 18:58 UTC (permalink / raw)
To: Josh Kupershmidt <[email protected]>; +Cc: pgsql-hackers
Josh Kupershmidt <[email protected]> writes:
> Attached is a patch implementing an old feature request on the wiki's Todo:
> "Include the symbolic SQLSTATE name in verbose error reports"
I kind of doubt that this is actually useful (which presumably is
the reason the TODO item has languished uncompleted for so long).
The first problem is that the SQL spec's list of error codes isn't
very granular, so that there are many cases where different errors
with different messages have been mapped to the same SQLSTATE.
You are way better off to read the message text than to believe
that the SQLSTATE is a useful summary.
The second problem is that the proposed implementation relies on
libpq to know the server's list of SQLSTATEs, with obvious risks
for cross-version skew.
I also wonder if this wouldn't break some applications that are
expecting specific output from psql, or for that matter from
anything else relying on libpq's error message formatting.
No doubt such apps could be fixed, but the cost/benefit ratio
just doesn't seem all that attractive.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-05-25 18:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-01 13:44 Todo item: Include the symbolic SQLSTATE name in verbose error reports Josh Kupershmidt <[email protected]>
2026-05-25 18:58 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox