Thread: Extracting more useful information from PSQLException

Hi everyone,

While designing web services that are backed by PostgreSQL, it is often helpful to report a more useful HTTP error than
ageneric "Something went wrong, sorry dude". 

The thrown PSQLException has a SQLState variable which can tell you the type of failure that occurred, but there is
muchmore useful information in the exception message that does not seem to be available in a structured manner.  For
example,if a UNIQUE constraint or a CHECK constraint is violated, the constraint name is in the error message.  This
couldbe useful as it is possible that the violation of a "email UNIQUE" constraint is most appropriate as a HTTP 400,
sincethe user provided an email that is already in use, while a "id PRIMARY KEY" constraint is most appropriately a
HTTP500 since the server clearly shouldn't be inserting duplicate IDs and this is an internal error. 

Has anyone found a satisfying solution to this problem?  My current approach is to parse the exception message with a
regexto get the interesting bits, but this is not robust to database message changes in newer versions of PG, locale /
translations,etc… 


Is it possible / would it be a good addition to expose structured SQL error information through JDBC?  If such a thing
doesnot exist, is this a feasible contribution?  I expect it might require some hacking on the PostgreSQL server side
toexpose the error information in a structured manner.  Or am I just dreaming too much, and this is not feasible? 

Thanks,
Steven Schlansker

On Fri, 28 Dec 2012, Steven Schlansker wrote:

> While designing web services that are backed by PostgreSQL, it is often
> helpful to report a more useful HTTP error than a generic "Something
> went wrong, sorry dude".
>
> Is it possible / would it be a good addition to expose structured SQL
> error information through JDBC?  If such a thing does not exist, is this
> a feasible contribution?  I expect it might require some hacking on the
> PostgreSQL server side to expose the error information in a structured
> manner.  Or am I just dreaming too much, and this is not feasible?
>

There is currently a patch in progress and under discussion for the server
changes to expose this information.  I would recommend reviewing this and
joining the discussion on -hackers if you have something useful to
contribute.  I don't know anything about the patch other than that it
exists.  I would be useful to see how much the JDBC driver would have to
change to take advantage of it.

https://commitfest.postgresql.org/action/patch_view?id=843

Kris Jurka

On Fri, 28 Dec 2012, Kris Jurka wrote:

> On Fri, 28 Dec 2012, Steven Schlansker wrote:
>
> > Is it possible / would it be a good addition to expose structured SQL
> > error information through JDBC?  If such a thing does not exist, is this
> > a feasible contribution?  I expect it might require some hacking on the
> > PostgreSQL server side to expose the error information in a structured
> > manner.  Or am I just dreaming too much, and this is not feasible?
> >
>
> There is currently a patch in progress and under discussion for the server
> changes to expose this information.

This patch was committed to the server and I've exposed these field in the
JDBC driver through the somewhat ugly...

} catch (SQLException sqle) {
    ServerErrorMessage err =
((PSQLException)sqle).getServerErrorMessage();
    System.out.println(err.getTable());
}

The server infrastructure doesn't cover all the error cases I would have
hoped, so you can't just through data at the database and always be able
to produce an intelligent error response to a user, but it's a start.

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=991f3e5ab3f8196d18d5b313c81a5f744f3baaea

https://github.com/pgjdbc/pgjdbc/commit/e9ac5f8d964202ab5d43e401d74dcd76cefd112e


Kris Jurka

On Jan 31, 2013, at 4:55 PM, Kris Jurka <books@ejurka.com> wrote:

>
>
> On Fri, 28 Dec 2012, Kris Jurka wrote:
>
>> On Fri, 28 Dec 2012, Steven Schlansker wrote:
>>
>>> Is it possible / would it be a good addition to expose structured SQL
>>> error information through JDBC?  If such a thing does not exist, is this
>>> a feasible contribution?  I expect it might require some hacking on the
>>> PostgreSQL server side to expose the error information in a structured
>>> manner.  Or am I just dreaming too much, and this is not feasible?
>>>
>>
>> There is currently a patch in progress and under discussion for the server
>> changes to expose this information.
>
> This patch was committed to the server and I've exposed these field in the
> JDBC driver through the somewhat ugly...
>
> } catch (SQLException sqle) {
>     ServerErrorMessage err =
> ((PSQLException)sqle).getServerErrorMessage();
>     System.out.println(err.getTable());
> }
>
> The server infrastructure doesn't cover all the error cases I would have
> hoped, so you can't just through data at the database and always be able
> to produce an intelligent error response to a user, but it's a start.
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=991f3e5ab3f8196d18d5b313c81a5f744f3baaea
>
> https://github.com/pgjdbc/pgjdbc/commit/e9ac5f8d964202ab5d43e401d74dcd76cefd112e
>

Fantastic!  Thanks so much.  I'll use this in the next revision of our database code :-)