Обсуждение: Range for user-defined SQLSTATE codes

I can't be the first person (or even the 10,000th) to want to define my
own SQLSTATE codes when raising errors in a stored procedure.  I've
just tested doing so in a PL/pgSQL function access via JDBC, and I had
no problem retrieving the non-standard state from the SQLException.  (I
used 'WWWWW' and 'ZZZZZ' as my tests.)

Is there anything like a standard range -- formal or otherwise -- for
such codes?  A best practice?  A general consensus?  A half-painted
bike shed?

Thanks!

--
========================================================================
Ian Pilcher                                         arequipeno@gmail.com
Sometimes there's nothing left to do but crash and burn...or die trying.
========================================================================



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ian Pilcher <arequipeno@gmail.com> wrote:

> I can't be the first person (or even the 10,000th) to want to define my
> own SQLSTATE codes when raising errors in a stored procedure.  I've
> just tested doing so in a PL/pgSQL function access via JDBC, and I had
> no problem retrieving the non-standard state from the SQLException.  (I
> used 'WWWWW' and 'ZZZZZ' as my tests.)
>
> Is there anything like a standard range -- formal or otherwise -- for
> such codes?  A best practice?  A general consensus?  A half-painted
> bike shed?

There is this in the SQL standard.  According to that, SQLSTATE
values with 0-4 or A-H in both the first and third positions are
reserved for values defined by standards.  All others are available
for "implementation-specified" exception conditions.  As far as I
know, the PostgreSQL community has claimed SQLSTATE values with P0
or XX in the start of a SQLSTATE or P in the third character.  To
allow for future expansion by the PostgreSQL community it might be
wise to stay away from any SQLSTATE starting with P for your
application use.

-Kevin

Kevin Grittner <kgrittn@ymail.com> writes:
> Ian Pilcher <arequipeno@gmail.com> wrote:
>> Is there anything like a standard range -- formal or otherwise -- for
>> such codes?  A best practice?  A general consensus?  A half-painted
>> bike shed?

> There is this in the SQL standard.  According to that, SQLSTATE
> values with 0-4 or A-H in both the first and third positions are
> reserved for values defined by standards.  All others are available
> for "implementation-specified" exception conditions.  As far as I
> know, the PostgreSQL community has claimed SQLSTATE values with P0
> or XX in the start of a SQLSTATE or P in the third character.  To
> allow for future expansion by the PostgreSQL community it might be
> wise to stay away from any SQLSTATE starting with P for your
> application use.

In addition to what the spec says, it's worth looking into
src/backend/utils/errcodes.txt, where you'll note that we have borrowed
some codes beginning with '5' from DB2, and also stated that codes
beginning with 'Y' are reserved for client-side code such as ecpg.
So I'd stay away from 5, P, X, and Y as first characters of private
SQLSTATEs (or third characters of private codes within a spec-defined
class, if that's what you want).  Other than that, go nuts.

            regards, tom lane