Re: Practical sets of SQLSTATE values?

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> In my mind, distinct error codes are only useful if the application can
> react differently to the condition.

Agreed, we do not want to divide the error codes too finely. However,
we had a request on the lists just today for an error-code-based way to
detect whether the server failed because of running out of disk space,
and that wasn't by any means the first such request. So "out of disk
space" definitely deserves its own SQLSTATE, IMHO.

A nice property of the SQLSTATE design is that even if an application
doesn't recognize the exact code, it probably can recognize the
category (the first two characters), and the category is usually enough
to give it an idea of whether it can do anything useful or not. So for
example, as long as "no such function" is under the 42xxx (syntax error
or access rule violation) category, it shouldn't be a big problem for
applications to understand it well enough for their purposes. This is
specifically intended by the spec writers, I think, in view of this
note in SQL99:

NOTE 356 - One consequence of this is that an SQL-implementation
may, but is not required by ISO/IEC 9075 to, provide subcodes for
exception condition syntax error or access rule violation that
distinguish between the syntax error and access rule violation
cases.

We should probably expend more care on making sure we have the
categories right than on worrying about which errors deserve their
own subcodes. I also wonder whether we shouldn't explicitly document
someplace "if you don't recognize an XXYYY SQLSTATE, you may treat it
as XX000 instead".

regards, tom lane