RE: Appendix A. PostgreSQL Error Codes

[Andy Dossett]

Hi

The use case is pretty simple; my system has thrown an SQL error code, I
want to know what it means.
 
Having the list in 'order by sqlstate' sequence would save me from having to
hunt up and down the list. And I can easily determine if the error code is
missing; I can then raise another bug report.

Regards

Andy Dossett

-Original Message-
From: Tom Lane <t...@sss.pgh.pa.us> 
Sent: 27 April 2018 16:16
To: Bruce Momjian <br...@momjian.us>
Cc: doss...@btinternet.com; pgsql-docs@lists.postgresql.org
Subject: Re: Appendix A. PostgreSQL Error Codes

Bruce Momjian <br...@momjian.us> writes:
> On Fri, Apr 27, 2018 at 10:08:12AM +, PG Doc comments form wrote:
>> Would it be possible to present the codes in ascending sequence?

> Uh, I am guessing this is the order listed in the SQL standard.  How 
> would you order them differently?  Have the letters only at the end?

There was some discussion of this a couple months ago, which I'm too lazy to
search the archives for right now, but it trailed off without any agreement
on what to do.

There are at least three different ordering principles that might be applied
here: numeric by code (well, perhaps more like "ASCII sort order");
alphabetical by exception name (but probably still keeping the
000 category codes at the top); or semantic grouping (ie try to keep related
errors together).  It looks to me like all three of these have been applied
in different places ;-).

One idea that might help is to present two tables with different sort
orders, say strict numeric and strict alphabetical.  (If we did that, I'd be
inclined to leave errcodes.txt alone and put the sorting responsibility on
the script that converts it to SGML.)

One question that has to be asked is what we think the use-case for this
table is at all.  Different use-cases result in different ideas about the
best ordering.

regards, tom lane

Re: Appendix A. PostgreSQL Error Codes

[Jürgen Purtz]

The SQL standard sorts the SQLSTATE table in a first step according to 
the *textual representation* of class (first two byte) and in a second 
step to the textual representation of subclass (next 3 byte) - keeping 
the text '(no subclass)' for subclass '000' on top. Obviously for some 
people this 'textual' sort order is relevant.


For my personal use I strongly prefer a pure 'numeric' sort order of 
SQLSTATE. Therefore I created a Perl program, which generates the SGML 
file out of errcode.txt and published it in 
https://www.postgresql.org/message-id/d69ce4f1-dd4e-61c1-070e-25f662e9c622%40purtz.de 
. This was a follow-up action after a discussion about illegal SQLSTATE 
values in PG, see: 
https://www.postgresql.org/message-id/11558.1513819695%40sss.pgh.pa.us.


PG documentation uses a numeric sort order for class and a semantic sort 
order for subclass.


Which one of the three is best? Here is my judgement: For technical 
oriented people the first approach is horrible because it sorts not only 
the subclass-text but also the class-text. This scrambles important and 
less important values to arbitrary positions. For me, sorting of the 
numeric value of class in a first step is essential. The third approach 
may be good for persons which work on the improvement of the PG source 
code. But they can refer to errcodes.txt. The primary readers of our 
documentation are users which work in roles as DBA or application 
developer. I doubt that they are interested in the semantic order of 
subclass. But I do not know whether they prefer numeric to text or text 
to numeric translation.


Jürgen Purtz


On 27.04.2018 17:16, Tom Lane wrote:


Bruce Momjian  writes:

On Fri, Apr 27, 2018 at 10:08:12AM +, PG Doc comments form wrote:

Would it be possible to present the codes in ascending sequence?

Uh, I am guessing this is the order listed in the SQL standard.  How
would you order them differently?  Have the letters only at the end?

There was some discussion of this a couple months ago, which I'm too lazy
to search the archives for right now, but it trailed off without any
agreement on what to do.

There are at least three different ordering principles that might be
applied here: numeric by code (well, perhaps more like "ASCII sort
order"); alphabetical by exception name (but probably still keeping the
000 category codes at the top); or semantic grouping (ie try to keep
related errors together).  It looks to me like all three of these have
been applied in different places ;-).

One idea that might help is to present two tables with different sort
orders, say strict numeric and strict alphabetical.  (If we did that,
I'd be inclined to leave errcodes.txt alone and put the sorting
responsibility on the script that converts it to SGML.)

One question that has to be asked is what we think the use-case for this
table is at all.  Different use-cases result in different ideas about the
best ordering.

regards, tom lane

Re: Appendix A. PostgreSQL Error Codes

[Tom Lane]

Bruce Momjian  writes:
> On Fri, Apr 27, 2018 at 10:08:12AM +, PG Doc comments form wrote:
>> Would it be possible to present the codes in ascending sequence?

> Uh, I am guessing this is the order listed in the SQL standard.  How
> would you order them differently?  Have the letters only at the end?

There was some discussion of this a couple months ago, which I'm too lazy
to search the archives for right now, but it trailed off without any
agreement on what to do.

There are at least three different ordering principles that might be
applied here: numeric by code (well, perhaps more like "ASCII sort
order"); alphabetical by exception name (but probably still keeping the
000 category codes at the top); or semantic grouping (ie try to keep
related errors together).  It looks to me like all three of these have
been applied in different places ;-).

One idea that might help is to present two tables with different sort
orders, say strict numeric and strict alphabetical.  (If we did that,
I'd be inclined to leave errcodes.txt alone and put the sorting
responsibility on the script that converts it to SGML.)

One question that has to be asked is what we think the use-case for this
table is at all.  Different use-cases result in different ideas about the
best ordering.

regards, tom lane

Re: Appendix A. PostgreSQL Error Codes

[Bruce Momjian]

On Fri, Apr 27, 2018 at 10:08:12AM +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/10/static/errcodes-appendix.html
> Description:
> 
> Hi
> Would it be possible to present the codes in ascending sequence?
> This applies to all releases.

Uh, I am guessing this is the order listed in the SQL standard.  How
would you order them differently?  Have the letters only at the end?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Appendix A. PostgreSQL Error Codes

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/static/errcodes-appendix.html
Description:

Hi
Would it be possible to present the codes in ascending sequence?
This applies to all releases.

Thanks