Range for user-defined SQLSTATE codes

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

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

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

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