Investigating a psql error

Error messages can sometimes be cryptic, and you may be left wondering why did this error happen at all?

For this purpose, psql recognizes two variables, VERBOSITY and CONTEXT; valid values are terse, default, or verbose for the former, and never, errors, or always for the latter. A more verbose error message will hopefully specify extra detail, and the context information will be included. Here is an example to show the difference:

postgres=# \set VERBOSITY terse
postgres=# \set CONTEXT never
postgres=# select * from missingtable;
ERROR:  relation "missingtable" does not exist at character 15

This is quite a simple error, so we don't actually need the extra detail, but it is nevertheless useful for illustrating the extra detail you get when raising verbosity and enabling context information:

postgres=# \set VERBOSITY verbose
postgres=# \set CONTEXT errors
postgres=# select * from missingtable;
ERROR:  42P01: relation "missingtable" does not exist
LINE 1: select * from missingtable;
                      ^
LOCATION:  parserOpenTable, parse_relation.c:1159

Now you get the SQL error code 42P01, which you can look up in the PostgreSQL manual, and even a reference to the file and the line in the PostgreSQL source code where this error is raised, so you can investigate it (the beauty of open source!).

However, there is a problem with having to enable verbosity in advance: you need to do it before running the command. If all errors were reproducible, this would not be a huge inconvenience. But in certain cases you may hit a transient error, such as a serialization failure, which is difficult to detect itself; and it could sometimes happen that you struggle to reproduce the error, let alone analyze it.

The \errverbose metacommand in psql was introduced precisely to avoid these problems.