Thread: the case for machine-readable error fields
Hi, I think the case for machine-readable error fields is well made and doesn't need any further push. So, what do we need to make it happen? Here's my proposal. First we need several new error message fields: table name, function name, constraint name, and so on. One possible way to go about this 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. The documentation on the FE/BE protocol already says that frontends should ignore unrecognized type fields, so I don't think we need to bump the protocol version for this. The other part is getting the information in ErrorData. AFAICS this is just a matter of adding a few setter functions; say errtable(), errfunction(), etc. Furthermore, we can just add those to existing errcontext callbacks, which would be pretty simple. Thoughts? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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 > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
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
wordyenough 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> 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
David Fetter wrote: > 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> > > > So, we are just trying to whip into shape explain diagnostics which are in JSON or XML, and now you want us to exclude XML from this one because you don't like it? Can we please try for some consistency? Sorry to break it to you, but there are plenty of people and businesses who want XML. And I certainly don't want to have to master every data representation model out there. XML has far more traction than anything else that's comparable in my experience. The fact that Greg is prepared to suggest CSV, with its obvious serious deficiencies, as being *better* than XML, makes his whole argument highly suspect IMNSHO. cheers andrew
> So, we are just trying to whip into shape explain diagnostics which are > in JSON or XML, and now you want us to exclude XML from this one because > you don't like it? Can we please try for some consistency? > > Sorry to break it to you, but there are plenty of people and businesses > who want XML. And I certainly don't want to have to master every data > representation model out there. XML has far more traction than anything > else that's comparable in my experience. > > The fact that Greg is prepared to suggest CSV, with its obvious serious > deficiencies, as being *better* than XML, makes his whole argument > highly suspect IMNSHO. >From a business perspective, XML is the only viable option for output. Joshua D. Drake -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Tue, Aug 4, 2009 at 8:36 PM, Joshua D. Drake<jd@commandprompt.com> wrote: > >> So, we are just trying to whip into shape explain diagnostics which are >> in JSON or XML, and now you want us to exclude XML from this one because >> you don't like it? Can we please try for some consistency? >> >> Sorry to break it to you, but there are plenty of people and businesses >> who want XML. And I certainly don't want to have to master every data >> representation model out there. XML has far more traction than anything >> else that's comparable in my experience. >> >> The fact that Greg is prepared to suggest CSV, with its obvious serious >> deficiencies, as being *better* than XML, makes his whole argument >> highly suspect IMNSHO. > > From a business perspective, XML is the only viable option for output. Wow, I feel like it's time for a bench-clearing brawl! My serialization format kicks your serialization format's butt! This doesn't have a whole lot to do with the original topic of this thread, which unless I missed something had only to do with extending the FE/BE protocol, but it definitely makes for lively conversation. Anyone want to vote ASN.1 for world domination? Can we set up some kind of cage match between the dueling standards? ...Robert
On Tue, Aug 04, 2009 at 09:16:23PM -0400, Robert Haas wrote: > On Tue, Aug 4, 2009 at 8:36 PM, Joshua D. Drake<jd@commandprompt.com> wrote: > > > >> So, we are just trying to whip into shape explain diagnostics which are > >> in JSON or XML, and now you want us to exclude XML from this one because > >> you don't like it? Can we please try for some consistency? > >> > >> Sorry to break it to you, but there are plenty of people and businesses > >> who want XML. And I certainly don't want to have to master every data > >> representation model out there. XML has far more traction than anything > >> else that's comparable in my experience. > >> > >> The fact that Greg is prepared to suggest CSV, with its obvious serious > >> deficiencies, as being *better* than XML, makes his whole argument > >> highly suspect IMNSHO. > > > > From a business perspective, XML is the only viable option for output. > > Wow, I feel like it's time for a bench-clearing brawl! > > My serialization format kicks your serialization format's butt! > > This doesn't have a whole lot to do with the original topic of this > thread, which unless I missed something had only to do with extending > the FE/BE protocol, but it definitely makes for lively conversation. > Anyone want to vote ASN.1 for world domination? Can we set up some > kind of cage match between the dueling standards? I love standards. There are so many to choose from :) 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
David Fetter <david@fetter.org> writes:
> On Tue, Aug 04, 2009 at 09:16:23PM -0400, Robert Haas wrote:
>> My serialization format kicks your serialization format's butt!
> I love standards. There are so many to choose from :)
And the funny thing is that the format we transmit this info to the
client in is really the least interesting or difficult part of the
whole matter ...
regards, tom lane
On Wed, Aug 5, 2009 at 2:43 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > And the funny thing is that the format we transmit this info to the > client in is really the least interesting or difficult part of the > whole matter ... That in a nutshell is the problem with XML in the first place. -- greg http://mit.edu/~gsstark/resume.pdf
2009/8/5 Alvaro Herrera <alvherre@commandprompt.com>: > 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. +1 really, I don't like to parse "any" text again to get this info. Pavel > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
> 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/) {
> ...
As an aside comment, a bit more regex foo with \b is indicated here :-)
if ($err =~ /\bname_of_first_foreign_key\b/) { $r->error_exit('First error message.')
}
Andreas
On Tue, Aug 04, 2009 at 01:12:10PM -0400, Alvaro Herrera wrote: > First we need several new error message fields: table name, function > name, constraint name, and so on. One possible way to go about this > 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. As pointed out downstream this seems somewhat open-ended and arbitrary; I would start with just making the constraint name easy to get to--I hope this doesn't happen already as I can't see anything obvious. My rational is that everything (short of syntax errors and strange things in the procedural languages) is already associated with a constraint. Syntax errors seem awkward to get standardized reporting for, the syntax keeps changing meaning that reporting anything more than what we do now doesn't seem practically useful. The calling code isn't going to be able to generate different SQL depending on error messages we give back, a human is needed there and can still interpret the text as well as we've always done. Constraints failing are a useful thing that calling code can do useful things with and it makes sense to give this back. These would seem to capture everything you mentioned elsewhere except UNIQUE indexes that weren't created as a constraint. Maybe this could be fixed by turning them into a constraint? as they seem like one to me. What are people doing with parsing error messages for "column names for datatype mismatches"? I can't imagine any of my code being able to do anything sensible in such a case. If it's things like people giving dates to the database in an incorrect format then that's what they get for not doing input validation isn't it? -- Sam http://samason.me.uk/
> > What are people doing with parsing error messages for "column names for > datatype mismatches"? I can't imagine any of my code being able to do > anything sensible in such a case. If it's things like people giving > dates to the database in an incorrect format then that's what they get > for not doing input validation isn't it? > When you have a full set of constraint, then you don't need to validate input. Just you will execute statement. When execution is correct, then all is ok, when not, then you have to recheck message, err code, ... and you have to verify, so some exception is expected or not. This is programming based on exceptions. Some better structured information helps. And what's more - this should be in conformity with ANSI SQL. regards Pavel Stehule
On Wed, Aug 05, 2009 at 01:29:13PM +0200, Pavel Stehule wrote: > > What are people doing with parsing error messages for "column names for > > datatype mismatches"? I can't imagine any of my code being able to do > > anything sensible in such a case. If it's things like people giving > > dates to the database in an incorrect format then that's what they get > > for not doing input validation isn't it? > > When you have a full set of constraint, then you don't need to > validate input. Just you will execute statement. OK, then we mean different things when we say "validate input". I was just meaning "simple" things like checking dates are well formed and that you're not passing things like 'sam's test' into the database (i.e. that you're actually escaping things correctly). Constraints are different from input validation as they rely on state that the database's client by definition doesn't have (otherwise it would be able to do the constraint checking just as well as the database). > When execution is > correct, then all is ok, when not, then you have to recheck message, > err code, ... and you have to verify, so some exception is expected or > not. This is programming based on exceptions. Some better structured > information helps. And what's more - this should be in conformity with > ANSI SQL. Humans can interpret the current error messages just fine, I don't believe that code could do with "better structured information". It would be possible to have the *Params libpq functions (not sure where this lives in the underlying protocols) give back errors when its inputs can't be parsed, but that seems like a different problem. Describing where problems are in a machine readable format from arbitrary code seems very fragile. -- Sam http://samason.me.uk/
Sam Mason <sam@samason.me.uk> wrote: > On Wed, Aug 05, 2009 at 01:29:13PM +0200, Pavel Stehule wrote: >> When you have a full set of constraint, then you don't need to >> validate input. Just you will execute statement. > > Constraints are different from input validation as they rely on > state that the database's client by definition doesn't have > (otherwise it would be able to do the constraint checking just as > well as the database). Just because something *can* also be checked within the front end doesn't mean it's best if it *is* checked there. When we were using a client/server model, we couldn't put the validations just in the client software, because there might be many places which could cause a violation of the business rule, and it was not reliable to count on all programmers knowing every rule and where it would need to be enforced. On top of that, there are cases where data is modified outside of the normal application software, and constraints only enforced in the application obviously provide no protection for data integrity in those cases. Attempting to put enforcement just in the RDBMS layer was tricky, though, because the messages tend to be written from the perspective of a database hacker, and tended to confuse or frighten the less computer-savvy staff using the software. I won't get into the all details of how we've dealt with this; primarily I want to chime in that it is a real problem. Briefly, though, our solution in the multi-tier environment did involve creating the ability to associate unique SQLSTATE values with failure of individual constraints for which there weren't well defined values (like there are for duplicate keys, for example). We could then have business write a friendly message for each such SQLSTATE. The more general ones were trickier, and I can say from experience that the ability to reliably pick off a table name or two when there's a duplicate key or a foreign key violation is critical to user-friendly behavior. Trying to enforce identical constraints in both the client code (for friendly behavior) and the database side (for better data integrity) is fraught with obvious problems. Anyway, the upshot is -- I think that it would be beneficial to allow, to the extent we can confirm it's not a violation of any applicable standard, a user-defined SQLSTATE to be associated with a constraint. I also think that it would be valuable to provide a mechanism for PostgreSQL-specific application code to be able to pick off one or two table names related to a "standard" constraint violation. I'm less convinced at the column or data value level, but I can see where it might be useful. Oh, and I've got nothing against XML as long as it's not exposed to a human being or application code. -Kevin
On Wed, Aug 05, 2009 at 09:30:02AM -0500, Kevin Grittner wrote: > Anyway, the upshot is -- I think that it would be beneficial to allow, > to the extent we can confirm it's not a violation of any applicable > standard, a user-defined SQLSTATE to be associated with a constraint. > I also think that it would be valuable to provide a mechanism for > PostgreSQL-specific application code to be able to pick off one or two > table names related to a "standard" constraint violation. I'm less > convinced at the column or data value level, but I can see where it > might be useful. Not sure if overloading SQLSTATE is the right way of doing this is it? It already has things like 23514 for a check violation and any other client code relying in this would break if it started getting different things back. -- Sam http://samason.me.uk/ p.s. I think you were agreeing with everything else I was saying, even if I didn't explain myself well enough for you to understand me!
Sam Mason <sam@samason.me.uk> wrote: > Not sure if overloading SQLSTATE is the right way of doing this is > it? It already has things like 23514 for a check violation and any > other client code relying in this would break if it started getting > different things back. If that's the standard SQLSTATE, I agree -- it suggests a need for some user-controllable field which could be set to a value to indicate a particular problem. Does the standard have anything like that, or would that be an extension? > p.s. I think you were agreeing with everything else I was saying, > even if I didn't explain myself well enough for you to understand > me! It's good to see convergence, then. Sorry I misunderstood. -Kevin
On Wed, Aug 05, 2009 at 11:32:06AM -0500, Kevin Grittner wrote: > Sam Mason <sam@samason.me.uk> wrote: > > Not sure if overloading SQLSTATE is the right way of doing this is > > it? It already has things like 23514 for a check violation and any > > other client code relying in this would break if it started getting > > different things back. > > If that's the standard SQLSTATE, I agree -- it suggests a need for > some user-controllable field which could be set to a value to indicate > a particular problem. Does the standard have anything like that, or > would that be an extension? Not sure how standard it is, but the docs[1] would suggest that it's trying to following something. Microsoft's MSDN docs on ODBC[2] show a reasonable similarity, the first Oracle doc I found[3] where similar as well. It just looks like a fixed set of numbers for a fixed set of conditions, can't find any canonical definition about what it's really for though. -- Sam http://samason.me.uk/ [1] http://www.postgresql.org/docs/current/static/errcodes-appendix.html[2] http://msdn.microsoft.com/en-us/library/ms714687(VS.85).aspx[3] http://download.oracle.com/docs/cd/B19306_01/appdev.102/a58231/appd.htm I think I prefer PG's urls!
Sam Mason <sam@samason.me.uk> wrote: > It just looks like a fixed set of numbers for a fixed set of > conditions, can't find any canonical definition about what it's > really for though. Sorry, I'm familiar with the SQLSTATE's role in the spec, I just wasn't sure how specific they got in their table of standard values regarding particular constraints. From the spec: "The character string value returned in an SQLSTATE parameter comprises a 2-character class value followed by a 3-character subclass value, each with an implementation-defined character set that has a one-octet character encoding form and is restricted to <digit>s and <simple Latin upper case letter>s. Table 32, *SQLSTATE class and subclass values*, specifies the class value for each condition and the subclass value or values for each class value." and: "If a subclass value is not specified for a condition, then either subclass '000' or an implementation-defined subclass is returned." From the table, the 23xxx series is for integrity constraint violations, but they appear not to have gotten too specific about breaking that down; thereby leaving it as an implementation choice: integrity constraint violation 23 (no subclass) 000 restrict violation 001 Anyway, it was a bad suggestion that we provide a way to specify a SQLSTATE to use for a constraint failure. I do think that some field which could be used for that purpose would be good. Preferably something which could be specified in the declaration of the constraint. -Kevin
On Wed, Aug 05, 2009 at 12:41:30PM -0500, Kevin Grittner wrote: > From the spec: > > "The character string value returned in an SQLSTATE parameter > comprises a 2-character class value followed by a 3-character subclass > value, each with an implementation-defined character set that has a > one-octet character encoding form and is restricted to <digit>s and > <simple Latin upper case letter>s. Table 32, *SQLSTATE class and > subclass values*, specifies the class value for each condition and > the subclass value or values for each class value." > > and: > > "If a subclass value is not specified for a condition, then either > subclass '000' or an implementation-defined subclass is returned." Thanks, I'd not found that specified--it matches up to what I'd found PG and other databases doing. Still doesn't really describe the engineering rational behind it though. > From the table, the 23xxx series is for integrity constraint > violations, but they appear not to have gotten too specific about > breaking that down; thereby leaving it as an implementation choice: > > integrity constraint violation 23 > (no subclass) 000 > restrict violation 001 Yes; but somewhere along the line we've got exactly the same integrity constraint violation sqlcodes as DB2 (and Derby, but that's not very surprising as they're both IBM). Can't find anybody else trying very hard though. > Anyway, it was a bad suggestion that we provide a way to specify a > SQLSTATE to use for a constraint failure. I do think that some field > which could be used for that purpose would be good. Preferably > something which could be specified in the declaration of the > constraint. I still stand by my assertion that the constraint name is sufficient for the original purpose. -- Sam http://samason.me.uk/
Sam Mason <sam@samason.me.uk> writes:
> On Wed, Aug 05, 2009 at 12:41:30PM -0500, Kevin Grittner wrote:
>> Anyway, it was a bad suggestion that we provide a way to specify a
>> SQLSTATE to use for a constraint failure. I do think that some field
>> which could be used for that purpose would be good. Preferably
>> something which could be specified in the declaration of the
>> constraint.
> I still stand by my assertion that the constraint name is sufficient for
> the original purpose.
Yeah. Changing the SQLSTATE for a given error seems much more likely
to break things than to be helpful. It does make sense to be able to
extract the constraint name for a constraint-related error without
having to make unsafe assumptions about the spelling of the
human-readable error message, though.
Peter pointed out upthread that the SQL standard already calls out some
things that should be available in this way --- has anyone studied that
yet?
regards, tom lane
Sam Mason <sam@samason.me.uk> writes:
> On Wed, Aug 05, 2009 at 12:41:30PM -0500, Kevin Grittner wrote:
>> From the table, the 23xxx series is for integrity constraint
>> violations, but they appear not to have gotten too specific about
>> breaking that down; thereby leaving it as an implementation choice:
> Yes; but somewhere along the line we've got exactly the same integrity
> constraint violation sqlcodes as DB2 (and Derby, but that's not very
> surprising as they're both IBM). Can't find anybody else trying very
> hard though.
BTW, that's because we deliberately borrowed as much as we could from
DB2. See the notes near the top of errcodes.h. As you say, nobody
else seems to care much, so that was the only precedent we could find.
regards, tom lane
Sam Mason <sam@samason.me.uk> wrote: > Still doesn't really describe the > engineering rational behind it though. Well, the distinctions in many cases would be mostly of interest to a DBA managing a large shop who was trying to characterize the reasons for query failure. Some codes, however, are particularly valuable. At the low end, classes '00' (information), '01' (warning), and '02' (no rows affected) can be used for useful, if mundane, purposes. A really interesting one is '40001' -- which indicates that your transaction was rolled back because of conflicts with concurrent transactions. Our framework, for example, resubmits transactions which fail with this SQL state; the user, and indeed the application code, never have any indication that the transaction was rolled back and restarted -- it appears just the same as a delay caused by blocking. (Our logs, of course, track these, so we can look to reduce conflicts.) > I still stand by my assertion that the constraint name is sufficient > for the original purpose. After thinking about that some more, I think I'm sold. -Kevin
2009/8/5 Tom Lane <tgl@sss.pgh.pa.us>: > Sam Mason <sam@samason.me.uk> writes: >> On Wed, Aug 05, 2009 at 12:41:30PM -0500, Kevin Grittner wrote: >>> Anyway, it was a bad suggestion that we provide a way to specify a >>> SQLSTATE to use for a constraint failure. I do think that some field >>> which could be used for that purpose would be good. Preferably >>> something which could be specified in the declaration of the >>> constraint. > >> I still stand by my assertion that the constraint name is sufficient for >> the original purpose. > > Yeah. Changing the SQLSTATE for a given error seems much more likely > to break things than to be helpful. It does make sense to be able to > extract the constraint name for a constraint-related error without > having to make unsafe assumptions about the spelling of the > human-readable error message, though. > > Peter pointed out upthread that the SQL standard already calls out some > things that should be available in this way --- has anyone studied that > yet? yes - it's part of GET DIAGNOSTICS statement http://savage.net.au/SQL/sql-2003-2.bnf.html#condition%20information%20item%20name regards Pavel Stehule > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Tom Lane wrote: > Peter pointed out upthread that the SQL standard already calls out some > things that should be available in this way --- has anyone studied that > yet? Yeah, I gave it a look. It looks useful as a guide, though obviously not directly implementable because it relies on GET DIAGNOSTICS to have somewhere to store the diagnostics information into (a host variable, etc). They do define that there is a TABLE_NAME, etc. Not much else to report at the moment. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> Peter pointed out upthread that the SQL standard already calls out some
>> things that should be available in this way --- has anyone studied that
>> yet?
> Yeah, I gave it a look. It looks useful as a guide, though obviously
> not directly implementable because it relies on GET DIAGNOSTICS to have
> somewhere to store the diagnostics information into (a host variable,
> etc). They do define that there is a TABLE_NAME, etc. Not much else to
> report at the moment.
I'm not proposing that we implement GET DIAGNOSTICS as a statement.
I was just thinking that the list of values it's supposed to make
available might do as a guide to what extra error fields we need to
provide where.
regards, tom lane
2009/8/5 Tom Lane <tgl@sss.pgh.pa.us>: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> Tom Lane wrote: >>> Peter pointed out upthread that the SQL standard already calls out some >>> things that should be available in this way --- has anyone studied that >>> yet? > >> Yeah, I gave it a look. It looks useful as a guide, though obviously >> not directly implementable because it relies on GET DIAGNOSTICS to have >> somewhere to store the diagnostics information into (a host variable, >> etc). They do define that there is a TABLE_NAME, etc. Not much else to >> report at the moment. > > I'm not proposing that we implement GET DIAGNOSTICS as a statement. > I was just thinking that the list of values it's supposed to make > available might do as a guide to what extra error fields we need to > provide where. > +1 regards Pavel Stehule > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Wed, Aug 05, 2009 at 08:57:14PM +0200, Pavel Stehule wrote: > 2009/8/5 Tom Lane <tgl@sss.pgh.pa.us>: > > Peter pointed out upthread that the SQL standard already calls out some > > things that should be available in this way --- has anyone studied that > > yet? > > yes - it's part of GET DIAGNOSTICS statement > > http://savage.net.au/SQL/sql-2003-2.bnf.html#condition%20information%20item%20name Just out of interest, how is this supposed to be used? Also, how many other SQL statements can be run when a transaction has been aborted? I would've thought that only COMMIT or ROLLBACK (and their synonyms) make sense and GET DIAGNOSTICS seems wrong for this purpose. I (and most code I've seen) normally structures client calls off to the database as follows: db.execute("""BEGIN; INSERT INTO foo (a,b) VALUES ($1,$2); INSERT INTO bar (c,d) VALUES ($3,$4); SELECT frub($5,$6); COMMIT;""", a,b,c,d,e,f); Where would a call to "GET DIAGNOSTICS" sensibly go? Or is it defined to return information about the last executed transaction, I can't find much in the above page or in anything Google gives back about it. Supporting it is fine from a standards point of view, from a calling code's correctness point of view it seems much better to send the info back at a protocol level. -- Sam http://samason.me.uk/
2009/8/6 Sam Mason <sam@samason.me.uk>: > On Wed, Aug 05, 2009 at 08:57:14PM +0200, Pavel Stehule wrote: >> 2009/8/5 Tom Lane <tgl@sss.pgh.pa.us>: >> > Peter pointed out upthread that the SQL standard already calls out some >> > things that should be available in this way --- has anyone studied that >> > yet? >> >> yes - it's part of GET DIAGNOSTICS statement >> >> http://savage.net.au/SQL/sql-2003-2.bnf.html#condition%20information%20item%20name > > Just out of interest, how is this supposed to be used? Also, how many > other SQL statements can be run when a transaction has been aborted? I > would've thought that only COMMIT or ROLLBACK (and their synonyms) make > sense and GET DIAGNOSTICS seems wrong for this purpose. > > I (and most code I've seen) normally structures client calls off to the > database as follows: > > db.execute("""BEGIN; > INSERT INTO foo (a,b) VALUES ($1,$2); > INSERT INTO bar (c,d) VALUES ($3,$4); > SELECT frub($5,$6); > COMMIT;""", a,b,c,d,e,f); > > Where would a call to "GET DIAGNOSTICS" sensibly go? Or is it defined > to return information about the last executed transaction, I can't find > much in the above page or in anything Google gives back about it. > > Supporting it is fine from a standards point of view, from a calling > code's correctness point of view it seems much better to send the info > back at a protocol level. typically in SQL/PSM (stored procedures - look on GET DIAGNOSTICS statement in plpgsql doc), maybe in ecpg. Other's environments raise exception - so you can get some data from exception or from special structures related to environment - php, ruby, .NET etc Pavel > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Thu, Aug 06, 2009 at 11:41:55AM +0200, Pavel Stehule wrote: > typically in SQL/PSM (stored procedures - look on GET DIAGNOSTICS > statement in plpgsql doc), maybe in ecpg. Other's environments raise > exception - so you can get some data from exception or from special > structures related to environment - php, ruby, .NET etc Sorry, I should have said that I saw how it was used in stored procedures. My interest was in getting the client doing something interesting, if you've already got the complexity of a stored procedure it shouldn't be to hard to teach it where the problem is. One thing I didn't see any comment on was on the fact that I think CREATE UNIQUE INDEX is really creating a constraint--it's just not showing up as one. For the constraint name to be sent back in the case of an error I think this needs to be changed. Triggers (and other domain specific code) seem less important here as they can always fail with whatever error is appropriate. -- Sam http://samason.me.uk /
Tom Lane <tgl@sss.pgh.pa.us> wrote: > I'm not proposing that we implement GET DIAGNOSTICS as a statement. > I was just thinking that the list of values it's supposed to make > available might do as a guide to what extra error fields we need to > provide where. From what I could find on a quick scan: RETURNED_SQLSTATE CLASS_ORIGIN SUBCLASS_ORIGIN CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CATALOG_NAME SCHEMA_NAME TABLE_NAME COLUMN_NAME CURSOR_NAME MESSAGE_TEXT MESSAGE_LENGTH MESSAGE_OCTET_LENGTH CATALOG is, of course, equivalent to database in the PostgreSQL world. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: > From what I could find on a quick scan: > > RETURNED_SQLSTATE > CLASS_ORIGIN > SUBCLASS_ORIGIN > CONSTRAINT_CATALOG > CONSTRAINT_SCHEMA > CONSTRAINT_NAME > CATALOG_NAME > SCHEMA_NAME > TABLE_NAME > COLUMN_NAME > CURSOR_NAME > MESSAGE_TEXT > MESSAGE_LENGTH > MESSAGE_OCTET_LENGTH Also, though I'm not yet totally clear on their meaning: COMMAND_FUNCTION DYNAMIC_FUNCTION And since users can declare a condition and associate it with a SQLSTATE, and later use that to terminate a database transaction with the SIGNAL command: | If the value of the RETURNED_SQLSTATE corresponds to unhandled | user-defined exception, then the value of CONDITION_IDENTIFIER is | the <condition name> of the user-defined exception. -Kevin