Take the 2-minute tour ×

My question is simple:

What tactics are used with PostgreSQL based applications to avoid the user ever being presented with a DB generated error caused by constraints and foreign keys etc. ?

The rationale for this question is:

It is common practise within all DBs I've worked with to trap at least some user errors. Generally you try to make sure the application guides the users away from error but no matter how idiot proof you make something, someone will always make a better idiot. As a result you always need to encode constraints such as unique constraints and foreign key constraints to ensure that a brilliantly foolish individual does not corrupt your data.

These need to be DB constraints and not application logic because frequently they involve race conditions (only the DB can correctly keep something unique or enforce foreign keys etc).

For modern software it no longer acceptable to present a user with a message such as: ERROR: duplicate key value violates unique constraint "test_x_key". Just the word "error" makes some users phone tech support to tell them the application has broken. Users expect to be talked to as people not as a computer. And well they should... computers the servants of people; not the other way around.

The problem is, I have never found a good solution to reliably catch a DB error resulting from a constraint and trace it's precise origin in order to a) have the application take appropriate action such as correcting the error automatically and retrying or b) presenting the user with a meaningful error message written by the application designer not the makers of PostgreSQL.

I have thought of parsing the resulting human readable error message which usually available in some form or other depending on which language you're working in. My concern over this answer is that I don't see the error messages specified in the manual and so I'm not certain how reliable they are from one version of PostgreSQL to the next.

I'm interested to know: What techniques do people use to identify which DB constraint caused a DML statement to fail or what techniques do people use to avoid hitting these constraints entirely.

share|improve this question
add comment

closed as too broad by Juhana, Charles, Dimitri M, Harry Johnston, Chris Mar 3 at 7:08


2 Answers

When you catch the error, extract the constraint name from the exception/error message and use the constraint name as a key to a resource bundle that maps the name to a human readable message

Given the table:

create table foo 
    id integer constraint pk_foo primary key, 
    some_value integer

create a resource bundle (or whatever technique you use in your programming language) with this content:

pk_foo=This record already exists in the database

For FK constraints you can have something like:

fk_foo_bar=There is no corresponding "bar" record available.

These messages can then even be localized.

To supply "generic" error messages, you should rely on the error code and not on the message. The error codes are described in the manual

share|improve this answer
Could you elaborate a little on the reliability of error messages. You suggest that parsing them is acceptable. What is the scope for them changing from one version to the next and thus messing up the "extraction" of the constraint name? –  couling Jan 6 '13 at 23:14
@couling: although there is a chance they will change, I doubt that existing message will change substantially. I would however make sure that the language is always set to english, so that you don't need to worry about that. –  a_horse_with_no_name Jan 7 '13 at 8:02
add comment

Another possibility (although I can't comment on whether it's a good solution) is to create your own set of translated messages.

See Native Language Support for a description of how message catalogs are written for PostgreSQL. Many of the message strings are parameterized so you may find that the information that you plan to parse out of the error message is already treated as a parameter anyway.

share|improve this answer
This is exactly what we do with LedgerSMB. One note I would make is that the best way to do this is to translate the SQL State code to a human readable message, not the db error string. –  Chris Travers Mar 4 '13 at 11:55
add comment
