From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?J=c3=bcrgen_Purtz?= <juergen@purtz.de>
cc: pgsql-sql@lists.postgresql.org
Subject: Re: SQL conformity regarding SQLSTATE
In-reply-to: <c2c1a96d-dae9-6667-bf11-eb423ee90385@purtz.de>
References: <c2c1a96d-dae9-6667-bf11-eb423ee90385@purtz.de>
Comments: In-reply-to =?UTF-8?Q?J=c3=bcrgen_Purtz?= <juergen@purtz.de>
	message dated "Tue, 19 Dec 2017 15:14:50 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <31998.1513697414.1@sss.pgh.pa.us>
Date: Tue, 19 Dec 2017 10:30:14 -0500
Message-ID: <31999.1513697414@sss.pgh.pa.us>
Precedence: bulk

=?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