Re: the case for machine-readable error fields - Mailing list pgsql-hackers

Sam Mason <sam@samason.me.uk> wrote: 
> On Wed, Aug 05, 2009 at 01:29:13PM +0200, Pavel Stehule wrote:
>> When you have a full set of constraint, then you don't need to
>> validate input. Just you will execute statement.
> 
> Constraints are different from input validation as they rely on
> state that the database's client by definition doesn't have
> (otherwise it would be able to do the constraint checking just as
> well as the database).
Just because something *can* also be checked within the front end
doesn't mean it's best if it *is* checked there.  When we were using a
client/server model, we couldn't put the validations just in the
client software, because there might be many places which could cause
a violation of the business rule, and it was not reliable to count on
all programmers knowing every rule and where it would need to be
enforced.  On top of that, there are cases where data is modified
outside of the normal application software, and constraints only
enforced in the application obviously provide no protection for data
integrity in those cases.
Attempting to put enforcement just in the RDBMS layer was tricky,
though, because the messages tend to be written from the perspective
of a database hacker, and tended to confuse or frighten the less
computer-savvy staff using the software.  I won't get into the all
details of how we've dealt with this; primarily I want to chime in
that it is a real problem.  Briefly, though, our solution in the
multi-tier environment did involve creating the ability to associate
unique SQLSTATE values with failure of individual constraints for
which there weren't well defined values (like there are for duplicate
keys, for example).  We could then have business write a friendly
message for each such SQLSTATE.  The more general ones were trickier,
and I can say from experience that the ability to reliably pick off a
table name or two when there's a duplicate key or a foreign key
violation is critical to user-friendly behavior.
Trying to enforce identical constraints in both the client code (for
friendly behavior) and the database side (for better data integrity)
is fraught with obvious problems.
Anyway, the upshot is -- I think that it would be beneficial to allow,
to the extent we can confirm it's not a violation of any applicable
standard, a user-defined SQLSTATE to be associated with a constraint.
I also think that it would be valuable to provide a mechanism for
PostgreSQL-specific application code to be able to pick off one or two
table names related to a "standard" constraint violation.  I'm less
convinced at the column or data value level, but I can see where it
might be useful.
Oh, and I've got nothing against XML as long as it's not exposed to a
human being or application code.
-Kevin