Thread: SQLState
Hi FAST have developed an extension to the PostgreSQL - replacing the storage manager with a more enterprise enabled version. This extension throws some errors that need SQLState codes. Thus, we would like to ask a couple of question regarding SQLState codes. 1/ Is there a convention for creating sub-class codes. I.e., not major classes, but children. do we have to take the next available number, or do we increment in 100s? 2/ If there is no convention is it ok to use the letter 'x' for all our State codes? eg. if we want to add a state under the "53" class, we would use "53x001", "53x002" and so on. 2/ If we have some States that might become common to the code in future releases, can we give them in as patches? Eg. we have an error "Archive log is full" with SQLState 53x00. It might be possible that OSS code might want to throw the same, if not similar, message. Can we give common errors such as that in for patching? Would the community accept these patches? We would appreciate having your inputon this matter. Mahesh Swamy Software Engineer Fujitsu Australia Software Technology Address: 14 Rodborough Road, Frenchs Forest NSW 2086 Tel: +61 2 9452 9000 Fax: +61 2 9975 2899 Email: maheshs@fast.fujitsu.com.au Web site: www.fastware.com This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinaryuser of the email address to which it was addressed and may contain copyright and/or legally privileged information.No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive thisemail in error, please return to sender. Thank you. If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please emailunsubscribe@fast.fujitsu.com.au
On Tue, 2004-05-11 at 22:07, Mahesh Swamy wrote: > 1/ Is there a convention for creating sub-class codes. The only convention I can see is that subclass values not defined by the SQL specification begin with 'P'. (This ought to be documented; barring any objections, I'll commit a patch stating this in errcodes.h explicitly). Beyond that I don't know of any other conventions. > 2/ If there is no convention is it ok to use the letter 'x' for all our > State codes? Why would you want to do that? > 2/ If we have some States that might become common to the code in > future releases, can we give them in as patches? What's the value in keeping these the same? (If a client is already looking for a specific error code, it doesn't seem too onerous for them to need to look for two.) -Neil
Neil Conway <neilc@samurai.com> writes:
> On Tue, 2004-05-11 at 22:07, Mahesh Swamy wrote:
>> 1/ Is there a convention for creating sub-class codes.
> The only convention I can see is that subclass values not defined by the
> SQL specification begin with 'P'.
The collection of SQLSTATEs defined by the standard itself is, um,
minimalistic. We have borrowed freely from DB2 and other
implementations in creating the set used by PG 7.4, and then added some
of our own. I believe we've used 'P' subclass codes for all the ones
we invented (other than XX000 which seemed appropriate for totally-
zonked-out conditions).
I would advise taking a good look at other DBs to see if you can find a
usable SQLSTATE before you go inventing new ones. If you do have to
invent a new one then use a 'P' code. This is a shared namespace after
all, so we shouldn't randomly use up new ranges of codes.
> (This ought to be documented; barring any objections, I'll commit a
> patch stating this in errcodes.h explicitly).
Okay, but I thought it was explained somewhere already. I might be
mistaken about that though...
regards, tom lane
Thank you Tom and Neil for your replies. > > 2/ If there is no convention is it ok to use the letter 'x' for all our > > State codes? > > Why would you want to do that? I think that I have missed explaining a point. We are developing a proprietary extension to PostgreSQL. This means that some errors that are thrown in our extension will never be thrown by the OSS community code. Thus, for these cases we should use a code that will not be used by the OSS community (hopefully) so we will not have to keep changing it in future updates. I hope i am clear? Thus, we invented using the letter 'x', as it seems a bit out of the way... > > 2/ If we have some States that might become common to the code in > > future releases, can we give them in as patches? > > What's the value in keeping these the same? (If a client is already > looking for a specific error code, it doesn't seem too onerous for them > to need to look for two.) In keeping one state code to mean one condition, it would mean that there is less confusion on the user's side. For example, after applying our extension, if the same code were not to be used, two different codes might mean the same condition. Like 53799 and 53P19 (fictitious numbers) might both mean "insufficient space on tablespace XXX". This is probably a bad thing from the user's perspective. Also, it wouldn't mean that there is better code re-use? > I would advise taking a good look at other DBs to see if you can find a > usable SQLSTATE before you go inventing new ones. If you do have to > invent a new one then use a 'P' code. This is a shared namespace after > all, so we shouldn't randomly use up new ranges of codes. > With regards to the error codes that might be thrown by PostgreSQL community, I will follow Tom's advice and look into DB2 and other databases to look for codes in order to patch. Thanks again! Mahesh This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinaryuser of the email address to which it was addressed and may contain copyright and/or legally privileged information.No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive thisemail in error, please return to sender. Thank you. If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please emailunsubscribe@fast.fujitsu.com.au
Am Mittwoch, 12. Mai 2004 04:07 schrieb Mahesh Swamy: > 2/ If there is no convention is it ok to use the letter 'x' for all our > State codes? eg. if we want to add a state under the "53" class, we would > use "53x001", "53x002" and so on. That doesn't work, because sqlstate values have to be exactly 5 characters. In addition to the conventions that you were already told, classes starting with Y are reserved for PostgreSQL clients.
On Wed, 2004-05-12 at 00:55, Mahesh Swamy wrote: > I think that I have missed explaining a point. We are developing a > proprietary extension to PostgreSQL. This means that some errors that are > thrown in our extension will never be thrown by the OSS community code. > Thus, for these cases we should use a code that will not be used by the OSS > community (hopefully) so we will not have to keep changing it in future > updates. I hope i am clear? Thus, we invented using the letter 'x', as it > seems a bit out of the way... Ah, right -- makes sense. I don't think a (lower-case) 'x' is the best choice, though; my copy of SQL2003 Section 23.1 sayeth: Class values that begin with one of the <digit>s 0 , 1 , 2 , 3 , or 4 or one of the <simple Latin upper case letter>s A , B , C , D , E , F , G , or H are returned only for conditions defined in ISO/IEC 9075 or in any other International Standard. [ ... ] Subclass values associated with such classes that also begin with one of those 13 characters are returned only for conditions defined in ISO/IEC 9075 or some other International Standard. [... ] Subclass values associated with such classes that begin with one of the <digit>s 5 , 6 , 7 , 8 , or 9 or one of the <simple Latin upper case letter>s I , J , K , L , M , N , O , P , Q , R , S , T , U , V , W , X , Y , or Z are reserved for implementation-specified conditions and are called implementation-defined subclasses. So an upper-case 'X' or other suitable letter (for those errors thrown _only_ by the proprietary extension) seems best, as the standard explicitly says that such a subclass value is legal for implementation-defined conditions. -Neil
On Wed, 2004-05-12 at 10:37, Peter Eisentraut wrote: > In addition to the conventions that you were already told, classes starting > with Y are reserved for PostgreSQL clients. They are? Under what circumstances would a PostgreSQL client produce an error code? (No such error codes exist at the moment, do they?) -Neil
Neil Conway wrote: > On Wed, 2004-05-12 at 10:37, Peter Eisentraut wrote: > > In addition to the conventions that you were already told, classes > > starting with Y are reserved for PostgreSQL clients. > > They are? Under what circumstances would a PostgreSQL client produce > an error code? > > (No such error codes exist at the moment, do they?) ECPG has several instances.
On Tue, 2004-05-11 at 22:56, Neil Conway wrote: > The only convention I can see is that subclass values not defined by the > SQL specification begin with 'P'. (This ought to be documented; barring > any objections, I'll commit a patch stating this in errcodes.h > explicitly). I've applied the attached patch to CVS HEAD. -Neil