Re: Upgrading the backend's error-message infrastructure

johnnnnnn <john(at)phaedrusdeinus(dot)org> writes:
> If it's different enough to warrant a different message, then, in my
> mind, it's different enough to warrant a different SQLSTATE.

Unfortunately, you're at odds with the SQL spec authors, who have made
their intentions pretty clear by defining only about 130 standard
SQLSTATEs: the granularity is supposed to be coarse. To take one
example, there's just a single SQLSTATE for "division by zero". One
might or might not want different messages for float vs integer zero
divide, but they're going to have the same SQLSTATE.

My feeling is that the spec authors knew what they were doing, at least
for the purposes they intended SQLSTATE to be used for. Applications
want to detect errors at a granularity corresponding to what their
recovery choices might be. For example, apps want to distinguish
"unique key violation" from "zero divide" because they probably have
something they can do about a unique-key problem. They *don't* want
"unique key violation" to be broken down into forty-seven subvariants
(especially not implementation-specific subvariants) because that just
makes it difficult to detect the condition reliably --- it's almost as
bad as having to look at an error message text.

We could possibly invent a unique code for each message that is separate
from SQLSTATE, but that idea was considered and rejected some time ago
for what seem to me good reasons: it adds a lot of
bookkeeping/maintenance effort for far too little return. Ultimately,
the source code is the authoritative database for the set of possible
errors, and trying to put that authority someplace else is just not
worth the effort. (Besides, we already have tools that can extract
information from the source code at need --- gettext does exactly this
to prepare the NLS files.)

>> It's also unclear how you insert parameters into error strings if
>> you do this.

> That's valid, but there are other ways of dealing with it. The
> position in the SQL statement has been moved out to another item in
> the response, so why not move the table, column, index, or whatnot
> into another item(s) as well?

Because then the reassembly becomes the front-end's problem. This was
in fact an approach I proposed a year or two back, and it was
(correctly, in hindsight) shot down. We have multiple frontend libraries
and only one backend, so it's better to do this sort of thing once in
the backend. There is not enough payback from making each frontend have
to implement it. There is a good reason for separating out position ---
different frontends are going to want to handle syntax-error marking
differently (consider psql vs some kind of windowed GUI). But there's
no corresponding bang for the buck in making every frontend handle
localization issues.

regards, tom lane