Re: the case for machine-readable error fields

On Tue, Aug 4, 2009 at 4:05 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Tom Lane wrote:
>>> Lastly, I'm not as sure as you are that the case for these is well made.
>>> In exactly what cases would client code be able to do something useful
>>> with them?  Your proposal involves a pretty huge amount of work if we
>>> are to carry it out thoroughly, and I'm 100% not convinced that there's
>>> a proportional benefit.
>
>> Hmm, well, I skipped the rationale because it has been requested before.
>> For example, we need to give constraint names so that applications can
>> tell which unique key is being violated.  We need table names on which
>> they are being violated.  We need column names for datatype mismatches,
>> and so on.  We frequently see people parsing the error message to
>> extract those, but that is known to be fragile, cumbersome and error
>> prone.
>
> Frankly, I don't believe it.  I've seen possibly one or two requests
> for such things.  That's not enough interest to justify the kind of
> work and code-size investment you're talking about.
>
> If there are situations where this info is missing from the
> human-readable message, then sure, let's look into fixing that.
> But the use-case for automatic interpretation of the message
> is just a whole lot smaller than would justify the work.
> To take just one point, I rather doubt that SQLSTATE codes are really
> sufficiently fine-grained to let applications automatically determine
> what to do without looking at the message text.

I'm not sure whether the code complexity is justified because I don't
know how much code complexity is involved. But as a guy who writes a
lot of web applications, I do know that the need to parse error
messages is frequent. I tend to make it the sole responsibility of
the database to report things like foreign key violations. For
example, a user tries to delete an object via a web interface and it
fails because of a foreign key violation. I need to tell him (in
English) which foreign key constraint he violated, with a message
something like this:

This <object-type> may not be deleted because it is still being used
by one or more <other-object-type-in-plural-form>.

Right now, I do this like this:

if ($err =~ /name_of_first_foreign_key/) {
$r->error_exit('First error message.')
}
elsif ($err =~ /name_of_second_foreign_key/) {
...

I've always found that a bit icky because it relies (for example) on
their not being two constraint names such that the first is a prefix
of the second, and on there not being any other data in the error
string which can be confused with the constraint name. But I can't
say I've ever had any serious problems as a result of this ickiness...
I just make sure my constraint names are long and wordy enough to
prevent confusion.

...Robert