Обсуждение: SQL conformity regarding SQLSTATE

Hello,

SQLSTATE is defined by the SQL standard. Our usage of the value seems to 
contain some defects in respect to it: SQLCODE is divided into a *class* 
(first two bytes) and a *subclass* (next 3 bytes). If an implementation 
defines additional values to support its own non- standardised features, 
it must use values within the two ranges [5-9] or [I-Z] for the first 
byte of the class *or* the first byte of the subclass. Our preferred 
byte for this case is P. But there are cases where other decisions have 
taken place.

Here is a list of values, which violate the above rule as the values are 
in the range which is reserved for the standard but (actually) are not 
defined by the standard. I compared our list in the version 10 
documentation with the SQL:2011 standard. (Unfortunately I have no 
access to SQL:2016. Maybe, some values of my list are defined there.)

01008, 03000, 0B000, 23502 - 23514, 39001, 42501 - 42939, F0000, F0001.

With that said I have some questions:

a) We strive for standard conformity as well as for continuity in our 
product. How can we solve that conflict?

b) Shall we add a comment into 'errcodes.txt' to remind everybody to the 
mentioned rule?

c) Is it possible to rearrange the rows of 'errcode.txt' in a way that 
reflects the natural sort order of SQLSTATE? This will be helpful for 
reading Appendix A of our documentation which is generated out of 
'errcode.txt'. But: a lot of other Postgres parts depends on this file - 
may be, some unwanted side effects will arise?

d) Do we have representatives in ISO's national bodies (ANSI, DIN, BSI, 
...) to follow and influence the standardisation process?


Jürgen Purtz

=?UTF-8?Q?J=c3=bcrgen_Purtz?= <juergen@purtz.de> writes:
> SQLSTATE is defined by the SQL standard. Our usage of the value seems to 
> contain some defects in respect to it:

There are various SQLSTATE codes that we borrowed from DB2 and other
RDBMSes; to the extent that those violate the spec, we're in good
company.

> Here is a list of values, which violate the above rule as the values are 
> in the range which is reserved for the standard but (actually) are not 
> defined by the standard. I compared our list in the version 10 
> documentation with the SQL:2011 standard. (Unfortunately I have no 
> access to SQL:2016. Maybe, some values of my list are defined there.)

> 01008, 03000, 0B000, 23502 - 23514, 39001, 42501 - 42939, F0000, F0001.

Actually, I'm pretty sure we were looking at SQL99 when we made our
original list.  I see 01008, 03000, 0B000, and 39001 there; are they
really not in later specs?

The 23xxx and 42xxx codes are there because SQL99 provides ridiculously
few subclasses for those classes.  I think many of those might've been
borrowed from DB2, but in any case they're in the legal range for
extension subclasses, so I don't follow your complaint.

Class F0 seems like a mistake ... maybe we could get away with changing
those two assignments, since it seems unlikely that any client code is
looking for those values.

> b) Shall we add a comment into 'errcodes.txt' to remind everybody to the 
> mentioned rule?

You mean the one at lines 64ff?

> c) Is it possible to rearrange the rows of 'errcode.txt' in a way that 
> reflects the natural sort order of SQLSTATE?

I'd have said it was already.

            regards, tom lane

>
> There are various SQLSTATE codes that we borrowed from DB2 and other
> RDBMSes; to the extent that those violate the spec, we're in good
> company.
In good company or in bad society ???
>> Here is a list of values, which violate the above rule as the values are
>> in the range which is reserved for the standard but (actually) are not
>> defined by the standard. I compared our list in the version 10
>> documentation with the SQL:2011 standard. (Unfortunately I have no
>> access to SQL:2016. Maybe, some values of my list are defined there.)
>> 01008, 03000, 0B000, 23502 - 23514, 39001, 42501 - 42939, F0000, F0001.
> Actually, I'm pretty sure we were looking at SQL99 when we made our
> original list.  I see 01008, 03000, 0B000, and 39001 there; are they
> really not in later specs?
Yes, I double checked it. Maybe they where used in one of the parts 5, 
6, 7,8, or 12. Those parts are no longer part of the SQL standard.

0B000 may be switched into the 23 class? 39001 "invalid SQLSTATE 
returned" sounds dubious for me - it declares itself to be 'invalid'.
> The 23xxx and 42xxx codes are there because SQL99 provides ridiculously
> few subclasses for those classes.  I think many of those might've been
> borrowed from DB2, but in any case they're in the legal range for
> extension subclasses, so I don't follow your complaint.
OK, my error. 23xxx and 42xxx subclasses are in the legal range.
> Class F0 seems like a mistake ... maybe we could get away with changing
> those two assignments, since it seems unlikely that any client code is
> looking for those values.
>
>> b) Shall we add a comment into 'errcodes.txt' to remind everybody to the
>> mentioned rule?
> You mean the one at lines 64ff?
Yes. But as you said: it is still there. No action necessary.
>> c) Is it possible to rearrange the rows of 'errcode.txt' in a way that
>> reflects the natural sort order of SQLSTATE?
> I'd have said it was already.

There is no sorting of subclasses within their class in 'errcode.txt'.

Summary: 01008, 03000, 0B000, 39001, F0000, and F0001 do not conform to 
the standard.


Kind regards
Jürgen Purtz

On 20 December 2017 at 10:08, Jürgen Purtz <juergen@purtz.de> wrote:

> Summary: 01008, 03000, 0B000, 39001, F0000, and F0001 do not conform to the
> standard.

The last two are clearly outside the standard anyway.

If you have suggested replacements for the others, please say. Patch
even better. Thanks

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

=?UTF-8?Q?J=c3=bcrgen_Purtz?= <juergen@purtz.de> writes:
> Summary: 01008, 03000, 0B000, 39001, F0000, and F0001 do not conform to 
> the standard.

I poked around in SQL:2011 and I concur that the first four of those
no longer appear in the standard.  However, unless grep is failing me,
we aren't generating those errcodes anywhere either:

ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING
ERRCODE_SQL_STATEMENT_NOT_YET_COMPLETE
ERRCODE_INVALID_TRANSACTION_INITIATION
ERRCODE_E_R_I_E_INVALID_SQLSTATE_RETURNED

So we could just remove those codes and be no worse off.

As for the other two, ERRCODE_CONFIG_FILE_ERROR and
ERRCODE_LOCK_FILE_EXISTS, we certainly are using those, but
as I mentioned it seems somewhat unlikely that clients are
testing for them.  I'm tempted to propose renumbering them
as PF000 and PF001.

            regards, tom lane

> I poked around in SQL:2011 and I concur that the first four of those
> no longer appear in the standard.  However, unless grep is failing me,
> we aren't generating those errcodes anywhere either:
>
> ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING
> ERRCODE_SQL_STATEMENT_NOT_YET_COMPLETE
> ERRCODE_INVALID_TRANSACTION_INITIATION
> ERRCODE_E_R_I_E_INVALID_SQLSTATE_RETURNED
>
> So we could just remove those codes and be no worse off.

+1.

I rearranged 'errcodes.txt' according to SQLSTATE without eliminating 
any row. Please read section 42 and its comments carefully. Maybe you 
want to rearrange the 4 rows which have no fourth column. The attachment 
contains the changes as raw file and as patch.

Generating the documentation works as expected. I haven't done any other 
test.

Kind regards
Jürgen Purtz

=?UTF-8?Q?J=c3=bcrgen_Purtz?= <juergen@purtz.de> writes:
> I rearranged 'errcodes.txt' according to SQLSTATE without eliminating 
> any row. Please read section 42 and its comments carefully. Maybe you 
> want to rearrange the 4 rows which have no fourth column. The attachment 
> contains the changes as raw file and as patch.

I looked at this a bit and am completely unwilling to split up the
ERRCODE_UNDEFINED_xxx and ERRCODE_DUPLICATE_xxx codes as you propose.
I think it's important to keep them together so that programmers looking
at the list will select the right one.  It's easy to foresee someone
mistakenly using the generic ERRCODE_UNDEFINED_OBJECT code if the one they
should have used is some distance away.  Some of the other places where
numeric code order is violated are probably there for similar reasons
about keeping logically related codes together.  (I think others are just
a result of SQL99 having listed the codes in a random order to begin
with...)

I think what might make sense is to rewrite generate-errcodes-table.pl
so that it sorts the entries for itself rather than relying on the
input file to determine the order.

            regards, tom lane

> I think what might make sense is to rewrite generate-errcodes-table.pl
> so that it sorts the entries for itself rather than relying on the
> input file to determine the order.
>
OK, tested for HTML and PDF output.

Kind regards
Jürgen Purtz