Обсуждение: Interpreting PostgreSQL error messages

This is a general question about dealing with PostgreSQL error messages
in programs using client interfaces to PostgreSQL. What is the best way 
to process the error messages and present them to users of for instance, 
a web application, in a user friendly way?

Let's take a concrete example: An e-shop application, and the database 
has a table "customers" and tables "orders" and "invoices" with foreign 
key constraints on customers. The e-shop has a CGI program for 
maintaining customers that also allows deleting customers if they have 
no pending orders or invoices.

The code could look like this:

----------------------------------------------

try:
    delete from customers where customer_id=$id

exception:
    if postgres_error_message.ends_with \        "key in customers still referenced from orders":
        print "There are still orders from the customer!"
    else if postgres_error_message.ends_with \        "key in customers still referenced from invoices":
        print "There are still invoices for the customer!"
    else:
        print postgres_error_message

----------------------------------------------

You get the idea: If there is an error like the violation
of a foreign key constraint, the program will catch and
"translate" the error message for the usage, printing a
more a user friendly message instead of bothering you with
database specific details and terms like "constraint",
"table", "key" etc. The most frequent and likely error
situations are caught like this. Only if the program cannot
interpret the error message, it will fall back to printing
out the original PostgreSQL error message.

I think this is quite elegant and easy for the programmer,
but there is a big drawback:

1) The error messages can vary with the PostgreSQL version.
2) The error messages also depend on the localization.

One could resolve 2) by forcing lc_messages = 'C' in
postgresql.conf, but then the user would be confronted
with English messages instead of the localized messages
in the "fallback" case.

Is there any other elegant solution?

I think it would be nice to have some normed "machine readable"
kind of error message besides the "humand readable" message
that PostgreSQL provides.

I know there is a "SQLSTATE" code and the doco says:
"Applications that need to know which error condition has occurred
should usually test the error code, rather than looking at the textual
error message. The error codes are less likely to change across
PostgreSQL releases, and also are not subject to change due to
localization of error messages. "

But first, SQLSTATE is not accesible from many programming interfaces,
and second, anyway it would not help because it would not contain a hint
about for instance, which foreign key constraint has been violated.

Any ideas or comments on this?

-- Christoph

Christoph Zwerschke <cito@online.de> writes:
> I know there is a "SQLSTATE" code and the doco says:
> "Applications that need to know which error condition has occurred
> should usually test the error code, rather than looking at the textual
> error message. The error codes are less likely to change across
> PostgreSQL releases, and also are not subject to change due to
> localization of error messages. "

> But first, SQLSTATE is not accesible from many programming interfaces,

So you need to use a less broken interface...

> and second, anyway it would not help because it would not contain a hint
> about for instance, which foreign key constraint has been violated.

The SQL spec thinks that error reports should contain additional
optional fields, such as "name of affected table" and "name of affected
column" for a column constraint violation error.  (I'm handwaving here
but that's the general idea.  The SQLSTATE code would tell you which
additional fields are available.)  Providing such fields would eliminate
the need for client-side code to parse human-readable messages.  We have
not got round to implementing any of this yet, but the protocol and
backend error-handling infrastructure are prepared to deal with extra
fields of this ilk.

Obviously, whenever that does happen, you'll need an even less broken
client library to get at the extra fields.  So if you open the bidding
by saying "I won't use a tool that has heard of SQLSTATE", the answer
is going to be "tough luck".
        regards, tom lane

The future enhancements sound good. That's definitely something that 
will be very useful, and yes, interfaces should provide new 
functionality as soon as possible.

But what is the best way to solve the mentioned problem right now?

-- Christoph