3

I am implementing a RESTful Web Service backed by PostgreSQL. When PostgreSQL throws a SQLException, the HTTP response status should be 400 Bad Request if the error is something like the input string for a value is too long, or 500 Internal Server Error if the disk crashed.

To a human, it's obvious from the message in the exception. But is there any way (short of renting Watson now that he's back from Jeopardy) that my program can look at a SQLException and guess whether the problem is a user error or a system error?

I don't know a lot about SQL. I note that SQLState is a five-character string, and the first two characters are the "Class Code".

Here are my guesses so far:

Class 00: Successful completion, no exception thrown.

Class 01: Warning. Is a SQLException thrown in this case? If so, result should probably be 400 Bad Request.

Class 02: No Data (normal result when no results returned?)

Class 03: SQL Statement Not Yet Complete: probably a programming error, not an end user error, therefore 500 Internal Server Error

Class 08: Connection Exception: obviously 500 Internal Server Error

Classes 09 - 21: Probably 500 Internal Server Error

Class 22: Data Exception. The specific example I am working on raises "22001 ERROR: value too long for type character varying(250)." This should be returned as 400 Bad Request. (There is length validation on the "name" field before the SQL statement is executed but the value actually stored is a full path name, i.e. the name appended to some other stuff.) I can probably return 400 Bad Request for other class 22 errors, even though many of them would stem from programming errors.

The other classes look like 500 Internal Server Error would usually be the more correct status.

Mark Lutton
  • 6,959
  • 7
  • 41
  • 57
  • What it is a user error?. When the user is illegal? Users are not usually allowed to crash the DB sever. I doubt most HUmans would consider any of those error statements intelligible. Seems like a better path would be more testing and anticipation of common oopsies. – nate c Feb 21 '11 at 20:52

2 Answers2

1

You may consider validating requests and only forward them to DB if they are valid, and DB should process them successfully. In this case, all errors returned by postgresql can be considered as internal server error.

driushkin
  • 3,531
  • 1
  • 24
  • 25
  • In general a good idea and we try to do that, but this question was prompted by a case where the validation code did not match what PostgreSQL actually did. A unit tester wrote a test expecting 400 Bad Request and got 500 Internal Server Error. But ultimately, not validating the name length correctly was a programming error, and therefore, paradoxically, 500 Internal Server Error was correct. – Mark Lutton Feb 21 '11 at 16:31
0

A good start is the manual: Appendix A. PostgreSQL Error Codes

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135