Thread: SQLSTATEs for warnings

The SQL standard states

An implementation-defined completion condition shall be indicated by
returning an implementation-defined subclass in conjunction with one of
the classes successful completion, warning, or no data.

In other words, an info, notice, or warning must have a class 00, 01, 02.
However, I noticed several warnings that use codes that are intended for
errors, for example in portalcmds.c and xact.c.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> In other words, an info, notice, or warning must have a class 00, 01, 02.

I suspect though that the spec is assuming that the SQLSTATE code is the
*only* way for the application to determine whether the message is
success, warning, or error.  Since we have other signaling mechanisms
(the severity field, or even more basically the Error/Notice message
type distinction), I'm not convinced we need to be entirely anal about
this division.

AFAICS the alternative to misusing error-class SQLSTATEs as warnings is
that we invent implementation-specific warning codes.  Is it really
worth having two codes for what amounts to the same condition?

> However, I noticed several warnings that use codes that are intended for
> errors, for example in portalcmds.c and xact.c.

Several of them are things that probably *ought* to be errors, as you
noted in a later message.  I don't recall exactly what other sins I
committed in this area (the whole effort is already becoming a blur ;-)).
We have time to rejigger around the edges though --- I have no objection
to reassigning codes during beta.  Once we release it will get harder.

A possibly related sin in the same category is that I think that the
class-40 SQLSTATEs are intended to imply that the server has forcibly
aborted your whole transaction --- not just the current statement, which
is what I think the spec envisions for all the other error classes.
We do not currently make that distinction, but sooner or later we will.
Perhaps we should avoid the class-40 codes and make our own
implementation-dependent codes for deadlock detection and so forth.
        regards, tom lane

Tom Lane writes:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > In other words, an info, notice, or warning must have a class 00, 01, 02.
>
> I suspect though that the spec is assuming that the SQLSTATE code is the
> *only* way for the application to determine whether the message is
> success, warning, or error.  Since we have other signaling mechanisms
> (the severity field, or even more basically the Error/Notice message
> type distinction), I'm not convinced we need to be entirely anal about
> this division.

The severity field is useless, because it contains localized text that
cannot be evaluated by a program.  Also, neither the severity field nor
the error/notice message distinction is necessarily available in
interfaces that work at a layer above libpq (e.g., embedded SQL).

There is no portable (let alone consistent) way right now to detect
whether a given condition is success, a warning, or an error.

> AFAICS the alternative to misusing error-class SQLSTATEs as warnings is
> that we invent implementation-specific warning codes.

I don't see that as being allowed.

> Is it really worth having two codes for what amounts to the same
> condition?

The same condition shouldn't be a warning in one place and an error in
another.  Otherwise it's not really the same condition.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> I suspect though that the spec is assuming that the SQLSTATE code is the
>> *only* way for the application to determine whether the message is
>> success, warning, or error.

> The severity field is useless, because it contains localized text that
> cannot be evaluated by a program.

Fair point.

> Also, neither the severity field nor
> the error/notice message distinction is necessarily available in
> interfaces that work at a layer above libpq (e.g., embedded SQL).

An interface library would have to really go out of its way to make
errors and notices look alike, at least if it's built atop libpq.
I think this argument is pretty weak.  But you're right about severity.

>> AFAICS the alternative to misusing error-class SQLSTATEs as warnings is
>> that we invent implementation-specific warning codes.

> I don't see that as being allowed.

Hm?  Surely we can invent implementation-defined warning codes in the
ranges 015xx-019xx and 01Ixx-01Zxx.  If not, what other alternative
do you propose?
        regards, tom lane