Re: BUG #5246: Misleading/inconsistent SQLSTATE behavior

Just to be clear (before I consider attempting a patch maybe I can
hand off to someone else to double-check)...

On Wed, Dec 16, 2009 at 5:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Yeah.  The problem is that the only infrastructure libpq has for returning
> individual error message fields (like the SQLSTATE) is associated with a
> PGresult, and there's no PGresult for a connection failure.  I see no
> easy way to fix that without incompatible changes in libpq's API.

Looking through the code it looks like this is for two reasons:

1) The current API assumes a PGResult rather than a connection being handed in.
2) There is no place in the struct for a connection to handle the
message fields.

>
> This is related to the fact that errors detected internally in libpq
> generally lack SQLSTATEs.  Part of the reason that fixing that has been
> so low-priority is that in many cases there's no existing API whereby
> they could be returned anyhow.  It's been on the TODO list since 7.4,
> but nobody has cared to tackle it.

I do a lot with SQLSTATEs in my Perl code and having access to this
would be really quite helpful. (More info below but don't want to
crowd out my questions.)

It looks like this could be added without a disruption to programmer
interfaces, but it seems like any major change in this area would
create binary compatibility issues (i.e. require recompile of linked
software). Is this correct in what you mean by API incompatibility?

A quick review suggests to me it shouldn't be too bad to add this, but
at the same time my C code is not the best out there. I might still
be willing to give it a shot.

As for more info:

I use the SQLSTATE field quite heavily for error handling and while it
isn't always sufficient by itself, it is quite helpful in detecting
errors and providing more helpful messages to end users. The
application I am working on at the moment uses database roles as
application roles and enforces security in the database. On login, the
user has to enter username, password, and database name in order to
access the application. The problem I was running into is that if the
user enters a non-existant database, the program would prompt for
username/password instead of letting them know the database was wrong.

My workaround at the moment is to check the error message against a
configurable value to see if it represents a missing database. It
sucks because it means that foreign locale users must go through extra
configuration steps. If I had the SQLSTATE data it would be easy to
set up so that wouldn't be needed.