the case for machine-readable error fields

Alvaro Herrera <alvherre@commandprompt.com> writes:

First we need several new error message fields: table name, function
name, constraint name, and so on.

It would also help to have clear definitions of what these *mean*, which
is entirely unclear from your comments --- in particular, the reference
to errcontext callbacks confuses the heck out of me. I would have
thought that these would be used for the referenced object name in cases
like "table not found", and surely using an errcontext callback for that
would be the hardest possible way to implement it.

... would be to give each new field its own start letter (see
http://www.postgresql.org/docs/8.4/static/protocol-error-fields.html);
say "T" for table, "f" for function (F is taken), "c" for constraint (C
is taken), and so on. Another possibility would be to use a single
letter, say N, and add a subtype to it; so table name would be "NT"
followed by the table name, NF for functions, etc.

Without a pretty concrete list of what the additions are going to be,
it's difficult to make any reasoned choices there.

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.

regards, tom lane

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

First we need several new error message fields: table name, function
name, constraint name, and so on.

It would also help to have clear definitions of what these *mean*, which
is entirely unclear from your comments --- in particular, the reference
to errcontext callbacks confuses the heck out of me. I would have
thought that these would be used for the referenced object name in cases
like "table not found", and surely using an errcontext callback for that
would be the hardest possible way to implement it.

Huh, yeah, certainly there are cases where the errtable() call is going
to be directly in the ereport() call instead of errcontext, but in some
other cases (for example when reporting problems in functions) we're
going to need errcontext.

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.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.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.

regards, tom lane

2009/8/4 Tom Lane <tgl@sss.pgh.pa.us>:

Alvaro Herrera <alvherre@commandprompt.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 can see sense of special err attrib for constraints, table and
columns. This should to help with error procession on application
level. This is language independent and +/- more stable than error
messages.

regards
Pavel Stehule

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane wrote:

It would also help to have clear definitions of what these *mean*, which
is entirely unclear from your comments --- in particular, the reference
to errcontext callbacks confuses the heck out of me. I would have
thought that these would be used for the referenced object name in cases
like "table not found", and surely using an errcontext callback for that
would be the hardest possible way to implement it.

Huh, yeah, certainly there are cases where the errtable() call is going
to be directly in the ereport() call instead of errcontext, but in some
other cases (for example when reporting problems in functions) we're
going to need errcontext.

Also, you completely dodged the question of defining what the fields
really mean, which would be 100% essential to doing anything automatic
with the results. If "errtable" sometimes means a table that doesn't
exist, and sometimes means a table that exists but doesn't contain an
expected column, or sometimes a table that exists but doesn't contain
an expected value, or sometimes a table that exists and contains a
value that shouldn't be there, etc etc, then actually doing anything
interesting with the information is going to be a matter of guess and
hope rather than something that's reliably automatable. As a single
example, in a foreign-key error message, is "errtable" going to be
the referencing table or the referenced table? And how would an
application writer predict that? If you don't have a clear design
rule that tells the answer, there is absolutely no chance of achieving
any consistency among the hundreds or thousands of ereports that will
have to be consistent in order for this feature to be worth anything.
Let alone whatever random effects an errcontext callback would produce;
I don't think you've thought through that bit at all.

regards, tom lane

On Tue, Aug 4, 2009 at 4:05 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Alvaro Herrera <alvherre@commandprompt.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

Tom Lane wrote:

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.

The message text is unparsable, because sometimes it is translated. If
the application wants to do something particular upon receiving a
particular error message, it has two choices: either it parses the
string, in which case it better not be translated, or it passes it to
the user untouched, in which cases it better be translated. There's no
way the application can try to do both things at once.

For example, say an application needs to capture a certain class of
errors to take automatic action, but pass others to the user untouched
for display (unexpected condition). This is not all that uncommon.
However, there's no way to do this at present. What I've done is check
the SQLSTATE, but then I can't get the affected table name. (Normally
the application knows which is the affected table, but this is not
always the case.)

I don't know about finegrainedness of SQLSTATEs but I haven't had
problems with that. ERRCODE_UNIQUE_VIOLATION appears twice in the
backend code; ERRCODE_CHECK_VIOLATION appears four times;
NOT_NULL_VIOLATION appears five times, as does
ERRCODE_FOREIGN_KEY_VIOLATION.

Lastly, I wonder if this could be useful to GUI tool writers?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Tuesday 04 August 2009 23:19:24 Tom Lane wrote:

Also, you completely dodged the question of defining what the fields
really mean, which would be 100% essential to doing anything automatic
with the results. If "errtable" sometimes means a table that doesn't
exist, and sometimes means a table that exists but doesn't contain an
expected column, or sometimes a table that exists but doesn't contain
an expected value, or sometimes a table that exists and contains a
value that shouldn't be there, etc etc, then actually doing anything
interesting with the information is going to be a matter of guess and
hope rather than something that's reliably automatable.

The SQL standard contains an analogous facility that defines exactly that.
Look for <get diagnostics statement>. It specifies what the "table name" etc.
is in specific error situations.

Robert Haas <robertmhaas@gmail.com> writes:

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.

As for the prefix bit, doesn't perl have a "word boundary" regexp
constraint?

However, I wonder whether we could turn this around. Instead of an
open-ended project to add an ill-defined collection of fields to an
ill-defined collection of error cases, maybe we could identify a
very short list of cases where it's known to be useful to pull a
specific bit of information out of a specific error message. And
then implement just those.

A minimum requirement for such a thing, in my opinion, is that *every*
occurrence of one of the targeted SQLSTATE codes should be able to
produce the same auxiliary fields with the same meanings. If you can't
define it that way, then you haven't actually made things better than
looking at the message text.

The bottom line behind my complaining is that this isn't going to be
helpful unless it's very clearly defined which error reports produce
what auxiliary fields. The impression I got from Alvaro's comments
was that he wanted to decorate everything in sight with anything he
could think of, which perhaps is not what he intended.

regards, tom lane

Tom Lane escribi�:

However, I wonder whether we could turn this around. Instead of an
open-ended project to add an ill-defined collection of fields to an
ill-defined collection of error cases, maybe we could identify a
very short list of cases where it's known to be useful to pull a
specific bit of information out of a specific error message. And
then implement just those.

Hmm, yeah, it makes sense to look at the problem this way.

The bottom line behind my complaining is that this isn't going to be
helpful unless it's very clearly defined which error reports produce
what auxiliary fields. The impression I got from Alvaro's comments
was that he wanted to decorate everything in sight with anything he
could think of, which perhaps is not what he intended.

Right :-(

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, Aug 4, 2009 at 5:23 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

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.

As for the prefix bit, doesn't perl have a "word boundary" regexp
constraint?

Yep, and it is in fact useful for these kinds of situations. At least
for me (and I obviously don't have to contend with translation issues
as Alvaro does, since I have the advantage of speaking the
untranslated language) the ickiness of the current situation is
manageable. It's not ideal, but it is manageable.

However, I wonder whether we could turn this around.  Instead of an
open-ended project to add an ill-defined collection of fields to an
ill-defined collection of error cases, maybe we could identify a
very short list of cases where it's known to be useful to pull a
specific bit of information out of a specific error message.  And
then implement just those.

A minimum requirement for such a thing, in my opinion, is that *every*
occurrence of one of the targeted SQLSTATE codes should be able to
produce the same auxiliary fields with the same meanings.  If you can't
define it that way, then you haven't actually made things better than
looking at the message text.

The bottom line behind my complaining is that this isn't going to be
helpful unless it's very clearly defined which error reports produce
what auxiliary fields.  The impression I got from Alvaro's comments
was that he wanted to decorate everything in sight with anything he
could think of, which perhaps is not what he intended.

Perhaps not. :-)

Maybe a specific list of the places where he'd like to add things
would be helpful.

...Robert

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.

If that's what we're trying to solve, I don't think that adding some
kind of proprietary shorthand coding is a good idea. If we're do to
this at all, it should be a connection-based GUC option, and use some
standard formal like XML fragments.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

If that's what we're trying to solve, I don't think that adding some
kind of proprietary shorthand coding is a good idea. If we're do to
this at all, it should be a connection-based GUC option, and use some
standard formal like XML fragments.

+1 to this idea in general, but *please* don't consider the use of
XML. If we really need some sort of formatting, let's do CSV. Or
YAML. Or JSON. Anything but XML.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200908041806
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkp4sOoACgkQvJuQZxSWSsjItACgsDtcid8Zqx9J2ehIhqbtN1l3
1jMAoNcd/6eJPBkKbdNYx6wbUl3bnxhC
=H5yv
-----END PGP SIGNATURE-----

Josh Berkus <josh@agliodbs.com> writes:

If that's what we're trying to solve, I don't think that adding some
kind of proprietary shorthand coding is a good idea. If we're do to
this at all, it should be a connection-based GUC option, and use some
standard formal like XML fragments.

Huh? What he was talking about is providing additional error fields,
which would presumably be made available via PQresultErrorField in
libpq, or its kin in other interfaces, and would be totally invisible to
any client that didn't ask for them. I can't imagine any value-add
from introducing XML into the problem.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

A minimum requirement for such a thing, in my opinion, is that
*every* occurrence of one of the targeted SQLSTATE codes should be
able to produce the same auxiliary fields with the same meanings.
If you can't define it that way, then you haven't actually made
things better than looking at the message text.

I would hope that SQLSTATE *categorizes* messages rather than uniquely
identifying them. If it is being used correctly (as I see it), there
could well be different specific messages within the category
identified by a SQLSTATE for which different identifiers are useful.

I'm not so interested in using this feature, personally; but I am
concerned about how the issue might affect our use of SQLSTATE, about
which I do care.

Many products have a sequence number to identify their messages in
addition to using SQLSTATE to classify them. That seems pretty
sensible to me.

-Kevin

On Tue, Aug 4, 2009 at 11:28 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Huh?  What he was talking about is providing additional error fields,
which would presumably be made available via PQresultErrorField in
libpq, or its kin in other interfaces, and would be totally invisible to
any client that didn't ask for them.  I can't imagine any value-add
from introducing XML into the problem.

Well it could add a single field which contained a structured
description of the error. That would solve the problem you described
for things like foreign key references which require two sets of table
and column lists and assigning meanings to them both -- essentially
because a single list of fields isn't really rich enough to describe
all possible errors.

But I wonder if it's really that hard to parse the text errors. We
have a pretty formulaic construction for our error messages. And we
quote things pretty aggressively and prefixing name with the object
type pretty consistently. Fixing any cases where we don't follow the
rules and fixing the rules in cases where it's hard to parse seems
like at least as good a solution.

So an alternate proposal is to add a field in the error message which
contains the untranslated string. That would let applications always
look at the untranslated string for parsing and always use the
translated string for user displays.
--
greg
http://mit.edu/~gsstark/resume.pdf

On Tue, Aug 04, 2009 at 10:06:37PM -0000, Greg Sabino Mullane wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

If that's what we're trying to solve, I don't think that adding
some kind of proprietary shorthand coding is a good idea. If
we're do to this at all, it should be a connection-based GUC
option, and use some standard formal like XML fragments.

+1 to this idea in general, but *please* don't consider the use of
XML. If we really need some sort of formatting, let's do CSV. Or
YAML. Or JSON. Anything but XML.

+1 on the "anything but XML." XML reeks of inner platform effect.

<http://en.wikipedia.org/wiki/Inner-platform_effect&gt;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Greg Stark wrote:

So an alternate proposal is to add a field in the error message which
contains the untranslated string. That would let applications always
look at the untranslated string for parsing and always use the
translated string for user displays.

That's an interesting idea, but you also have to consider other stuff
not related to translation, like schemas of the tables in question.
Someone requested some time ago to schema-qualify the table name (or a
constraint name, I don't recall offhand) in an error message, but this
idea was shot down because if that person really wanted that info, what
he should be looking at is including extra info in machine-readable
format into errors instead of a kluge like that.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

David Fetter wrote:

On Tue, Aug 04, 2009 at 10:06:37PM -0000, Greg Sabino Mullane wrote:

If that's what we're trying to solve, I don't think that adding
some kind of proprietary shorthand coding is a good idea. If
we're do to this at all, it should be a connection-based GUC
option, and use some standard formal like XML fragments.

+1 to this idea in general,

I think the train left the station on this issue quite a while ago. The
error messages have been like they are now for six releases. I don't
have any use for changing the format.

Clients can produce XML or JSON or whatever format you like already
anyway. The protocol is perfectly defined already.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support