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(at)commandprompt(dot)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(at)commandprompt(dot)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(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.

regards, tom lane

2009/8/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> 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 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Alvaro Herrera <alvherre(at)commandprompt(dot)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(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

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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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(at)turnstep(dot)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(at)agliodbs(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)jabber(dot)postgresql(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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

David Fetter <david(at)fetter(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)commandprompt(dot)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(at)postgresql(dot)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(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Sam Mason <sam(at)samason(dot)me(dot)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(at)postgresql(dot)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(at)commandprompt(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)samason(dot)me(dot)uk>:
> On Wed, Aug 05, 2009 at 08:57:14PM +0200, Pavel Stehule wrote:
>> 2009/8/5 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)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(dot)Grittner(at)wicourts(dot)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